smd_query: Talk to the database directly via SQL

Bloke · 2008-06-10 13:01:08

Sheru wrote:

I did not find variable under Extensions tab. Where is it?

Sorry, you have to install and activate the smd_vars plugin first :-) Then when you go to the Extensions tab you’ll see a ‘Variables’ sub-tab. There you will find an “Install table” button. Click it. Then you will have the ability to add variables to textpattern as I wrote above.

I am using 4.0.6 version. Do I need to wrap smd_query?

Yes, more than likely. If you try it and you get errors or odd " and > characters appearing in the output, install the asy_wondertag plugin and do:

<txp:asy_wondertag>
  <txp:smd_query ........ />
</txp:asy_wondertag>

That should help.

Sheru · 2008-06-11 04:21:22

Warm Greetings from Sheru, Dear Stef, Thank you a lot for your answers and for your precious time. I have received huge information on smd_query and smd_vars and it’s implementations on above. I will now try to use it. I will come in touch with you.

Thank you so much for your kind help.
Sheru

Sheru · 2008-06-11 08:07:30

Dear Stef, I am stuck..

Stef Wrote
bq. So what you do is put your (X)HTML search form on a page/article and direct the HTML <form>‘s action parameter to the same article. Then you will need some conditional logic at the top of the page to detect if the user has submitted the form or not.

<txp:asy_wondertag>
<txp:smd_if field="urlvar:criteria" operator="isused">
  <txp:smd_query query="<txp:smd_var_get name="files_query" />"
        form="show_results" wraptag="table" break="tr"
        label="Search results" labeltag="h3" />
<txp:else />
  // Display the page content and search box here so visitors can try again 
// I have added form this way
<form action="../downloads" method="get">
<tr><td>Search<td /><td><input type="text" name="criteria" value="criteria" /><td /><td><input type="submit" name="Search" value="Search" /><td /><tr />
</form>
</txp:smd_if>
</txp:asy_wondertag>

- I have added the query on Extensions>Variables tab.
- Added the show_results page

When clicked Search button, following url displays
http://localhost:8888/testsite/downloads/?criteria=doc&Search=Search

And does not display results. Remains on the same page.
Could not figure out.

Could you please help me?
Thank you so much for your kind help
Sheru

Last edited by Sheru (2008-06-11 08:08:46)

Bloke · 2008-06-11 09:19:58

Hmm, try this:

<txp:smd_if field="urlvar:criteria" operator="isused">
 <txp:asy_wondertag>
  <txp:smd_query query="<txp:smd_var_get name="files_query" />"
        form="show_results" wraptag="table" break="tr"
        label="Search results" labeltag="h3" />
 </txp:asy_wondertag>
<txp:else />
  // Display the page content and search box here so visitors can try again 
// I have added form this way
<form action="../downloads" method="get">
<tr><td>Search<td /><td><input type="text" name="criteria" value="criteria" /><td /><td><input type="submit" name="Search" value="Search" /><td /><tr />
</form>
</txp:smd_if>

The asy_wondertag might be preventing smd_if (or the rest of the page) from working. It is best to only use asy_wondertag around the one tag that causes problems.

If that does not work, verify that it is definitely calling smd_if by adding debug="1" to it. You should see a load of diagnostic output. Check that the urlvar is being read properly and tested. Post the results of the debug if you can’t figure out what it means.

Sheru · 2008-06-11 10:51:02

Dear Stef, Thank you so much for your answers to solve my problem.

I used the code as you have suggested..

<txp:smd_if field="urlvar:criteria" operator="isused">
 <txp:asy_wondertag>
  <txp:smd_query query="<txp:smd_var_get name="files_query" />"
        form="show_results" wraptag="table" break="tr"
        label="Search results" labeltag="h3" />
 </txp:asy_wondertag>
<txp:else />
  // Display the page content and search box here so visitors can try again 
// Added here the content here
<form action="../downloads" method="get">
<tr><td>Search<td /><td><input type="text" name="criteria" value="criteria" /><td /><td><input type="submit" name="Search" value="Search" /><td /><tr />
</form>
</txp:smd_if>

While adding above code and using asy_wraptag in single tag produced blank screen. But as I added in </txp:smd_if debgu=“1”>, this time, everything( content + searchbox ) displayed with errors which is below

" form="show_results" wraptag="table" break="tr" label="Search results" labeltag="h3" />

Could not figure out.

Thank you for your great help and precious time.
Sheru

Bloke · 2008-06-11 11:06:52

Sheru wrote:

While adding above code and using asy_wraptag in single tag produced blank screen. But as I added in </txp:smd_if debgu=“1”>, this time, everything( content + searchbox ) displayed with errors

