Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
#121 2009-06-25 05:53:38
Re: smd_query: Talk to the database directly via SQL
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">
Offline
#122 2009-06-25 06:12:14
Re: smd_query: Talk to the database directly via SQL
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)
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
Offline
#123 2009-06-25 13:42:49
- kostas45
- Member
- From: Greece
- Registered: 2007-11-08
- Posts: 61
Re: smd_query: Talk to the database directly via SQL
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)
Offline
#124 2009-09-05 20:33:39
Re: smd_query: Talk to the database directly via SQL
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…
Offline
#125 2009-09-06 06:32:03
Re: smd_query: Talk to the database directly via SQL
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!
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
Offline
#126 2009-09-06 06:55:21
Re: smd_query: Talk to the database directly via SQL
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.
Offline
#127 2009-09-06 22:51:40
- uli
- Moderator
- From: Cologne
- Registered: 2006-08-15
- Posts: 4,310
Re: smd_query: Talk to the database directly via SQL
Jonathan, there’s jmd_count:
<txp:jmd_count table=“table_name” where=“where_clause”/>
In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links
Offline
#128 2009-09-07 00:11:24
Re: smd_query: Talk to the database directly via SQL
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…
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
Offline
#129 2009-09-07 00:35:28
- uli
- Moderator
- From: Cologne
- Registered: 2006-08-15
- Posts: 4,310
Re: smd_query: Talk to the database directly via SQL
Oh damn! What a sloppy reader I am :p
In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links
Offline
#130 2009-09-29 17:02:54
Re: smd_query: Talk to the database directly via SQL
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)
Offline
#131 2009-10-16 14:36:19
Re: smd_query: Talk to the database directly via SQL
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.
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
Offline
#132 2009-10-22 11:43:40
Re: smd_query: Talk to the database directly via SQL
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
Offline