smd_query: Talk to the database directly via SQL

Bloke · 2009-10-26 08:52:06

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.

pieman · 2009-11-05 16:06:46

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'
                  >
                  ...

whaleen · 2009-11-13 01:48:45

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)

whaleen · 2009-11-13 02:18:04

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.

pieman · 2009-11-13 11:59:20

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!

whaleen · 2009-11-21 04:10:00

Bloke wrote:

<txp:variable /> support so you can read values from those lovely things directly into a query instead of having to embed them tag-in-tag stylie and get hung up on quoting issues

And the following just plain isn’t working for me:

<txp:smd_query
column="custom_13, custom_23"
table="textpattern"
where="custom_13='<txp:variable name="thingy" />' AND custom_23='something'">

Showing all items with {custom_13} and {custom_23}

</txp:smd_query>

Are variables meant to be called in another fashion when in the smd_query context?

Bloke · 2009-11-21 08:29:55

whaleen wrote:

Are variables meant to be called in another fashion when in the smd_query context?

Yes, if tags-in-tags doesn’t work, try the ? shortcut:

<txp:smd_query
column="custom_13, custom_23"
table="textpattern"
where="custom_13='?thingy' AND custom_23='something'">

Showing all items with {custom_13} and {custom_23}

</txp:smd_query>

whaleen · 2009-11-25 02:49:31

Bloke wrote:

Yes, if tags-in-tags doesn’t work, try the ? shortcut:

This smd_query will still not use that variable called thingy when trying the ? shortcut. Notice I’m running <txp:variable name="thingy" /> above and below the query in order to check that the variable exists. It does.

<txp:variable name="thingy" />

<txp:smd_query
column="custom_13, custom_23"
table="textpattern"
where="custom_13='?thingy' AND custom_23='something'">

Showing all items with {custom_13} and {custom_23}

</txp:smd_query>

<txp:variable name="thingy" />

Variations not working:

custom_13='?thingy'
custom_13='<txp:variable name="thingy" />'
custom_13='<txp:smd_var_get name="<txp:ign_user_info type="name" />-thingy" />' (username-variable versions are kept server side)

Two Ways I make Vars

1. The way in which the thingy variable is created is by using the permanent stored variable from the server:

<txp:variable name="thingy" value='<txp:smd_var_get name="<txp:ign_user_info type="name" />-thingy" />' />

2. The one instance where this: custom_13='?thingy' or custom_13='<txp:variable name="thingy" />' will work is when I create the variable in this way:

if($_POST['t']){$GLOBALS['variable']['thingy']=implode(',',$_POST['t']);
}

Do you know why these different variables can only be used in certain contexts? When I say different I mean that there are three in my mind. 1) created by smd_vars 2) created by POST and 3) created by txp:variable.

uli · 2009-11-25 10:23:30

whaleen wrote:

Two Ways I make Vars […]

<txp:variable name="thingy" value='<txp:smd_var_get name="<txp:ign_user_info type="name" />-thingy" />' />

- – - – - – - – - – - – - –

Just to have a stab until experts show (me) up: Have you tried:

<txp:variable name="thingy" value='<txp:smd_var_get name=''<txp:ign_user_info type="name" />-thingy'' />' />

(Exchanged the double apostrophes around the ign tag for two times two single apostrophes.)

whaleen · 2009-11-28 13:39:36

Thanks Uli for your suggestion. Double-single quotes around the ign tag do make it possible for me to then make ?thingy work in smd_queries. It’s strange to me that when I was using single quotes around that tag, that <txp:variable name="thingy" /> would print the value of that var onto that page but that it wouldn’t by using ?thingy in smd_query as mentioned. To quote your two examples and to explain better what is happening now:

Creating a variable this way:

Method 1. <txp:variable name="thingy" value='<txp:smd_var_get name="<txp:ign_user_info type="name" />-thingy" />' />

lets me use <txp:variable name="thingy" /> but not ?thingy

Method 2. <txp:variable name="thingy" value='<txp:smd_var_get name=''<txp:ign_user_info type="name" />-thingy'' />' />

lets me use either. I have not tested so see that the txp:if_variable tag can now use <txp:variable name="thingy" /> but I assume it will work now too. As I mentioned previously method 1 would not make the variable available for use with the if_variable tag.

