smd_query: Talk to the database directly via SQL

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)

wet · 2008-05-22 19:19:47

Bloke, you should build more plugins. Lots of them. Not only for the code, but I’d miss your “Intended Use” jokes if you didn’t.

redbot · 2008-05-23 01:06:54

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.

Oops sorry,
I was so enthusiastic I spoke without thinking well first.
Anyway your plugin is priceless, it will save me a lot of time and headaches, thank you!

mrdale · 2008-05-23 15:34:00

Actually, it would be cool if people (smarter than me) posted the queries they use here. I’m excited by this plugin but it’s kinda like giving a band saw to 10-year-old… unattended use may cause loss of fingers.

I wanna see what smart people do with this one.

Last edited by mrdale (2008-05-23 15:34:26)

Bloke · 2008-05-23 15:49:40

mrdale wrote:

I wanna see what smart people do with this one.

Heh, counts me out then. I just make the tools that allow other people to cut their own limbs off :-p

I’ll try and come up with some useful snit-bits as I get time over the next few days, but in the meantime if anyone would like to post some cool queries here that do ultimately useful stuff, then that’d be awesome.

keith · 2008-05-23 15:52:02

I’m sitting here, grinning inanely and whispering to myself clever, clever, clever sod!!

jm · 2008-05-23 17:17:08

mrdale wrote:

Actually, it would be cool if people (smarter than me) posted the queries they use here.

I think we should ~~abuse~~ use the TextBook’s community pages. Sound good?

Nice job on this Stef. When 4.0.7 rolls around, are you gonna add container functionality (forms are so hard to create)?

ruud · 2008-05-23 17:49:18

Perhaps a better solution is to put them in the plugin help as examples…

Bloke · 2008-05-23 18:54:46

jm wrote:

When 4.0.7 rolls around, are you gonna add container functionality (forms are so hard to create)?

Heh, it already has container ability I just neglected to mention it in the plugin help, oops. Good catch, thanks. You can do:

<txp:smd_query column="*" table="txp_file"
     where="(category='?category1' OR category='?category2')
     AND status=4 ORDER BY downloads desc LIMIT 10"
     wraptag="table" label="Most popular downloads" labeltag="h3">
  <tr>
  <td><txp:file_download_link id="{id}">{filename}</txp:file_download_link></td>
  <td>{description}</td>
  <td>downloads: {downloads}</td>
  </tr>
</txp:smd_query>

To tabulate the top 10 downloads (status=live) that have a category matching either of the current article’s categories, with most popular listed first.

I have a couple of minor edits to make (one stolen shamelessly from the 4.0.6 compatibility mod you made to jmd_count) so when I update the plugin I’ll add that example to the help.

ruud wrote:

Perhaps a better solution is to put them in the plugin help as examples…

That was my intention, to collate some good ones from real use cases and roll them into the plugin help as updates come around. I thought 3 examples would be enough to get going, but perhaps I was a little bit mean and should have thought up more before releasing it.

Bloke · 2008-05-23 22:16:25

Further to my last post, v0.11 is out [ compressed plugin ]

New in this release:

Supports files and links in even more surreal combinations so you can now embed smd_query inside the files or plainlinks forms and perform a query on each matching file (see Example 5 in the help)
Maths in queries using < and > comparison operators for TXP 4.0.6 and below (thanks jm). To do this successfully in TXP 4.0.6 you must use the entity names < and > in the query (see Example 6)
Three more examples for those of you craving ideas, and better help (thanks again jm)
Bug fix: if you miss out the where attribute it now defaults to “the whole table” (thanks for the prod, jm)

As ever, feedback is welcomed in all its useful forms. Get querying…

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

Textpattern CMS

Textpattern CMS support forum

#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

#16 2008-05-22 19:19:47

Re: smd_query: Talk to the database directly via SQL

#17 2008-05-23 01:06:54

Re: smd_query: Talk to the database directly via SQL

#18 2008-05-23 15:34:00

Re: smd_query: Talk to the database directly via SQL

#19 2008-05-23 15:49:40

Re: smd_query: Talk to the database directly via SQL

#20 2008-05-23 15:52:02

Re: smd_query: Talk to the database directly via SQL

#21 2008-05-23 17:17:08

Re: smd_query: Talk to the database directly via SQL

#22 2008-05-23 17:49:18

Re: smd_query: Talk to the database directly via SQL

#23 2008-05-23 18:54:46

Re: smd_query: Talk to the database directly via SQL

#24 2008-05-23 22:16:25

Re: smd_query: Talk to the database directly via SQL

Board footer