smd_query: Talk to the database directly via SQL

danwoodward · 2009-06-25 05:53:38

Is there a way to paginate the results, I’m using smd_query to make a alphabetic navigation based on url variables. I’ve got ob1_pagination setup to keep the url parameters as they are set, but the pagination doesn’t get triggered with smd_query, there isn’t any record of page numbers i guess… any help would be much appreciated.

<txp:smd_query 
           column="Title, Image, url_title"
	   table="textpattern"
           where="section='songwriters' AND status='4' AND LEFT(Title, 1) > 'a' = 1 AND LEFT(Title, 1) < 'f' = 1 ORDER BY Title asc LIMIT 30">

Bloke · 2009-06-25 06:12:14

danwoodward wrote:

Is there a way to paginate the results

Not in the classic sense of ‘N per page, next/prev’, no because the plugin returns everything you ask for. But you should be able to take advantage of SQL’s LIMIT offset syntax and handle the paging yourself.

A couple of methods spring to mind (I haven’t ever tried these):

1) use LIMIT ?offset,30 to get a maximum 30 results, then use a conditional (smd_if?) to check {smd_thisrow} and if it goes over a certain number — say 10 — stop outputting. You can then create a fake ‘newer’ link that calls the same page but adds offset=10 to the URL. Your query will return the ‘next 10’, and so on. With a bit of maths you can create the illusion of pages from these fake newer/older links. Tip: use defaults="offset: 0" to make sure the plugin always sees an offset value, even if it’s not in the URL.

2) Get all the records as normal and use jQuery to page through the result set N records at a time. Again, {smd_thisrow} should help here.

If you get anywhere with either of these, please post your code here because it would be insanely useful, thanks. And of course if you get stuck, post again and I’ll see if I can help a bit further.

Last edited by Bloke (2009-06-25 06:14:57)

kostas45 · 2009-06-25 13:42:49

This is my current (tested) solution to the smd_query pagination problem:

It uses rvm_counter, smd_if and smd_pagicle.
The idea is to use smd_query output to increment a counter up to the number of records we want (20 in the example below), then output the text that smd_pagicle needs to splitat.

<txp:variable name="your_counter" value='<txp:rvm_counter reset="0" />' />
...
<txp:smd_query query="..." ... class="search-list" wraptag="ul" break="li">
   ...Do something to output record...
   <txp:variable name="your_counter" value='<txp:rvm_counter />' />
   <txp:variable name="total_records" value="{smd_rows}" />
   <txp:smd_if field="txpvar:your_counter" operator="eq" value="20">
      <span style="display:none">Page-Here<txp:rvm_counter reset="0" /></span>
   </txp:smd_if>
<txp:else />
   <p>No output</p>
</txp:smd_query>
...
<txp:smd_if field="txpvar:total_records" operator="not" value="20">
   <div id="smd_pagnav"></div>
   <txp:smd_pagicle container=".search-list" splitat="Page-Here0" splitwraptag="" />
</txp:smd_if>

EDIT: I added code to account for the case the output records are 20. In this case one more page is created, which is empty. This is expected as it counts the remaining closing li and ul tags. Added code (total_records var declaration and check at the end) prevents this :-)

Cheers,
Kostas

Last edited by kostas45 (2009-06-25 15:16:04)

jstubbs · 2009-09-05 20:33:39

Sorry to ask this if it has already been asked (wish we had a search in thread feature) – is it possible to choose a different DB/Table from the TXP one? I can get around this with a quick include PHP file but thought I’d ask anyway…

Bloke · 2009-09-06 06:32:03

jstubbs wrote:

is it possible to choose a different DB/Table from the TXP one?

Nope, sorry. Only tables in the TXP DB are catered for because the additional overhead of authentication to an alternative resource did my head in!

jstubbs · 2009-09-06 06:55:21

That’s fine – its easier to just use a small script anyway. Its to count the number of rows in a table and output the result – no biggie. Thanks.

uli · 2009-09-06 22:51:40

Jonathan, there’s jmd_count:
<txp:jmd_count table=“table_name” where=“where_clause”/>

