smd_query: Talk to the database directly via SQL

Bloke · 2010-04-19 13:48:01

atbradley wrote:

Is it adequate just to say $thisarticle_orig = $thisarticle; $thisfileorig = $thisfile; $thislinkorig = $thislink; at the top of smd_query() and then swap them back before the end?

Yup. That’s what I’d do!

I’m not clear how this works—wouldn’t you need to check for the existence of specific fields in the query result before feeding rows into $thiswhatever?

I’d just cheat and say something like this:

if (!$datatype) {
   $datatype = ($thisarticle) ? $thisarticle : NULL;
   $datatype = ($thislink) ? $thislink : $datatype;
   $datatype = ($thisimage) ? $thisimage : $datatype;
   $datatype = ($thisfile) ? $thisfile : $datatype;
}

At the end of that lot, your datatype will either be:

your hard-coded plugin attribute
NULL if you’re on a section/category/author/etc landing page — in which case you don’t have to bother saving anything
one of the contexts based on whichever one currently has content in it

The thing I dislike about this approach is that the hierarchy is a bit hit-and-miss. I’ve assumed that article is the lowest common denominator and file context trumps everything. In reality I would probably opt for a system that allowed you to specify / override the order. But I’ve not really thought it through yet.

I suppose it’s not a massive headache whichever hierarchy is chosen, because if you’re using smd_query inside a file_download_list and still want to get at the article tags you can just specify datatype in the tag and it’ll do your bidding; the automatic selection is just there as a first stab so that in 95% of cases you can ignore the attribute!

If I (or anyone else) can come up with anything better I’ll put it in the plugin :-)

sereal · 2010-05-01 17:31:02

hi Mr.Stef

one question..

how to limit Title’s characters from smd_query ?

<txp:permlink id=”{ID}”>{Title}</txp:permlink>

I use this @Gocom’s php to limit title in other section but I have no idea how to inject it into your smd_query

<txp:php> preg_match(‘/^(.{0,48})(.*)$/u’, title(array()), $match); echo $match¹; if (strlen($match²)) echo ‘…’;
</txp:php>

please advise

thanks Mr.Stef

Last edited by sereal (2010-05-01 18:26:27)

surfwax95 · 2010-05-10 03:06:34

Can anyone give me some direction on how to use smd_query to create an advanced search similar to this one ?

I’ve no idea where to start and would appreciate it greatly!

geoff777 · 2010-05-31 06:42:11

Hi Stef,

Is it possible to get a total of the visitors for a given day (ie yesterday) from the txp_log?

I already have a another table set up to hold this total and have the code worked out to insert the value of yesterdays total.

I need to get the total into the variable “visitors”.

<txp:smd_query query="INSERT INTO gmf_record (ymd,record) VALUES (CURDATE()-1, '<txp:variable name="visitors" />')" />

I’d then like to be able to get the highest value row out of this table. – The record.

Thanks
Geoff

Last edited by geoff777 (2010-05-31 06:47:26)

the_ghost · 2010-05-31 08:09:21

You should use ' instead of " for surrounding attr for parsing tag inside attr

<txp:smd_query query='INSERT INTO gmf_record (ymd,record) VALUES (CURDATE()-1, "<txp:variable name="visitors" />")' />

And IIRC there replacements for using variable in attr – read for help or wait for Stef :)

Last edited by the_ghost (2010-05-31 08:09:47)

geoff777 · 2010-05-31 08:27:12

Hi thanks Ghost,

That query is the piece of the puzzle that actually works but I´ll try it your way too. :-)

I’m playing with this …

SELECT COUNT(DISTINCT ip ) FROM txp_log WHERE DATE( 'time' ) = ( CURDATE( ) -1 )

which works returning a unique visitor count in PHP MyAdmin.

I don’t know how to handle the result from smd_query how do I get the total into a txp:variable? … I’ll continue playing …

Last edited by geoff777 (2010-05-31 10:32:19)

atbradley · 2010-05-31 17:44:59

Geoff, try something like this:

<txp:smd_query query="SELECT COUNT(DISTINCT ip ) AS `cnt` FROM txp_log WHERE DATE( 'time' ) = ( CURDATE( ) -1 )">
<txp:variable name="visitors">{cnt}</txp:variable> 
</txp:smd_query>

<txp:smd_query query='INSERT INTO gmf_record (ymd,record) VALUES (CURDATE()-1, "<txp:variable name="visitors" />")' />

maniqui · 2010-06-04 02:26:29

First time using this thing, and there is already a mix of feelings in my heart: that I’m cheating my beloved TXP, that I’m doing nasty things, that this is a powerful thing that I won’t be able to handle… :)

Anyway, Bloke, can you feel it already? It’s coming… it’s… a request!

I’ve noticed that smd_query doesn’t “understand” article tags, , like <txp:body or txp:title, and txp:if_article_*, nor in form nor in container mode (TXP will raise the typical “article tag not in article context” notice).
Of course, the wonderful {replacement_tags} are everything anyone should ever need to output article chunks.
But then, the DRY principle…
Yes, there is a cheat, a workaround to overcome this:

<txp:smd_query column="ID" table="textpattern" where="..." ><txp:article_custom id="{ID}" form="a_form_with_realtxp_tags" /></txp:smd_query>

But then, this seems not just redundant, but probably (I don’t know, really), it hits the database more than necessary.

Being that your plugins usually do a lot of stuff and cover lots of corners, I was amazed about smd_query not understanding real txp article tags.
So, I wonder if you left them out on purpose, and/or if I’m missing something obvious.

