smd_query: Talk to the database directly via SQL

Bloke · 2009-12-05 11:58:33

kostas45 wrote:

I also need my url (get) vars on all pages together with the pg var. Should next/prev links take account of that?

No. They can’t because txp:newer and txp:older don’t take them into account. smd_query doesn’t alter the tags’ functionality. They output a link to the current section’s list page and add ?pg=pagenum to the URL. Nothing more.

Example 11: Next/prev link targets are somehow wrong.

What do you mean “somehow wrong”? Look at the pageform in the example and see what it is doing, then alter it to suit your application. The example constructs links to the current article (using txp:permlink) so if you are using smd_query in an article it will page through the results and retain the current article in view. If you want to use it while in a list page you will need to change the anchor destinations accordingly.

Regarding the variables, the same applies. If you copied the example verbatim then it only adds the smd_qpg var to the links. If you want to tack on your existing URL params you will have to grab them from the URL and construct your links to include &param=val&some=var&.... You might like to consider adi_gps here, or use smd_query itself to grab (and optionally filter) the URL params then add them to your custom pagination links using the plugin’s ?varname notation to insert the variable names where you want them.

kostas45 · 2009-12-05 12:16:08

You are right! Sorry about that, I should have been more careful.
Custom pagination is definitely more useful :-)
Thanks for your help and work.

-Kostas

Bloke · 2009-12-07 09:39:04

speeke wrote:

I have begun looking at the new version, and am a little confused by all the extra gribble ;)

Having read through your code, I conclude it’s my crap documentation, sorry. I’ll see if I can tidy it up to make it clearer.

In the meantime, <txp:smd_query_if_prev> (and next) can only be used inside a pageform. Anywhere else they’ll do absolutely nothing (as you found!)

On a side note, <txp:link_to_prev> and <txp:link_to_next> shouldn’t need all the smd_if clutter, because they will automatically not render any container content if there is no next/prev article.

Also, can you please explain what pageform is? Is it a form of type article or misc ???

pageform is where you put a bunch of {} replacement tags (and/or navigation tags) to allow your visitors to page through your smd_query result sets. The replacement tags available are things that allow you to display stuff like “Showing records X to Y of Z” or “Previous M / Next N records”. And you can put TXP’s newer/older tags in here or construct your own, as shown in the plugin’s Example 11. You can place the contents of your pageform above or below the result set (or in both places!) using the pagepos attribute.

btw, it doesn’t matter what type you assign to your form. The types are just for your own organisational use and mean nothing above that.

Bloke · 2009-12-07 10:18:00

speeke wrote:

I don’t want an empty list if one or other are not true

Can you do something like this? (untested)

<txp:if_individual_article>
  <txp:smd_if field="prev_title, next_title" operator="isused, isused" logic="or">
    <h4>Adjacent Entries</h4>
    <ul>
      <txp:link_to_prev><li>Previous Entry</li></txp:link_to_prev>
      <txp:link_to_next><li>Next Entry</li></txp:link_to_next>
    </ul>
  </txp:smd_if>
</txp:if_individual_article>

That way if neither are present you see nothing, but if one or the other are present you enter the smd_if container whereby the link_to_prev/next containers will be displayed depending on which link(s) are available. And you will always get a valid XHTML list — even if it’s a list of one item.

EDIT: crap, no, you’re right. That’ll render the anchor around the <li>. You know what these two tags need? A wraptag attribute!

However, I thought some things had to go in an article type form for them to render

I don’t think so (someone correct me if I’m wrong). But if you want to preview the forms they have to be of type article.

Last edited by Bloke (2009-12-07 10:20:13)

Bloke · 2010-01-11 09:39:11

speeke wrote:

the logic fails

Hmmm, this is odd — and I know it isn’t much consolation — but your code seems to work for me. If I specify ?month=2008-06 where I know I have no articles, I see the ‘else’ message. Every other time I see an article and pagination links (incidentally, paging between articles is fun because the pagination form has to read the ?month and add it to the URL as well as the page variable!)

One thing you should probably alter is your txp:article; it should be <txp:article_custom /> because txp:article doesn’t take the id attribute since it’s context sensitive.

If that doesn’t help, I suggest adding debug="2" to the smd_query tag so we can see what the plugin sees on a page where there should be no articles. Let me know what you find, and I’ll help you dig further.

Happy New Year to you too. Hope it’s a good one.

Bloke · 2010-01-12 09:55:20

speeke wrote:

as soon as I place this in an article form, and call it using the <txp:article /> in post #167 above, the error here message fails.

Hmmm, clearly something’s amiss because it works as expected on my installation — even when the smd_query is put in the default form :-\

Back to first principles methinx:

