Textpattern Forum

You are not logged in. Register | Login | Help

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 5,980
Website

smd_query: Talk to the database directly via SQL

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.

Last edited by Bloke (2010-08-31 21:01:05)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern.

Txp Builders – finely-crafted code, design and Txp

Offline

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

redbot
Plugin Author
Registered: 2006-02-14
Posts: 1,399

Re: smd_query: Talk to the database directly via SQL

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

Offline

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

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 4,989
Website

Re: smd_query: Talk to the database directly via SQL

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


Yiannis
——————————
neme.org | neme-imca.org | hblack.net | LABS

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 5,980
Website

Re: smd_query: Talk to the database directly via SQL

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)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern.

Txp Builders – finely-crafted code, design and Txp

Offline

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

uli
Moderator
From: Cologne/Köln
Registered: 2006-08-15
Posts: 3,119

Re: smd_query: Talk to the database directly via SQL

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


In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 5,980
Website

Re: smd_query: Talk to the database directly via SQL

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*


The smd plugin menagerie — for when you need one more gribble of power from Textpattern.

Txp Builders – finely-crafted code, design and Txp

Offline

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

uli
Moderator
From: Cologne/Köln
Registered: 2006-08-15
Posts: 3,119

Re: smd_query: Talk to the database directly via SQL

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? ;)


In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 5,980
Website

Re: smd_query: Talk to the database directly via SQL

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


The smd plugin menagerie — for when you need one more gribble of power from Textpattern.

Txp Builders – finely-crafted code, design and Txp

Offline

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

uli
Moderator
From: Cologne/Köln
Registered: 2006-08-15
Posts: 3,119

Re: smd_query: Talk to the database directly via SQL

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?


In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 5,980
Website

Re: smd_query: Talk to the database directly via SQL

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 :-)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern.

Txp Builders – finely-crafted code, design and Txp

Offline

Board footer

Powered by FluxBB