smd_query: Talk to the database directly via SQL

Bloke · 2008-05-22 12:47:20

Ever wanted to just do a simple or complex query that’s not covered by a tag? Use this as a stop-gap solution. It allows you to run pretty much any SQL query you like and parse each row one by one via a form/container.

You can of course specify that any input strings to the SQL queries are read from the current article’s fields, TXP variables or the URL line which can probably render some stupidly flexible drill-down capabilities. It’s utterly, utterly mad. Check the help and examples for some wacky and not-so-wacky ideas, then set your mind to freewheel.

Download smd_query

If you spot any mishaps or can think of enhancements, features etc, give me a shout.

WARNING: Caveat blow-up-tor. Reading stuff from the database is fine, but you are on your own if you use the plugin to write back to the database. It has been tested in this mode but take care with your queries; if your database explodes as a result of using this plugin, seek medical attention. You have been warned :-)

Revision history
————————

All available versions and changes are listed here. Each entry indexes the relevant post(s) in the thread to learn about the features.

16 May 2020 | 0.6.0 | Tag registration. Use article_format_info() and link_format_info() internally (if available). Add var_prefix. Add raw_vals for backwards compatibility but its use is discouraged. Add {smd_usedrow} to number only rows you use (thanks, aslsw66). Add <txp:smd_query_info /> tag for those that prefer the security of a tag over {replacements}. Improve security (thanks, Gocom).
31 Aug 2010 | 0.5.0 | form overrides container. Container’s else automatically works in forms. Added populate (thanks atbradley). Fixed PHP 4 compatibility and added html_id and breakclass (thanks makss). SHOW defaults to output mode.
17 Jan 2010 | 0.4.1 | Added hashsize, mode and {smd_query_unique_id}.
05 Dec 2009 | 0.4.0 | Added a few more replacement tags and two conditional tags. Removed pgonly.
02 Dec 2009 | 0.3.0 | Fixed a few bugs and added strictfields (thanks speeke). Added direct pagination support.
16 Oct 2009 | 0.2.2 | Added escape (thanks jakob) and preparse attributes.
17 Mar 2009 | 0.2.1 | Added $_SERVER support.
23 Nov 2008 | 0.2.0 | Added URL var and TXP var support.
14 Jul 2008 | 0.1.2 | Added txp:else support in container (thanks jakob). Added silent and count attributes, and the replacement tags {smd_rows} {smd_thisrow} {smd_thisindex}.
23 May 2008 | 0.1.1 | Allowed maths in queries and fixed WHERE clause to default to entire table if none supplied (both thanks jm). Added more detailed file and link support.
22 May 08 | 0.10 | Initial release.

Last edited by Bloke (2020-05-16 10:27:24)

redbot · 2008-05-22 12:56:03

I can’t believe you really did it! Thanks!!!
P.S. I’m going to try as soon as I have some free time

colak · 2008-05-22 13:00:25

This looks like it’ll be a very useful plugin. Don’t know how I can utilise it yet but i’ll think of a way:)

Thanks stef

Bloke · 2008-05-22 13:03:45

colak wrote:

Don’t know how I can utilise it yet but i’ll think of a way:)

Hehehe, no probs. Play when you get time.

Example 3 in the help is one that I see requests for quite a lot in the forums: the count for a number of articles. With this, it’s fairly easy to make such a list, and has the added benefit that it won’t list categories that don’t have articles in them. That alone’s worth the admission price (umm, free!)

Last edited by Bloke (2008-05-22 13:04:49)

uli · 2008-05-22 13:07:23

Stef, I know many a ways of using this, and I can’t get that foolish grin off of my face! Thanks, man!!

Bloke · 2008-05-22 13:26:38

uli wrote:

Stef, I know many a ways of using this

Looking forward to seeing what you can come up with and sharing with the community to get us out of those tight corners!

The idea had been floating round my head for ages, but I coudn’t figure out a neat way of processing the rows. After working on smd_gallery it became clear how to approach it and from there it was just a morning of poking around and (the part that took most time) coming up with a few interesting SQL queries to show it off.

It’s fair to say at this juncture, that the plugin would not exist were it not for the pioneering (dare we say devious?) work and mind of our very own Mr Wetzlmayr. All props and kudos to him, thank you. *applause*

uli · 2008-05-22 13:48:11

Geez, that thing’s still warm! And has debugging mode! And outputs not only words as result but shoves anything it can grab into forms … Didn’t expect that much!
I know you’ll have a good day the next days! :)

bloke wrote

Looking forward to seeing what you can come up with and sharing with the community to get us out of those tight corners!

Nothing to share so far but I think some of these popular but unfotunately neglected plugins the kind of chh_related_articles can rest in peace now, and Coke’s mailbox will get emptier.

May destroy your database if used wrongly. Backup, backup backup…

Filtering for commands that could cause serial damage would be fine. Or did you intend “simple mode” to do “like so”?

BTW, do you have an URL at hand to some SQL tuts and examples site, in order to reroute all those SQL questions to come? ;)

Bloke · 2008-05-22 14:59:15

uli wrote:

Filtering for commands that could cause serial damage would be fine. Or did you intend “simple mode” to do “like so”?

Simple mode is query-only so you’d be hard pressed to do damage that way. “The Beast” query mode, however, is just a raw SQL query with all that can entail. I’ve not filtered anything (me? lazy?!) and not even tried using an INSERT, UPDATE or DELETE in there yet. I suspect the output won’t be an array so the form will blow up. But that’ll be the least of your worries… ;-)

