smd_query: Talk to the database directly via SQL

kostas45 · 2009-12-04 10:20:21

Stef, I am limiting to 5 and I get:

Page 1 of 4
Showing records 1 to 5 of 19

but Next 5 and Previous 5 do not print.

-Kostas

Bloke · 2009-12-04 10:50:54

kostas45 wrote:

Page 1 of 4 Showing records 1 to 5 of 19 but Next 5 and Previous 5 do not print.

Weird, it works for me, though I have just thought of an enhancement: some new replacement variables to help with navigation that indicate how many records are on the next/prev pages and what the next/prev page numbers are. That will allow you to put the older/newer tags in your pageform and have it automaitcally render the correct number of records , e.g. Next 5, Next 5, Next 5, Next 4 in your case. It will also allow you to build your own next/prev links more easily. I’ll add that when I get a moment.

You may need to watch out for this caveat. It may be affecting you here(?)

Also, if you put the smd_query and associated older/newer tags inside an article they may not show up because, by their nature, they are designed to be used on list pages. I tried one a minute ago in an article and, although it rendered the next/prev links, their destinations were to the section landing page and not the article itself. My proposed fix above will address that so you can test for the existence of the next page and display your own next/prev links if you wish to do something a little more esoteric inside an article context.

After that I don’t know what could be up with your implementation. It works for me so it’ll probably be something subtle like the fact you only have a few articles or the newer/older tags are being intercepted by TXP’s article tag. It could be a bug in the plugin or a silly assumption I’ve made. Is there any more info you can supply, like where the smd_query tag is being used (page, form, section, article?), your exact tag code and a rough indication of how many articles are in the section in which the smd_query appears (because it may have a bearing on the results here).

Last edited by Bloke (2009-12-04 10:51:09)

kostas45 · 2009-12-04 19:01:42

Still no luck. It seems to me I need to change the whole site structure, for the older/newer tags to work!
I just need to page some records anywhere. I should not care if I am in the correct section, or in a list, or whatever. Just page records :-P
Hey, is this smd_pagicle territory? I believe, it is!
I think I ‘ll wait for it to grow up and be history aware ;-)
Stef, thanks anyway!

-Kostas

Bloke · 2009-12-05 08:34:38

kostas45 wrote:

It seems to me I need to change the whole site structure, for the older/newer tags to work! I just need to page some records anywhere. I should not care if I am in the correct section, or in a list, or whatever.

If you’re using txp:older and txp:newer to page records, it does matter a great deal because those tags do specific things in TXP! I’ve allowed you to use them as a convenience to page records in this plugin. If you’re already using them for other things then you’ll have to build your own older/newer links because the plugin and your article list will clash.

The new version of the plugin (I altered it yesterday) allows you to more easily make your own links and separate the functionality of your smd_query from your article lists so you’re not tied to using the built-in older/newer tags. I’m just documenting it and then it’ll be released.

Bloke · 2009-12-05 10:30:25

OK, try the enhanced v0.4

Features:

New replacement tags {smd_prevpage}, {smd_nextpage}, {smd_rows_prev} and {smd_rows_next} for greater flexibility in your pageform
New attribute: pagevar to control which variable smd_query uses for paging (in order to break the link between article paging and smd_query when using older/newer)
Two new conditional tags <txp:smd_query_if_prev> and <txp:smd_query_if_next>, useful for building your own pagination controls (you can of course use smd_if if you prefer)
Removed pgonly because paging can now only be done inside the pageform

See how you get on with that one. Remember, <txp:older> and <txp:newer> can now only step through smd_query results if they are used inside the pageform. smd_query no longer clobbers the ‘external’ paging information so it is is returned to its original state as soon as the smd_query closing tag is encountered.

kostas45 · 2009-12-05 11:31:40

My results from local testing of docs examples:

Example 10: It works, except:
I am using a sticky form to do fulltext searching, so I also need my url (get) vars on all pages together with the pg var.
Should next/prev links take account of that?

Example 11:
Same as above, url vars are not carried over to pages, plus:
Next/prev link targets are somehow wrong.

-Kostas

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!

Textpattern CMS

Textpattern CMS support forum

#151 2009-12-04 10:20:21

Re: smd_query: Talk to the database directly via SQL

#152 2009-12-04 10:50:54

Re: smd_query: Talk to the database directly via SQL

#153 2009-12-04 19:01:42

Re: smd_query: Talk to the database directly via SQL

#154 2009-12-05 08:34:38

Re: smd_query: Talk to the database directly via SQL

#155 2009-12-05 10:30:25

Re: smd_query: Talk to the database directly via SQL

#156 2009-12-05 11:31:40

Re: smd_query: Talk to the database directly via SQL

#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

Board footer