Bloke · 2009-09-07 00:11:24

uli wrote:

there’s jmd_count

True, but that still only operates on the TXP database, right? Not an external DB with different MySQL credentials like Jonathan is using? Unless I need to re-read jm’s docs…

uli · 2009-09-07 00:35:28

Oh damn! What a sloppy reader I am :p

dl33 · 2009-09-29 17:02:54

SELECT * FROM iuc_textpattern WHERE section='kaeufer-sucht-verkaeufer'
AND custom_3 IN ('?objectart')
AND custom_5 >= '?min_wohnflaeche' AND custom_5 <= '?max_wohnflaeche'

This works very well, with the single problem that the txp variables ?min_wohnflaeche and ?max_wohnflaeche are interpreted as strings, while they should be integers.
(0 and 15, would show all entries starting with either a 0 or a 1, while 90 and 120 don’t show anything as there is no number bigger than 90 but smaller than 12.)

Is there any way of forcing them to be integers?

HA! Using phpmyadmin to change the varchar custom field into an int one solves the problem…

Last edited by dl33 (2009-09-29 18:01:43)

Bloke · 2009-10-16 14:36:19

Here’s an update for the write-once-do-many among you. Two new attributes:

escape :allows you to HTML encode the output of any column in the database. Simply supply a comma-separated list of column names. Very useful if you’re passing replacement variables as input to other tags (thanks jakob)
preparse : switch the rendering order of the plugin’s output so it parses the form/container first and then looks for replacement {...} tags

preparse might not sound like much but it’s insanely useful with the new <txp:yield /> tag because it allows you to write one query in a Form and then reuse it in multiple places about your site. Each time you call it you can format the results differently by supplying container content to the <txp:output_form> tag. Pseudo-Parameterized-Queries-R-Us.

Take a look at Example 9 in the help for a trivial use case. No doubt you can do better.

Download smd_query v0.22

pieman · 2009-10-22 11:43:40

Stef, as far as I can tell, smd_query doesn’t recognise custom fields > 10.

I’m using to glz_custom_fields to create 14 of the blighters, but I can only access the first 10.

Rather than posting all 14, here is a trimmed example.

‘post code’ aka custom_10 (or any previous cfs) returns the correct value
‘country’ aka custom_11 (or any subsequent cfs) returns nothing

                   <txp:smd_query column='*' table='textpattern'
                      where='custom_1 LIKE "%<txp:article_id />%"
                      AND section="who-are-we" ORDER BY title desc LIMIT 10'
                    >
                    <txp:article_custom id="{ID}">
                    <li><txp:custom_field name="post-code" /></li>
                    <li><txp:custom_field name="country" /></li>
                    </txp:article_custom>
                    </txp:smd_query>

I searched the smd_query code for a hard limit of 10, but I don’t think there is one. Is it possible to make this magic happen?
I’m using v0.21 on 4.2.0

Textpattern CMS

Textpattern CMS support forum

#121 2009-06-25 05:53:38

Re: smd_query: Talk to the database directly via SQL

#122 2009-06-25 06:12:14

Re: smd_query: Talk to the database directly via SQL

#123 2009-06-25 13:42:49

Re: smd_query: Talk to the database directly via SQL

#124 2009-09-05 20:33:39

Re: smd_query: Talk to the database directly via SQL

#125 2009-09-06 06:32:03

Re: smd_query: Talk to the database directly via SQL

#126 2009-09-06 06:55:21

Re: smd_query: Talk to the database directly via SQL

#127 2009-09-06 22:51:40

Re: smd_query: Talk to the database directly via SQL

#128 2009-09-07 00:11:24

Re: smd_query: Talk to the database directly via SQL

#129 2009-09-07 00:35:28

Re: smd_query: Talk to the database directly via SQL

#130 2009-09-29 17:02:54

Re: smd_query: Talk to the database directly via SQL

#131 2009-10-16 14:36:19

Re: smd_query: Talk to the database directly via SQL

#132 2009-10-22 11:43:40

Re: smd_query: Talk to the database directly via SQL

Board footer