That’s a shame, it sounds like asy_wondertag cannot handle the smd_query or smd_var_get call and is not very happy :-(

I’m not sure what to suggest now. Maybe this use of smd_vars will really only be of use in TXP 4.0.7 when the new tag parser is available. I will have to test it anyway soon so I will report if I find anything in the meantime.

I could really do with somebody letting me know what core functions I should be using to minimise the risk of SQL injection; then I could offer you the ability to plug the values directly into smd_query without smd_vars at all. If I get a chance later I might experiment some more with that but I’m working on a video today so I don’t have much time spare, sorry.

Sheru · 2008-06-13 03:31:04

Warm Greetings! Dear Stef thank so much for your answers to my query. Sorry, I did’t reply quickly, I was on leave yesterday.
I love to learn something and have learnt many things with you.

Hope smd_query plugin will have ability to search values using urlvars without smd_vars. Let me know smd_query plugin status in coming days.

I will have to test it anyway soon so I will report if I find anything in the meantime.

I want to hear from you about this.

Thank you Stef for your generous help. You are great.
Will be in touch with you.
Sheru

redbot · 2008-06-14 15:42:49

Hi Bloke,
I seem to have a problem with this plugin (I hope I’m not missing something obvious here)
If I use this code:

<txp:smd_query 
	column="Title"
    table="textpattern"
    where="section='my_section' AND custom_1='?custom_1'"
    form="my_form"  />

I have no output.
According to the plugin help (if I’ve not misunderstood) it shoud output a list of titles belonging to section “my_section” and having the same “custom_1” value of the currently displayed article.
I found the culprit is the “custom_1=’?custom_1’” part, if I omit it, it works.
It works also if I use:

custom_1='a_specific_value'

I have tried using the query attribute and the result is the same.
Any advice would be appreciated.
Thanks

P.S. the third example in the plugin help refers to “custom3” instead of “custom_3”. Maybe there is an error

Last edited by redbot (2008-06-14 15:45:52)

Bloke · 2008-06-14 16:52:11

redbot wrote:

I have no output.

My guess (and it is a guess) is that you have to supply the name of your 1st custom field in the ? attribute. i.e. if you’ve defined custom_1 as product_description in the admin prefs you’d need:

"custom_1='?product_description'"

[ for anyone not familiar with the intricacies of the textpattern table, it has 10 columns named custom_1 thru custom_10 and these are what you use when referring to them on the left-hand-side of queries. But the ? notation is specific to my plugins and grabs values from the custom fields after they have been read from the database and put on the page. At that point, they have been associated with the names you defined and should thus be referenced by that name ]

It’s a curious hybrid of syntaxes and, yes, it’s confusing (sorry) but doing it this way enables automatic support for glz_custom_fields and consistency (rightly or wrongly!) among the smd_ plugins. Plus it’s easier for me to code :-)

So, in answer to your question about the plugin help, it’s not an intentional mistake but it could be clearer so thank you for pointing that out. I’ll adjust it in the next version.

Last edited by Bloke (2008-06-14 16:54:41)

redbot · 2008-06-14 17:03:32

Thanks Bloke it works!!

merz1 · 2008-06-16 12:59:55

Stef: Just to get the syntax clear (I am not yet using smd_query)

"custom_1='?individual custom field name'"
"custom_1='custom_1'"

Both possible?

Bloke · 2008-06-16 14:23:58

merz1 wrote:

"custom_1='?individual custom field name'"

In theory yes. Spaces are allowed in custom field names so that should resolve properly. Can’t remember if I tested it but I think I did.

"custom_1='custom_1'"

Yes, if you have not renamed custom_1 from its default.

Textpattern CMS

Textpattern CMS support forum

#37 2008-06-10 13:01:08

Re: smd_query: Talk to the database directly via SQL

#38 2008-06-11 04:21:22

Re: smd_query: Talk to the database directly via SQL

#39 2008-06-11 08:07:30

Re: smd_query: Talk to the database directly via SQL

#40 2008-06-11 09:19:58

Re: smd_query: Talk to the database directly via SQL

#41 2008-06-11 10:51:02

Re: smd_query: Talk to the database directly via SQL

#42 2008-06-11 11:06:52

Re: smd_query: Talk to the database directly via SQL

#43 2008-06-13 03:31:04

Re: smd_query: Talk to the database directly via SQL

#44 2008-06-14 15:42:49

Re: smd_query: Talk to the database directly via SQL

#45 2008-06-14 16:52:11

Re: smd_query: Talk to the database directly via SQL

#46 2008-06-14 17:03:32

Re: smd_query: Talk to the database directly via SQL

#47 2008-06-16 12:59:55

Re: smd_query: Talk to the database directly via SQL

#48 2008-06-16 14:23:58

Re: smd_query: Talk to the database directly via SQL

Board footer