Textpattern Forum

You are not logged in. Register | Login | Help

#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: 4,504
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: 5,866
Website

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.

Txp Builders – finely-crafted code, design and Txp

Offline

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

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

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.

Txp Builders – finely-crafted code, design and Txp

Offline

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

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

Re: smd_query: Talk to the database directly via SQL

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.


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

Txp Builders – finely-crafted code, design and Txp

Offline

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

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

Re: smd_query: Talk to the database directly via SQL

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>

Offline

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

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

Re: smd_query: Talk to the database directly via SQL

jm wrote:

Top commenters

Niiiiiiiiiiice!


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

Txp Builders – finely-crafted code, design and Txp

Offline

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

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

Re: smd_query: Talk to the database directly via SQL

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)

Offline

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

merz1
Member
From: Hamburg
Registered: 2006-05-04
Posts: 994
Website

Re: smd_query: Talk to the database directly via SQL

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


Get all online mentions of Textpattern via OPML subscription: TXP Info Sources: Textpattern RSS feeds as dynamic OPML

Offline

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

Sheru
Member
From: Kathmandu, Nepal
Registered: 2007-05-09
Posts: 96

Re: smd_query: Talk to the database directly via SQL

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)

Offline

Board footer

Powered by FluxBB