smd_query: Talk to the database directly via SQL

aslsw66 · 2011-06-17 00:57:57

What happened was that I was getting different results when using the {replacement tags} inside a smd_query block than when I used the same tags inside a <txp:php> block. I actually had both outputs appearing on the same page, which made it really confusing.

It seems that somehow smd_query can take a variety of MySQL field types and output them nicely. However, when using raw PHP you need to pay a lot more attention to the field type and understand how PHP treats it. In this case, I was extracting a DATETIME field using smd_query – the native replacement tag neatly shows “2011-06-19” but the same tag inside <txp:php> showed “1986”.

By the way, the reason I want to do this inside <txp:php> is so that I can do some calculations and comparisons on the date. None of the calculation plugins I have looked at do this – they seem to only operate only on integers.

Bloke · 2011-06-17 08:52:18

aslsw66 wrote:

I was getting different results when using the {replacement tags} inside a smd_query block than when I used the same tags inside a <txp:php> block

Yes, with dates you can get mismatches simply because a DATETIME field is a string ‘YYYY-MM-DD HH:MN:SS’. The native replacement will output that and it’ll look fine. But if you assign it to a variable in PHP then you need to be careful to assign it to a string first, convert it to a unix timestamp (probably using strtotime()), do some calculations on that value and then use strftime() to reformat the number back to a date for display.

If you want any assistance with this, please post some code and I’ll see what I can do.

None of the calculation plugins I have looked at do this – they seem to only operate only on integers.

If you have smd_calendar installed, that has <txp:smd_cal_now /> which can take either a date string or an integer and apply an offset. The fact it’s called smd_cal_now is a little misleading because that’s only its default modus operandi; you can actually specify when now is! But it’s overkill to install that plugin just for this one function so in that instance a line or three of PHP is your best bet.

aslsw66 · 2011-06-23 12:18:54

Thanks for the offer, but it’s all good for now. At least by hacking around I’m (hopefully) learning some more about PHP, MySQL and how Textpattern works. If I let you do all of the heavy lifting, then where would I be?

But seriously, smd_query is the most powerful plugin I’m using.

jayrope · 2011-07-06 12:14:54

Hello,

is smd_query capable of writing the output of another plugin’s tag into a dedicated article body? If so, could this be done automatically in regular intervals, almost like a Cron job?

Thanx for any hints!

Bloke · 2011-07-06 12:49:12

jayrope wrote:

is smd_query capable of writing the output of another plugin’s tag into a dedicated article body?

smd_query is capable of executing any SQL so, yes. Something like this (untested) :

<txp:variable name="body_content"><txp:some_tag /></txp:variable>
<txp:smd_query query='INSERT INTO textpattern
     SET body=''<txp:variable name="body_content" />''
        , body_html=''<txp:variable name="body_content" />''
     WHERE ID=<txp:article_id />' />

If so, could this be done automatically in regular intervals, almost like a Cron job?

Nope. Well, not reliably on a schedule at least.

However, I have successfully used a visitor click as a ‘trigger’ to call a script. For example, write a form that’s called on every page. That form contains a counter which is incremented by 1 each time the form is called (i.e. on every page visit); rvm_counter is good for this. When it reaches a certain value, that form triggers your SQL and sets the counter back to zero.

Tweak the trigger point depending on your traffic. e.g. on high traffic sites, it might run every 100 clicks (remember a search engine bot hit is a ‘click’).

The other way to do it is to actually write a cron job which calls a fixed page/article (in a hidden section?) that contains your SQL code. But that’s getting convoluted and there’s probably a better way to achieve what you want.

jayrope · 2011-07-06 14:28:01

Stef, thank you very much. The idea is to write a tag cloud into a dedicated article body to reduce server load in high traffic situations. So if i could set the script to run upon an author saving an article it would already work as desired. Wouldn’t even need a Cron job.
Is there a built in way in smd_query eventually, takign the act of saving an article in the backend as the trigger for the query?
Thank you again!

Bloke · 2011-07-06 14:30:57

jayrope wrote:

Is there a built in way in smd_query eventually, takign the act of saving an article in the backend as the trigger for the query?

Not natively, as the plugin is public side only. You’d have to do it on a dashboard page, which is more hassle than it’s worth.

In this case you’d be better off writing a small plugin to hook into the article.save step and do it directly in a few lines of PHP and a call to safe_insert() or safe_update() imo.

jayrope · 2011-07-06 15:24:37

Stef, uhm, good point. Thanx a bunch for the roadmap!

frickinmuck · 2011-08-04 19:33:47

Having trouble making this work in all instances:

<txp:if_section name="systems-services">
<txp:smd_query query='select * from pet_txp_file where description like "%<txp:title />%"' limit='1'>
<h4>For Related Documents, See Our <a href="<txp:site_url />technical-reference/4/technical-reference?crit=<txp:title />&event=file&step=file_list&search=Go">Technical Reference Guide</a></h4>
</txp:smd_query>
</txp:if_section>

The idea is to query for files that are related (using the article title, which is always the name of the product), and if there are related files in the database, display the link. Problem is, if there is a space in an article title, it doesn’t work. Is this route just totally unusable for that reason? Is there any way around it?

Last edited by frickinmuck (2011-08-04 19:36:02)

jakob · 2011-09-09 14:03:33

Two quick questions:

Is there a way of forcing the pageform to appear permanently (for example to display page information) even if there is only a single page? In my experiments it only shows if there is more than one page.
Is there a way to treat pagepos “above” differently from “below”, for example to integrate the top navigation within a header element? I tried with <txp:smd_if field="{smd_thisrow}" operator="eq" value="1"> but I guess the pagination is independent of a particular row.

aslsw66 · 2011-10-18 09:03:04

Stef, this is just something I have discovered but not necessarily a bug… an enhancement maybe?

I have a table where the fields are named the same but have a single digit on the end eg. “crew1” to “crew8” [FN]. I was using rah_repeat to iterate over the numbers 1 to 8 and pull out the value. The problem is that using rah_repeat_value inside the replacement variable converts the variable to just a string ie. not a true variable anymore.

For example, {crew<txp:rah_replacement_value} outputs '{crew1}' as a string, instead of what my outer smd_query grabbed.

[FN] Yes, I know it is bad database design to do this. It would be better to have a simpler and more flexible table with each crew member in a single record. But the problem I have is inserting multiple records into a database from a single form submission. I’m using mem_form which only allows a single record at a time and I’m not smart enough to figure out how to do a multiple insert from one submit on a form.

[EDIT]
I know I can solve my original issue by just repeating each line manually. It just doesn’t look as neat!

Last edited by aslsw66 (2011-10-18 09:07:46)

Bloke · 2011-10-18 09:12:04

aslsw66 wrote:

For example, {crew<txp:rah_replacement_value} outputs '{crew1}' as a string, instead of what my outer smd_query grabbed.

Does it change things if you use the preparse="1" attribute? See example 9 in the help.

aslsw66 · 2011-10-18 09:59:34

Oh, that’s what it’s there for!

This looked like it would work but then broke some existing functions that did work. You see, each crew also has a ‘crew_leader’ and ‘driver’ and the display for those sits outside the rah_repeat function because they have a known, non-repeating name.

So, I separated out these into two separate queries – one for the two known names, and the other for the consecutively name crew fields. Adding preparse="1" works fine when I just want to display the results, but if I want the result to become the selected attribute for a mem_form_select then preparse stops that happening – just nothing gets passed through to the form.

Plan Z is to just run a separate query for each one.

Bloke · 2011-10-18 10:56:38

aslsw66 wrote:

Oh, that’s what it’s there for!

:-)

This looked like it would work but then broke some existing functions that did work. You see, each crew also has a ‘crew_leader’ and ‘driver’ and the display for those sits outside the rah_repeat function

Weird. It should work, umm, I think. I just ran a (simplified) test:

<txp:variable name="tester">ID: {ID}<br />Title: {Title}<br /></txp:variable>

<txp:smd_query query="select * from textpattern where ID < 10" preparse="1">
<txp:variable name="tester" />
  CUSTOM 1: {custom_1}<br />
</txp:smd_query>

and it displayed the three values for each of the 10 articles. Where custom_1 was missing it didn’t get displayed. Not sure why it didn’t display your known-named replacements.

Can you ‘pre-compute’ the list of {crewN} items in a txp:variable and do it like I just did? Or does that defeat the purpose of doing it inside the query to begin with?

aslsw66 · 2011-10-18 14:36:38

I think the issue is getting this to work nicely across three different plugins. At the moment, this is the structure:

outer plugin: smd_query to get the values for crewN,
middle plugin: rah_repeat to interate over the values 1-8, and insert each value into the mem_form_select,
inner plugin: mem_form (specifically mem_form_select) to output the form for the web.

This form has two modes:

new – nothing is prepopulated, easy.
edit – current value should be assigned to the select attribute.

So, if I precompute the list of {crewN} items on the outside, I need to somehow link that to the list of rah_repeat values.

BTW, if I use preparse on the outer plugin, it returns the correct values but then the mem_form_select ignores it.

Last edited by aslsw66 (2011-10-18 14:38:58)

Textpattern CMS

Textpattern CMS support forum

#256 2011-06-17 00:57:57

Re: smd_query: Talk to the database directly via SQL

#257 2011-06-17 08:52:18

Re: smd_query: Talk to the database directly via SQL

#258 2011-06-23 12:18:54

Re: smd_query: Talk to the database directly via SQL

#259 2011-07-06 12:14:54

Re: smd_query: Talk to the database directly via SQL

#260 2011-07-06 12:49:12

Re: smd_query: Talk to the database directly via SQL

#261 2011-07-06 14:28:01

Re: smd_query: Talk to the database directly via SQL

#262 2011-07-06 14:30:57

Re: smd_query: Talk to the database directly via SQL

#263 2011-07-06 15:24:37

Re: smd_query: Talk to the database directly via SQL

#264 2011-08-04 19:33:47

Re: smd_query: Talk to the database directly via SQL

#265 2011-09-09 14:03:33

Re: smd_query: Talk to the database directly via SQL

#266 2011-10-18 09:03:04

Re: smd_query: Talk to the database directly via SQL

#267 2011-10-18 09:12:04

Re: smd_query: Talk to the database directly via SQL

#268 2011-10-18 09:59:34

Re: smd_query: Talk to the database directly via SQL

#269 2011-10-18 10:56:38

Re: smd_query: Talk to the database directly via SQL

#270 2011-10-18 14:36:38

Re: smd_query: Talk to the database directly via SQL

Board footer