My conclusion is that while method 1 does create the variable it is a weaker half-dead sorry little variable while method 2 creates a higher quality full-of-life more flexible variable. :) I’m satisfied with that explanation as silly as it is but wonder what makes it seem this way. Maybe there are varied outcomes with variable creation with txp:variable. Something for the devs to look at? Maybe just a wormhole.

Bloke · 2009-11-28 15:55:13

whaleen

OT Texplanation:

Method 1 fails because any tags in double quotes are not parsed; stuff in single quotes is. Since you are already using the single quotes to nest smd_var, when you nest another tag you need to escape the first single quote and use a second single quote to start the new tag. Hence the reason your/Uli’s Method 2 works.

In nested cases like these, to save your sanity, it’s usually easier to use txp:variable as a container to assign the value:

<txp:variable name="thingy"><txp:smd_var_get name='<txp:ign_user_info type="name" />-thingy' /></txp:variable>

One caveat:

<txp:variable name="higgs"><txp:category name="boson" /></txp:variable>

is not the same as:

<txp:variable name="higgs">
<txp:category name="boson" />
</txp:variable>

especially when it comes to testing if something is ‘empty’ using txp:if_variable or smd_if. The second one has newlines (aka spaces) in the value, which are treated as ‘has some content, albeit blank’.

whaleen · 2009-11-28 16:21:23

Thanks Stef. The container method makes it much easier to scan over and understand when writing. Too bad about the newlines’ whitespace as they make it much more easy to read but those are present a lot in my code so I’m glad to know it can be a suspect.

I’m still curious as to why I was still able to print a value (w/ txp:variable) in a limited scope using method 1.

Bloke · 2009-12-02 19:46:39

Next major release is available. v0.3 features:

A few minor bug fixes (thanks speeke)
strictfields attribute for allowing or disallowing spaces in ? vars
Direct pagination support including a special pageform attribute for displaying paging stats (limit, offset and pgonly now supported)
Host of new replacement vars, including page number, row number (on this page and from the start of the result set), total number of pages in result set, and start/end record numbers on each page
<txp:newer /> and <txp:older /> will step through the result pages. See Example 10 in the help for a pagination use case

Hopefully this will help those of you who have been forced to use embedded <txp:article> tags to simulate paging. It also means that custom queries can be paged, which is good news since <txp:article_custom> can’t be paginated; this opens up some rather neat opportunities.

Note that it’s not “true” paging in the sense that it doesn’t work like a conventional ‘cursor’ view of a subset of the returned records. Each time you visit the page, the whole query is executed but only the relevant portion of the results are shown depending on the page number. The good news is that you can specify a hard MySQL LIMIT and still page through the results by setting the smd_query limit attribute to a smaller value.

Anyway, see how you get on with it. As usual, I’m here for bug reports or support requests. If you have any cool queries that’ll help out others, please also post them here.

Last edited by Bloke (2009-12-02 19:54:00)

kostas45 · 2009-12-04 10:11:50

Bloke wrote:

<txp:newer /> and <txp:older /> will step through the result pages. See Example 10 in the help for a pagination use case

These tags seem not to work for me in Example 10.
They just print nothing. :-(

-Kostas

Bloke · 2009-12-04 10:14:58

kostas45 wrote:

[older/newer] seem not to work for me in Example 10.

What’s the rest of your smd_query/form? Is it the same as example 10? If so, do you have more than 10 users in your TXP system?

Textpattern CMS

Textpattern CMS support forum

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

#141 2009-11-21 04:10:00

Re: smd_query: Talk to the database directly via SQL

#142 2009-11-21 08:29:55

Re: smd_query: Talk to the database directly via SQL

#143 2009-11-25 02:49:31

Re: smd_query: Talk to the database directly via SQL

#144 2009-11-25 10:23:30

Re: smd_query: Talk to the database directly via SQL

#145 2009-11-28 13:39:36

Re: smd_query: Talk to the database directly via SQL

#146 2009-11-28 15:55:13

Re: smd_query: Talk to the database directly via SQL

#147 2009-11-28 16:21:23

Re: smd_query: Talk to the database directly via SQL

#148 2009-12-02 19:46:39

Re: smd_query: Talk to the database directly via SQL

#149 2009-12-04 10:11:50

Re: smd_query: Talk to the database directly via SQL

#150 2009-12-04 10:14:58

Re: smd_query: Talk to the database directly via SQL

Board footer