Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
#265 2011-03-31 12:29:31
Re: smd_query: Talk to the database directly via SQL
Is it possible to insert a variable directly into the query used by smd_query?
What I have is a list of members. Using ign_password_protect, I have assigned inactive members with a privilege of 0, and active members with a privilege of 1.
I would like to be able to display a list of (1) all members, (2) active members or (3) inactive members, depending on a value passed through the URL. But the query won’t accept a textpattern variable using the notation ‘?variablename’.
Is this possible at all?
Offline
#266 2011-06-16 03:43:26
Re: smd_query: Talk to the database directly via SQL
The only way I managed to get this work was to create separate queries depending on each possible URL variable (using smd_if). It doesn’t feel very efficient, but it works.
Next question…
Is it possible to access the replacement tags in PHP? I am using smd_query to extract a date, and also a number from a custom field.
What I want to be able to do is subtract the number from the date and then compare to the current date (it’s like a customisable count-down). I can’t find an easy way to do this in TXP itself (or with any of the plugins) so I figure I need to do it within PHP. But I don’t know how to access the replacement tags from my smd_query in PHP.
Offline
#267 2011-06-16 08:36:30
Re: smd_query: Talk to the database directly via SQL
Sorry I missed this one aslsw66. You should be able to use ?variables ok in the plugin. As long as it’s been set as a <txp:variable> first you should be good to go. If it’s not working can you post your code please and I’ll see if I can figure out why.
Same goes for the tags inside php. If your code is in the smd_query container inside <txp:php> tags you can just use the {replacements} directly becuase they’re replaced before the bock is parsed. Again, if it’s not working or you need a hand, post some code and we’ll hammer out a solution.
Last edited by Bloke (2011-06-16 08:37:20)
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Txp Builders – finely-crafted code, design and Txp
Offline
#268 2011-06-17 00:57:57
Re: smd_query: Talk to the database directly via SQL
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.
Offline
#269 2011-06-17 08:52:18
Re: smd_query: Talk to the database directly via SQL
aslsw66 wrote:
I was getting different results when using the
{replacement tags}inside asmd_queryblock 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.
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Txp Builders – finely-crafted code, design and Txp
Offline
#270 2011-06-23 12:18:54
Re: smd_query: Talk to the database directly via SQL
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.
Offline
#271 2011-07-06 12:14:54
Re: smd_query: Talk to the database directly via SQL
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!
A hole turned upside down is a dome, when there’s also gravity.
Offline
#272 2011-07-06 12:49:12
Re: smd_query: Talk to the database directly via SQL
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.
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Txp Builders – finely-crafted code, design and Txp
Offline
#273 2011-07-06 14:28:01
Re: smd_query: Talk to the database directly via SQL
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!
A hole turned upside down is a dome, when there’s also gravity.
Offline
#274 2011-07-06 14:30:57
Re: smd_query: Talk to the database directly via SQL
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.
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Txp Builders – finely-crafted code, design and Txp
Offline
#275 2011-07-06 15:24:37
Re: smd_query: Talk to the database directly via SQL
Stef, uhm, good point. Thanx a bunch for the roadmap!
A hole turned upside down is a dome, when there’s also gravity.
Offline
#276 2011-08-04 19:33:47
- frickinmuck
- Member
- Registered: 2008-05-01
- Posts: 118
Re: smd_query: Talk to the database directly via SQL
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)
The AI does not hate you, nor does it love you, but you are made out of atoms which it can use for something else.
Offline