Put your site in debugging mode
On your Page, make sure there’s a <txp:article /> tag that calls the default form
In your chosen Section that calls your Page, make sure there are some articles for one month and not for another
In your default article form, put:

<txp:smd_query query="SELECT ID FROM textpattern WHERE Section = '?s' AND Status = 4 AND (SUBSTR(Posted,1,10)='?month' OR SUBSTR(Posted,1,7)='?month' OR SUBSTR(Posted,1,4)='?month') GROUP BY Posted DESC" limit="1" debug="2">
   Yay! Articles :-)
<txp:else />
   Booo, no articles :-(
</txp:smd_query>

and see what happens with various ?month values. That works on my test site.

I wonder — just a stab in the dark — if the reason you’re not seeing anything at all is because you’re getting a freak circular reference. If you’re using an article form to get the page, and inside your smd_query you’re showing an article with the same form, you’ll get a warning and no output. Debugging mode should show you any oddness.

EDIT: Second thoughts, it can’t be that because otherwise it’d fail when it finds a valid article too… *ponder*

EDIT 2: Just out of curiosity, what is all the smd_ and txp:variable paraphernalia for in post #168?

<txp:smd_switch item="?s">
  <txp:smd_case value="section-name">
   ...
  </txp:smd_case>
</txp:smd_switch>

is the same as <txp:if_section name="section-name"> isn’t it?

And I assume you must have adi_gps or something on the page, otherwise the <txp:if_variable name="month" value=""> will always fail…?

Last edited by Bloke (2010-01-12 10:08:04)

Bloke · 2010-01-13 09:33:32

speeke wrote:

However this example doesn’t appear to be testing for the error message scenario that I’m having problems with (ie when a user enters a different date in the title bar url for which no articles exist).

Oh, perhaps I’ve misunderstood then. Sorry. For me, if I put site.com/section/article?month=2009-11 I see an article shown in the output of smd_query. But if I use site.com/section/article?month=2009-09 I see the ‘else’ message because I have no articles with Posted dates in that month. Same result if I mangle the date in any way; the error message appears. I wonder why it’s different on your setup? smd_query is supposed to understand else conditions correctly; you’re using plugin v0.4, right?

I’ve had to include limit="1" in the article form tag that is included on the page that calls all this stuff.

If you omit the limit, do you get any error messages in debugging/testing mode?

1… / 2… / 3…

OK, all makes sense.

when I call it from the article form <txp:article form="content-main_entries" limit="1" /> the error message part of the equation fails.

Totally odd, because there’s very little difference (logically) with the two setups.

Is there a problem with calling an article form from a page, and then having that article form call another article form?

Only if the two forms are the same (as far as I know). My understanding is that as long as the forms differ, TXP won’t complain.

in my page <txp:article form="content-main_entries" limit="1" /> calls the <txp:smd_switch item="?s"> code in the article form “content-main_entries”, and within this form the tag <txp:article_custom listform="article_list" id="{ID}" /> calls the article form “article_list”.

Aha! Hang on. article_custom has no listform attribute so it’ll look for the container. Since there’s no container, it’ll use the default form and is actually ignoring your article_list form. Bet that’s it.

The question is why, when you set your Admin->Prefs Production Status to Debugging or Testing, is your site not reporting these two failures:

incorrect use of a tag attribute for article_custom
the circular reference generated by the article tags

? Spooky.

Bloke · 2010-01-13 13:27:46

speeke wrote:

If that’s the case, then for some reason my set up is not producing the same results – using your “back to basics” code. Hmm.

Bizarre indeed. Trying to think what else could vary between setups that might affect this. Perhaps permlink mode? (doubt it, though)

I’m concerned that I’m now taking up your valuable time on something… unrelated to the workings of your wonderful plugin

Well, it may be the plugin interacting weirdly with your installation (perhaps even the PHP version?) so I’d ilke to nail it down in case it’s something that needs to be addressed. Plus I hate giving up on niggling things like these!

For the record, I have /section/title permlinks and in my page I have a straight <txp:article /> tag (no attributes) which calls my default form. After the <txp:body/> tag I have this code:

<txp:smd_query query="SELECT ID FROM textpattern WHERE Section = '?s' AND Status = 4 AND (SUBSTR(Posted,1,10)='?month' OR SUBSTR(Posted,1,7)='?month' OR SUBSTR(Posted,1,4)='?month') GROUP BY Posted DESC" limit="1" debug="1">
   <txp:article_custom form="article_list" id="{ID}" wraptag="ul" />
<txp:else />
   error here
</txp:smd_query>

and in form article_list I have <li><txp:title /></li>.

With this URL: http://site.com/section/article?month=2009-11 I see an article title. With this one: http://site.com/section/article?month=2009-09 I see error here and no article.

One other thing that might be worth checking is to verify your tags are all paired properly and there are no dangling <txp:else /> tags or unclosed tags or something like that — check your Page, default form and any called forms, including ones that have been brought in with <txp:output_form />. Sometimes a pesky misplaced ‘/’ in a tag or an unclosed quote can wreak havoc in else clauses further down the page and cause the behaviour you’re witnessing. If necessary (and yeah, this is a pain), strip out all code except the bare minimum HTML and the tags under test to check if it all works. If it does, build up your page piece by piece until it stops working to track it down!

If you’re sure everything checks out in this regard, could you post the output of the smd_query SELECT statement in both article and non-article cases here please? debug="1" will show you that with the least amount of other crap on the page :-)

