Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

els
Moderator
From: The Netherlands
Registered: 2004-06-06
Posts: 7,458

Re: smd_query: Talk to the database directly via SQL

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

Offline

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

uli
Moderator
From: Cologne
Registered: 2006-08-15
Posts: 4,310

Re: smd_query: Talk to the database directly via SQL

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)


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

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,468
Website GitHub

Re: smd_query: Talk to the database directly via SQL

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)


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

Online

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

wet
Developer Emeritus
From: Schoerfling, Austria
Registered: 2005-06-06
Posts: 3,330
Website Mastodon

Re: smd_query: Talk to the database directly via SQL

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.

Offline

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

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

Re: smd_query: Talk to the database directly via SQL

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!

Offline

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

mrdale
Member
From: Walla Walla
Registered: 2004-11-19
Posts: 2,215
Website

Re: smd_query: Talk to the database directly via SQL

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)

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,468
Website GitHub

Re: smd_query: Talk to the database directly via SQL

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.


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

Online

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

keith
Member
From: Blyth, Northumberland, England
Registered: 2004-12-08
Posts: 199
Website

Re: smd_query: Talk to the database directly via SQL

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


Keith
Blyth, Northumberland, England
Capture The Moment

Offline

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

jm
Plugin Author
From: Missoula, MT
Registered: 2005-11-27
Posts: 1,746
Website

Re: smd_query: Talk to the database directly via SQL

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

Offline

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

ruud
Developer Emeritus
From: a galaxy far far away
Registered: 2006-06-04
Posts: 5,068
Website

Re: smd_query: Talk to the database directly via SQL

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

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,468
Website GitHub

Re: smd_query: Talk to the database directly via SQL

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.


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

Online

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,468
Website GitHub

Re: smd_query: Talk to the database directly via SQL

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 &lt; and &gt; 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)


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

Online

Board footer

Powered by FluxBB