do you have an URL at hand to some SQL tuts and examples site, in order to reroute all those SQL questions to come? ;)

Yes :-D

uli · 2008-05-22 15:23:11

Yes

I know that one, but never liked learning that way, eeek!! The number of examples over there is somewhat insufficient or else over-specialized. And I learn best from examples.

Would I be the first one to be sent there, BTW, if I’d asked whether smd_query is good for constructing something like <txp:article offset="-1" />?
And even the second one, if I’d ask what AS txc resp. txc.name in example 3 means?

Bloke · 2008-05-22 15:35:11

uli wrote:

I learn best from examples.

I’ll see if I can put together some more cheeky examples sometime. Anyone can chime in if they have found a good use of the plugin.

smd_query is good for constructing something like <txp:article offset="-1" />?

Not sure, I’ll have to think about it. You might be better off with the neighbour plugin from jmd. Can’t remember what it’s called offhand.

what AS txc resp. txc.name in example 3 means?

It’s just a shorthand way of naming the tables you use in the query. If you do it without the AS you have to keep repeating the full table name: textpattern, txp_category etc. I just shortened them to txp and txc. See above point about being lazy :-)

redbot · 2008-05-22 16:57:25

So – amongst many other things – I will now be able to perform every sort of single and multiple search (using attribute “query”), even in a non-txp table put in txp database, and without writing a single line of PHP code. Terrific.

jakob · 2008-05-22 17:25:19

(Ooops), you did it again!

els · 2008-05-22 17:48:30

uli wrote:

constructing something like <txp:article offset="-1" />?

As far as I know, you can already do that. At least, I’m doing it :)

uli · 2008-05-22 18:29:52

Els wrote:

<txp:article offset="-1" />?
As far as I know, you can already do that. At least, I’m doing it :)

Don’t remember what combination of tags/attributes I’d used exactly, but it threw up errors. So, thanks, Els!
(Odd that I posted immediately before you and never remarked this.)

Thanks for explaining, Stef!
I know jmd_neighbor, but as of now it is limited to utilizing ID as criterion.
I don’t have SQL-think adopted, but I fear with smd_query it’d need two steps to come to the desired results (i.e. «nah!»). For now there seems to be Els’ solution, but I’d really like to find it out using SQL.

Some weeks ago I’ve been asked if I could do some website that should be able to read from and feed data into a db. Do you have plans on integrating form elements in order to also cover such an active part in a specific way? [Edit: Hmm … wondertags … the new parser … ?]

Last edited by uli (2008-05-22 18:45:35)

Bloke · 2008-05-22 19:12:57

uli wrote:

…to read from and feed data into a db. Do you have plans on integrating form elements in order to also cover such an active part in a specific way?

I hadn’t planned on it. This is primarily (and I’d be tempted to say exclusively) used for pulling hard-to-get data out of the TXP database and chucking it on the screen. Of course, since it’s parsing a form there’s no reason you (or I) couldn’t put tags in there that wrote back to TXP or any other DB as long as a connection has already been made. Heck, there’s no reason you couldn’t put another call to smd_query inside your form that does an INSERT based on data read from the previous smd_query. No idea what it would do, but it’s allowed.

If you can come up with some concrete uses for how to write back to the DB and they make sense to be put in the plugin, well, never say never…

redbot wrote:

So – amongst many other things – I will now be able to perform every sort of single and multiple search (using attribute “query”), even in a non-txp table put in txp database, and without writing a single line of PHP code. Terrific.

In theory, yes.

In practice, if there’s a core tag or a plugin specifically for the purpose you require, use it in preference to this. As with most of my plugins they should be behind a pane of glass and supplied with a little hammer that says “for use in case of emergency only”.

Last edited by Bloke (2008-05-22 22:23:54)

Textpattern CMS

Textpattern CMS support forum

#1 2008-05-22 12:47:20

smd_query: Talk to the database directly via SQL

#2 2008-05-22 12:56:03

Re: smd_query: Talk to the database directly via SQL

#3 2008-05-22 13:00:25

Re: smd_query: Talk to the database directly via SQL

#4 2008-05-22 13:03:45

Re: smd_query: Talk to the database directly via SQL

#5 2008-05-22 13:07:23

Re: smd_query: Talk to the database directly via SQL

#6 2008-05-22 13:26:38

Re: smd_query: Talk to the database directly via SQL

#7 2008-05-22 13:48:11

Re: smd_query: Talk to the database directly via SQL

#8 2008-05-22 14:59:15

Re: smd_query: Talk to the database directly via SQL

#9 2008-05-22 15:23:11

Re: smd_query: Talk to the database directly via SQL

#10 2008-05-22 15:35:11

Re: smd_query: Talk to the database directly via SQL

#11 2008-05-22 16:57:25

Re: smd_query: Talk to the database directly via SQL

#12 2008-05-22 17:25:19

Re: smd_query: Talk to the database directly via SQL

#13 2008-05-22 17:48:30

Re: smd_query: Talk to the database directly via SQL

#14 2008-05-22 18:29:52

Re: smd_query: Talk to the database directly via SQL

#15 2008-05-22 19:12:57

Re: smd_query: Talk to the database directly via SQL

Board footer