smd_query: Talk to the database directly via SQL

aslsw66 · 2010-02-06 23:06:19

OK, I have all of this working. I’ve simplified it with two actions, either delete or insert. I figure if someone wants to update the text then they can delete the old one and insert a new one.

Now I’m stuck on inserting a correctly formatted date into the table. It works if I correctly use the format "YYY-mm-dd" in the form field, but I’m struggling to convert any other date format into the correct format (eg. I would like to implement a JQuery date picker). I seem to be coming undone where I try to use some raw PHP to get the date value from the HTML field, convert it to the right format and then pass it back into the <smd_query> update statement.

What is the right way to achieve this? ie. HTML form field with a date => reformat to SQL format => insert into smd_query.

Thanks for the help so far.

Bloke · 2010-02-07 13:45:59

maniqui wrote:

That’s the magic that happens here at the forum, when I hit the “Submit” button, right?

Yep. Except that’s ‘visible’ in terms of a message that pops up. I tend to implement it so it just takes that fraction longer and double-submits without the visitor realising. But either method works and avoids the repost issue.

How should I “google” this trick to have more information about it?

Ummm, not sure. How about prevent form data resubmission ?

aslsw66 wrote:

some raw PHP to get the date value from the HTML field, convert it to the right format and then pass it back into the <smd_query> update statement

From your form data I’d try using strtotime()and then either strftime() or date() to convert it to a known format. e.g.

<txp:php>
   global $variable;

   $theDate = gps('my_date');
   $utime = strtotime($theDate);

   if ($utime !== false) {
      $variable['proper_date'] = strftime("%Y-%m-%d", $utime);
   } else {
      // option to output a fixed date, e.g. "today"...
      $variable['proper_date'] = strftime("%Y-%m-%d");
      // ... or some error value you can test for and then fail the INSERT
      $variable['proper_date'] = 'INVALID';
   }
</txp:php>

Then you can use ?proper_date in your smd_query to retrieve the <txp:variable /> that has been created, and/or use <txp:if_variable> to test if it has been set to INVALID and take appropriate action.

aslsw66 · 2010-02-07 14:11:36

That’s where I’m coming unstuck. If you enter the date as the proper SQL format (Y-m-d) it works, but I’m having trouble converting from an Australian format (dd/mm/yyyy). And I want to use the proper SQL date format so that I can sort/extract on dates.

I’ve just got the date picker working, so at least that can make the field conform to my known format. If worst comes to worst, I guess I can deconstruct the day, month and year at each “/” and then recreate the SQL date properly.

Maybe I need to keep messing about with strtotime a bit more.

Thanks for the help though. All of this TXP and PHP stuff is keeping me amused and off the streets.

Bloke · 2010-02-07 14:15:06

aslsw66 wrote:

Maybe I need to keep messing about with strtotime a bit more.

In theory, strtotime() should convert any english human readable date into a UNIX timestamp. From there you can reformat it any way you like. I’ve successfully used it to convert UK dd/mm/yyyy before so it should work. Hate to say it, but if you’re having trouble, post some code and I’ll see what I can do ;-)

aslsw66 · 2010-02-07 21:25:43

Finally achieved what I was looking for, thanks to your help. I had a few problems with getting the date formatting right – converting between UK/Aus date format and MySQL seemed to give me trouble but I figure it out.

If you’re interested, here’s a link to the final version. It works, but I still have to pull alert messages out of the database and display them on the front page if there are any for today. Sounds like another job for smd_query.

mlarino · 2010-02-13 20:14:48

Hi,
I am trying to use this plugin to create a search form for a realestate website.
I have no almost nothing of PHP, and I am not sure if that limitation will keep me of understanding how this plugin works.

All I need it to create 2 select boxes, one for REGION (NY, Chicago…), and one for TYPE (apartment, house etc…) and a search button.

That form would give me a list of the articles that match those custom_fields.

Can someone help me with this?
thanks!

Bloke · 2010-02-14 11:08:43

mlarino wrote:

I need it to create 2 select boxes, one for REGION (NY, Chicago…), and one for TYPE (apartment, house etc…) and a search button.

