Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

danwoodward
Member
From: Brooklyn NY
Registered: 2006-08-06
Posts: 51
Website

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 10,285
Website GitHub

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

jstubbs
Moderator
From: Hong Kong
Registered: 2004-12-13
Posts: 2,395
Website

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 10,285
Website GitHub

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

jstubbs
Moderator
From: Hong Kong
Registered: 2004-12-13
Posts: 2,395
Website

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,238

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 10,285
Website GitHub

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,238

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

dl33
Member
Registered: 2006-06-17
Posts: 192
Website

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 10,285
Website GitHub

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.

Download smd_query v0.22


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

pieman
Member
From: Bristol, UK
Registered: 2005-09-22
Posts: 491
Website

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

Board footer

Powered by FluxBB