Last edited by Bloke (2010-01-13 13:28:59)

Bloke · 2010-01-15 13:52:07

speeke wrote:

Perhaps this is where the confusion is? I don’t want to sort by date within an individual article, rather, I need to sort a list of articles by date within a section. Is this the problem I wonder???

Riiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiight!

Aha, it all becomes clear. Sorry for being dim. Stepping through what you’re asking TXP to do:

http://site.com/section lists all articles in that section — i.e. a standard TXP section list. It calls your default form for each article in the section
Each article will thus execute the smd_query; this is not what you want, but let’s sidestep it for now
Adding ?month=2005-12 tells TXP to filter the articles to show only those in the given year-month
Each article will thus run the smd_query. If you only have one article in this month, it’ll run the query once; two articles will cause it to be run twice, and so on
Again, changing the URL to ?month=2005-11 tells TXP to show only article in the given year-month
Since there are no articles in this month/year combination, your default form is never called and thus your smd_query is never called. Result: a blank page

To successfully run it you must therefore put the smd_query on your Page and not in your default form. Your page is always only called once. You may need to detect if the URL var exists before calling smd_query, but essentially that’ll sort you out. Sorry again for not spotting that earlier. D’oh!

Bloke · 2010-01-15 19:45:24

speeke wrote:

I assume that I will also be able to remove the limit="1"

I guess so. One way to find out for sure… ;-)

Bloke · 2010-01-17 11:56:29

Maintenance release v0.41 adds:

mode attribute so you can use the plugin as a single tag if you are performing INSERT/DELETE/UPDATE operations. This attribute is set to automatically detect the query type so you should never have to alter it
hashsize which governs how the plugin creates a unique reference for each smd_query tag. This is useful in situations where you want to put multiple smd_query tags on the same page and paginate each independently. Ties in with…
… {smd_query_unique_id} which allows you to put the unique ID the plugin has generated into your pageform

Take it for a spin and shout if anything breaks or you discover the meaning of life while using the plugin ;-)

Bloke · 2010-01-18 09:47:59

speeke wrote:

It still counts the future dated article in the pageform count

And here is where things get murky… your query does indeed just count Live articles, irrespective of date (you are correct that Posted < CURDATE() is the right way to do things).

Regarding the pagination, it’s actually displaying stuff correctly. There are three articles that matched your query, so as far as the plugin is concerned it is trying to step through them all. However, if (as I assume) you’re using txp:newer and txp:older to page through the results then you’re out of luck because they do not — and will not — take future articles into account. So you’ll get a blank page as you experienced.

Bug? Nope. Unexpected behaviour? Perhaps.

Maybe smd_horizon should be expanded to allow stepping through future article lists as well as individual articles. Hmmmm…

Textpattern CMS

Textpattern CMS support forum

#157 2009-12-05 11:58:33

Re: smd_query: Talk to the database directly via SQL

#158 2009-12-05 12:16:08

Re: smd_query: Talk to the database directly via SQL

#159 2009-12-07 09:39:04

Re: smd_query: Talk to the database directly via SQL

#160 2009-12-07 10:18:00

Re: smd_query: Talk to the database directly via SQL

#161 2010-01-11 09:39:11

Re: smd_query: Talk to the database directly via SQL

#162 2010-01-12 09:55:20

Re: smd_query: Talk to the database directly via SQL

#163 2010-01-13 09:33:32

Re: smd_query: Talk to the database directly via SQL

#164 2010-01-13 13:27:46

Re: smd_query: Talk to the database directly via SQL

#165 2010-01-15 13:52:07

Re: smd_query: Talk to the database directly via SQL

#166 2010-01-15 19:45:24

Re: smd_query: Talk to the database directly via SQL

#167 2010-01-17 11:56:29

Re: smd_query: Talk to the database directly via SQL

#168 2010-01-18 09:47:59

Re: smd_query: Talk to the database directly via SQL

Board footer