OK, it can be done but just for clarity the plugin itself won’t create input boxes for you — that’s down to you to generate in goold old fashioned HTML (in a TXP Page, Form or Article). Once you have written an HTML <form> containing the select boxes and your submit button (make sure the form’s action submits back to the same page) you can then use smd_query to read the form variables and put them into a query to get the results you want. No PHP required.

So, for example, a simple query might be:

<txp:smd_query query="SELECT * FROM textpattern WHERE custom_1='?region' AND custom_2='?type'" wraptag="ul" break="li">
   <txp:permlink>{title}</txp:permlink>
</txp:smd_query>

(untested)

That would give you a list of hyperlinked article titles that match the search criteria. However, I wouldn’t use that on a production site because it is potentially unsafe. You should use the urlfilter and/or urlreplace along with defaults to catch any spurious user input.

Having said all that, I think you can achieve most of the same functionality with <txp:article_custom /> and the excellent adi_gps plugin, although you won’t be able to use pagination. It also depends if you are matching the custom field contents exactly (sounds like it to me, given your usage scenario of using dropdown select lists) or if you want partial matches. That would make things slightly more involved.

Hope that helps.

mlarino · 2010-02-14 15:05:28

Thanks Stef! I will give that a try!

I will look into those 2 plugins you mention.

I Think some php will be needed in my case.
I am trying to build something more complex.

The first SELECT is Type (APARMENT, HOUSE….) the second SELECT is Option (Rent, Sale).
After the user selects the type and the option and hit “search”, they will see a Map (I will use Tooltip JS to make the regions of the map show the number of articles found for each of the regions.
The user can select a region they want and they will see the articles only from that region. (Region is a custom field also)

So here I am with no clue how to acomplish something like this, I have a PHP programer that knows how to do something like this but in a regular website, he has never used TXP and I am not sure if there is some limitation on the RAW Php that can be used in a page, or how to use TXP tags with Raw Php…

There is more…
After selecting the Region, if only 5 or less results are found, it will show those results, and on another list it will show similar results to the one selected by the user.

If he searches for A+B in REGION 1 and gets 3 results.
It will show those 3 results in one list, and the results from A+B in REGION 2 and A+B in REGION 3 in another list.

Last edited by mlarino (2010-02-14 23:20:38)

mlarino · 2010-02-15 00:59:41

If I use adi_gps wouldnt I have to write hundreds of URL combination posibilities?

aslsw66 · 2010-02-15 08:24:39

What happens (based on my imperfect knowledge) is that when you post an HTML form the name and entered value of each form field is passed through the URL.

adi_gps helps you to get each of those pairings, and you can then pass them on to smd_query to get the results from the database.

Of course, this means that the avialble entries in the select boxes must match the possible entries in the custom fields.

mlarino · 2010-02-15 08:38:01

mmm
Sounds interesting.
I got what adi_gps does, and just tested, and works great. with the form I am able to filter articles from the URL..

Now, Have no clue what I can do with smd_query, I think is too much for me :)
I defenetly need pagination, So I guess I will have to study this plugin deeper
thanks!

aslsw66 · 2010-02-15 12:56:20

I haven’t used them, but reading the help file and Stef’s website it looks like there are replacement tags to support pagination. See example 11 on his website page.

Textpattern CMS

Textpattern CMS support forum

#193 2010-02-06 23:06:19

Re: smd_query: Talk to the database directly via SQL

#194 2010-02-07 13:45:59

Re: smd_query: Talk to the database directly via SQL

#195 2010-02-07 14:11:36

Re: smd_query: Talk to the database directly via SQL

#196 2010-02-07 14:15:06

Re: smd_query: Talk to the database directly via SQL

#197 2010-02-07 21:25:43

Re: smd_query: Talk to the database directly via SQL

#198 2010-02-13 20:14:48

Re: smd_query: Talk to the database directly via SQL

#199 2010-02-14 11:08:43

Re: smd_query: Talk to the database directly via SQL

#200 2010-02-14 15:05:28

Re: smd_query: Talk to the database directly via SQL

#201 2010-02-15 00:59:41

Re: smd_query: Talk to the database directly via SQL

#202 2010-02-15 08:24:39

Re: smd_query: Talk to the database directly via SQL

#203 2010-02-15 08:38:01

Re: smd_query: Talk to the database directly via SQL

#204 2010-02-15 12:56:20

Re: smd_query: Talk to the database directly via SQL

Board footer