Thanks.

edit: btw, minor error on the help. On hashsize attr, it says: “unique reference to the current smd_xml”.

Last edited by maniqui (2010-06-04 02:34:26)

maniqui · 2010-06-04 02:46:48

BTW, it will be less query-intensive if the above workaround is done the other way, by nesting txp:smd_query inside txp:article_custom@‘s id attribute, like this:

<txp:article_custom id=’<txp:smd_query column=“ID” table=“textpattern” where=”…” break=”,” >{ID}</txp:smd_query>’ form=“a_form_with_realtxp_tags” />

(note the break="," attribute)

atbradley · 2010-06-04 03:10:54

Maniqui,

Earlier in this forum I posted a modified version of smd_query that does what you mention. I’ve been meaning to do an updated version with the adjustments Bloke mentioned, but haven’t gotten around to it yet (hopefully this weekend).

In the meantime, the version there works in the setting I wrote it for, and might help you.

Bloke · 2010-06-04 08:19:59

maniqui wrote:

Being that your plugins usually do a lot of stuff and cover lots of corners, I was amazed about smd_query not understanding real txp article tags.

Yeah on the surface it seems an oversight, but I did have a legitimate reason not to do so (at the time — atbradley has changed my view on this). The reason? You might not always want to select * from textpattern. What if you chose an insert query? Or a select * from txp_users? To parse the query and determine which table it hits — and then do something special if it matches — burdens the plugin with extra processing for little gain.

However, atbradley’s notion of an opt-in attribute that tells the plugin “I am using the textpattern table so go ahead and populate the relevant stuff that allows me to use the built-in tags” is a nice way round it. Puts the onus back on you, so if it falls apart it’s nothing to do with me ;-)

I’m tweaking the concept a little. The problem is that it puts the burden of responsibility on you for extracting all the columns that the internal TXP functions require. For instance, if you wish to populate the article data you MUST do this:

<txp:smd_query query="SELECT *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod FROM textpattern" />

Failure to do that will result in warnings/errors. fwiw, this is my reasoning behind raising Issue 19 to help shield people from this kind of thing if they forget to select a required column (any ideas there welcome, btw!)

I’ve also made some other enhancements — the most major of which is that form now overrides the container (which it should have done from the start) but it allows you to use <txp:else /> at the same time if you wish; as if the plugin was used as a container. For example:

<txp:smd_query query="some query" form="query_form">
<txp:else />
Nothing matches today
</txp:smd_query>

… sometime later…

<txp:smd_query query="a different query from the same table" form="query_form">
<txp:else />
Please select an option from the list first
</txp:smd_query>

Currently that’s not possible and you’d need to specify your form’s contents in the container, potentially repeating it for different queries simply because you wish to display a different message. This is a real typing saver and allows form re-use in a more natural, TXPish way. I believe this is the first plugin to employ dual-purpose forms/containers with <txp:else /> due to its special nature as the laziest plugin ever! Time will tell if it works, but I’ve already found an application for it.

Once I’m happy with that part and have factored in atbradley’s patch I’ll release the next version. Hold tight or use his version until then :-)

minor error on the help

Thanks, fixed from next revision.

Last edited by Bloke (2010-06-04 12:16:39)

the_ghost · 2010-06-13 21:34:14

Does the plugin supports breaking query in lines? On my site i tried this:

	<txp:smd_query query="
		SELECT custom_7 
		FROM moe_textpattern 
		WHERE custom_7 <> '' 
		GROUP BY custom_7 
		HAVING COUNT(*)>4 
		ORDER BY custom_7 asc" debug="2" break=",">{custom_7}</txp:smd_query>

I get this in debug (nothing is normal output):

++ URL FILTERS ++


++ DEFAULTS ++
array (
)

		SELECT custom_7 
		FROM moe_textpattern 
		WHERE custom_7 <> '' 
		GROUP BY custom_7 
		HAVING COUNT(*)>4 
		ORDER BY custom_7 asc

After changing query attr to single line – everything works.

<txp:smd_query query="SELECT custom_7 FROM moe_textpattern WHERE custom_7 <> '' GROUP BY custom_7 HAVING COUNT(*)>4 ORDER BY custom_7 asc" break=",">{custom_7}</txp:smd_query>

I uploaded debug info here (it’s to big for forum i think) – link to debug

Textpattern CMS

Textpattern CMS support forum

#229 2010-04-19 13:48:01

Re: smd_query: Talk to the database directly via SQL

#230 2010-05-01 17:31:02

Re: smd_query: Talk to the database directly via SQL

#231 2010-05-10 03:06:34

Re: smd_query: Talk to the database directly via SQL

#232 2010-05-31 06:42:11

Re: smd_query: Talk to the database directly via SQL

#233 2010-05-31 08:09:21

Re: smd_query: Talk to the database directly via SQL

#234 2010-05-31 08:27:12

Re: smd_query: Talk to the database directly via SQL

#235 2010-05-31 17:44:59

Re: smd_query: Talk to the database directly via SQL

#236 2010-06-04 02:26:29

Re: smd_query: Talk to the database directly via SQL

#237 2010-06-04 02:46:48

Re: smd_query: Talk to the database directly via SQL

#238 2010-06-04 03:10:54

Re: smd_query: Talk to the database directly via SQL

#239 2010-06-04 08:19:59

Re: smd_query: Talk to the database directly via SQL

#240 2010-06-13 21:34:14

Re: smd_query: Talk to the database directly via SQL

Board footer