Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 8,621
Website

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

#133 2009-10-22 12:21:26

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 8,621
Website

Re: smd_query: Talk to the database directly via SQL

pieman

Not sure it’s directly related to smd_query here because you get the same result if you remove the query and hard-code an article_custom tag. And you’re not using any replacement tags for the extra CFs.

Have you tried using Gerhard’s built-in custom field function:

<li><txp:glz_custom_field name="country" /></li>

?

Last edited by Bloke (2009-10-22 12:23: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

#134 2009-10-22 19:35:15

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

Re: smd_query: Talk to the database directly via SQL

Bloke wrote:

Not sure it’s directly related to smd_query here because you get the same result if you remove the query and hard-code an article_custom tag. And you’re not using any replacement tags for the extra CFs.
Have you tried using Gerhard’s built-in custom field function:
bc. <li><txp:glz_custom_field name=“country” /></li>

hmmm, I think maybe those tags have been deprecated – there’s no mention of them in the help of v1.2 or v1.2.3.

But you’re right, this isn’t an smd_query issue. Something a bit wonky going on elsewhere. Thanks anyway.

Offline

#135 2009-10-25 20:06:28

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

Re: smd_query: Talk to the database directly via SQL

Back again :)

Is it possible with smd_query to limit the results to show future articles, as you’d do with time="future" on a normal article_custom call?

Offline

#136 2009-10-26 08:52:06

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 8,621
Website

Re: smd_query: Talk to the database directly via SQL

pieman wrote:

Is it possible with smd_query to limit the results to show future articles

You should be able to add AND Posted > NOW() to your query in order to do that.


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

#137 2009-11-05 16:06:46

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

Re: smd_query: Talk to the database directly via SQL

Bloke wrote:

You should be able to add AND Posted > NOW() to your query in order to do that.

Thanks Stef. After a brief delay I finally got around to trying that, but it seems to have no effect.

This is what I’ve got

                  <txp:smd_query column='*' table='textpattern'
                    where='custom_1 LIKE "%<txp:article_id />%"
                    AND section="events" AND Posted > NOW() ORDER BY title desc LIMIT 1'
                  >
                  ...

Offline

#138 2009-11-13 01:48:45

whaleen
Member
From: Portland
Registered: 2006-05-11
Posts: 372
Website

Re: smd_query: Talk to the database directly via SQL

How can one return articles which can make use of all article tags and plugin tags that affect articles?

I’m realizing that I have to query each column that my form needs to show what it contains.

If in my page:

<txp:smd_query 
    column="id, section, custom_13, custom_12, custom_15, etc"
    table="textpattern"
    where="section='section1' AND custom_13='some-custom-stuff'"
    form="smd_query_crud" 
/>

And in smd_query_crud form:

This: {custom_13}
That: {custom_15}
ID: {id}
Other: {custom_12}
Section: {section}

I get:

This: Things
That: Thing
ID: #
Other: Thing
Section: some-section

I’m not able to place a <txp:modified /> tag in my form and get results for example. I wonder how to force the results to act like TXP Articles do natively when returned. Does this make sense? I want to include other aspects of the articles returned such as rss_unlimited_categories and potentially other stuff.

Last edited by whaleen (2009-11-13 01:52:11)


txtstrap (Textpattern + Twitter Bootstrap + etc…)

Offline

#139 2009-11-13 02:18:04

whaleen
Member
From: Portland
Registered: 2006-05-11
Posts: 372
Website

Re: smd_query: Talk to the database directly via SQL

I might do:

<txp:article_custom id='<txp:output_form name="smd_query_crud" />' form="another_form" />

with smd_query_crud made up of:

{id},

That might give me the ability to use another_form to call on tags like txp:modified and others.

I’m still F@#$!ed trying to sort by rss_unlimited_categories though, which is the real beef in my salad.


txtstrap (Textpattern + Twitter Bootstrap + etc…)

Offline

#140 2009-11-13 11:59:20

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

Re: smd_query: Talk to the database directly via SQL

Bloke wrote:
You should be able to add AND Posted > NOW() to your query in order to do that.

For future reference, it needed this and time="all" on article_custom to work. Thanks Stef!

Offline

Board footer

Powered by FluxBB