smd_query: Talk to the database directly via SQL

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)

Bloke · 2008-05-23 22:52:28

Wheeeeeeeeeeeee. Had to continue my little monologue and post this example. I’ve just tried a slight variation of the plugin help’s Example 5 out in the files form.

Say you have downloads for artists on your record label. You list the latest download for each artist on a page (the files could be local or remote). What if you’d like to offer some further reading links to your articles about each band in case visitors land on the downloads page and like what they hear? No problem. Make your files form read:

<txp:text item="file" />: 
<txp:file_download_link>
<txp:file_download_name /> [<txp:file_download_size format="auto" decimals="2" />]
</txp:file_download_link>
<br />
<txp:text item="category" />: <txp:file_download_category /><br />
<txp:text item="download" />: <txp:file_download_downloads />
<br />
<txp:text item="Related articles" />:
<txp:smd_query query="SELECT DISTINCT
     txp.id, txp.title FROM textpattern AS txp
     WHERE (txp.keywords LIKE '%,?category%,'
     OR txp.keywords LIKE '%?category%,'
     OR txp.keywords LIKE '%,?category%'
     OR txp.category1 = '?category'
     OR txp.category2 = '?category')
     GROUP BY txp.title LIMIT 4"
     wraptag="ul" break="li">
  <txp:permlink id="{id}">{title}</txp:permlink>
</txp:smd_query>

Looks horrific but it’s not that bad. All it’s saying is:

for each file in your <txp:file_download_list />, grab its category (the band name in this case)
look at all the articles in the database and see if any of them have a matching category1 OR category2 OR have keywords that list the band’s name
if so, add the article to a list of other permlinked articles under the download details (up to a maximum of 4)

[ Extension homework: order the articles by posting date so it always shows the 4 most recent articles about that band ]

Sorry, couldn’t resist. I’ll get back in my box now.

jm · 2008-05-23 22:53:18

D’oh! I had a trailing slash. Containers work great. Here’s my little example:

Top commenters:

<txp:smd_query break="li" query="select count(*) as total,
    name from txp_discuss
    group by name
    order by total desc
    limit 10" wraptag="ol">{name}: {total}</txp:smd_query>

Bloke · 2008-05-23 22:55:22

jm wrote:

Top commenters

Niiiiiiiiiiice!

jm · 2008-05-23 23:23:57

I couldn’t help myself – I’ve got another one, though it’s only useful for jmd_dashboard.

Unmoderated comments (with edit link):

<h2>Unmoderated comments</h2>
<txp:smd_query break="li" query="SELECT discussid, name, parentid, 
    unix_timestamp(posted) as Posted
    FROM txp_discuss 
    WHERE visible = 0 
    ORDER BY posted DESC" wraptag="ul">
    <txp:jmd_dashboard_edit id="{discussid}" type="comment">
        {name} about <txp:php>echo since({Posted});</txp:php>
    </txp:jmd_dashboard_edit>
    on <txp:article_custom id="{parentid}">
        <txp:permlink><txp:title/></txp:permlink>
    </txp:article_custom>
</txp:smd_query>

Edit: Added article link

Last edited by jm (2008-05-23 23:26:52)

merz1 · 2008-05-24 23:16:59

Ouch. What’s going on here? This thread/plugin should not be available for the ~~public~~ newcomers :)

Very geeky little Pandora box. CMS abstraction layer made obsolete. The one tag CMS :)

What about the latest (search) referrers for an article, a site… ARGHHH…

Sheru · 2008-06-09 10:02:17

Greetings! Dear Stef, Thank you for great plugin smd_slimbox, I am enjoying with this plugin. Wonderful work…
I am even now trying another smd_query plugin to read files..

I have differnt categories files such as
ATC-Codes |Other Documentation |Example data |Macintosh |Windows. All data is read from txp_files.

I now wish to add a search box above this category to search the files from category or other fields.

Problem:
Once I click on Search button, how should I track where to take those.. No idea.
I am trying much to make it work but achieved no success..

Could please help me to make it work.
Thank you a lot for your kind help.
Sheru

Last edited by Sheru (2008-06-09 10:06:21)

Textpattern CMS

Textpattern CMS support forum

#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

#25 2008-05-23 22:52:28

Re: smd_query: Talk to the database directly via SQL

#26 2008-05-23 22:53:18

Re: smd_query: Talk to the database directly via SQL

#27 2008-05-23 22:55:22

Re: smd_query: Talk to the database directly via SQL

#28 2008-05-23 23:23:57

Re: smd_query: Talk to the database directly via SQL

#29 2008-05-24 23:16:59

Re: smd_query: Talk to the database directly via SQL

#30 2008-06-09 10:02:17

Re: smd_query: Talk to the database directly via SQL

Board footer