smd_query: Talk to the database directly via SQL

mlarino · 2010-03-13 16:15:14

Hi
I am working on the pagination, and cant find a way to do it like this:

<PREV 1… 8 9 10 11 …17 NEXT>

Is it posible using the replacement tags?

THANKS!

Last edited by mlarino (2010-03-13 16:20:18)

jakob · 2010-03-13 16:49:23

incorrect use of a tag attribute for article_custom

If I understand spanish correctly, I think it says that you have no txp:article tag in your page template “resultado-fichas”. Check you page template as that may be true when you’re using smd_query instead of txp:article. I’m not sure why it doesn’t show on the front page but does on a pagination page – you might want to check how your form works to see in which circumstances txp:article is called or not.
In any case, if I recall correctly, it’s just a warning that stops showing as soon as you switch to “live”.

Bloke · 2010-03-14 15:28:43

mlarino wrote:

a way to do it like this: <PREV 1… 8 9 10 11 …17 NEXT>

You need to think outside the box a bit… well, ok, a lot :-)

This isn’t all the way there yet because it only shows one number after the current page… if you want to show two you’ll need a few more tests and a couple more variables, but I’ll leave that to you as homework!

The general idea is this:

<txp:variable name="midpoint">{smd_thispage}</txp:variable>
<txp:adi_calc name="last_but_one" value="{smd_pages}" subtract="1" />

<txp:smd_if field="{smd_thispage}" operator="gt" value="2">
<txp:variable name="first_page">1</txp:variable>
<txp:adi_calc name="page_before" value='<txp:variable name="midpoint" />' subtract="1" />
<txp:variable name="before_mark" value="&hellip" />
<txp:else />
<txp:variable name="first_page" value="" />
<txp:variable name="page_before" value="" />
<txp:variable name="before_mark" value="" />
</txp:smd_if>

<txp:smd_if field="{smd_thispage}" operator="lt" value='<txp:variable name="last_but_one" />'>
<txp:adi_calc name="page_after" value='<txp:variable name="midpoint" />' add="1" />
<txp:variable name="after_mark" value="&hellip" />
<txp:else />
<txp:variable name="page_after" value="" />
<txp:variable name="after_mark" value="" />
</txp:smd_if>

<txp:smd_if field="{smd_thispage}" operator="eq" value="{smd_pages}">
<txp:variable name="last_page" value="" />
<txp:else />
<txp:variable name="last_page">{smd_pages}</txp:variable>
</txp:smd_if>

<

<txp:smd_query_if_prev>
<a href="?pg={smd_prevpage}"><b>PREV</b></a>
<txp:else />
PREV
</txp:smd_query_if_prev>
<txp:if_variable name="first_page" value="1">
<a href="?pg=1">1</a>
</txp:if_variable>
<txp:variable name="before_mark" />
<a href="?pg=<txp:variable name="page_before" />"><txp:variable name="page_before" /></a>
<a href="?pg={smd_thispage}"><b>{smd_thispage}</b></a>
<a href="?pg=<txp:variable name="page_after" />"><txp:variable name="page_after" /></a>
<txp:variable name="after_mark" />
<txp:if_variable name="last_page" value="">
<txp:else />
<a href="?pg={smd_pages}">{smd_pages}</a>
</txp:if_variable>
<txp:smd_query_if_next>
<a href="?pg={smd_nextpage}"><b>NEXT</b></a>
<txp:else />
NEXT
</txp:smd_query_if_prev>

>

There’s probably a more efficient way to do this; that’s just off the top of my head.

Last edited by Bloke (2010-03-14 16:56:21)

jakob · 2010-03-14 17:31:47

…that’s just off the top of my head

Wow, that’s quite impressive!

dinoact · 2010-03-17 19:26:30

I’m trying to display a list of posts from other years with matching months and days and am having some trouble. Maybe it isn’t possible with this plugin, or maybe I’m just doing it all wrong (more likely the case). Anyone have any ideas?

Example:
You’re viewing a post that was published 3-17-2009, the list would look like:
2006: ‘This is the post title from 3-17-2006’
2007: ‘This is the post title from 3-17-2006’
2008: ‘This is the post title from 3-17-2006’
2010: ‘This is the post title from 3-17-2006’

I set these global variables so I would have an easy way to parse the date in the sql, but it isn’t working for me. Also, I couldn’t find a better way to set these so I could use them in the sql statement.

<txp:php>
global $entryYear; $entryYear = posted(array('format'=>'%Y'));
global $entryMonth; $entryMonth = posted(array('format'=>'%m'));
global $entryDay; $entryDay = posted(array('format'=>'%d'));
</txp:php>
<txp:variable name="previousYear" value="<txp:php>global $entryYear; echo $entryYear;</txp:php>" />
<txp:variable name="previousMonth" value="<txp:php>global $entryMonth; echo $entryMonth;</txp:php>" />
<txp:variable name="previousDay" value="<txp:php>global $entryDay; echo $entryDay;</txp:php>" />

This was just my lame attempt to get the statement working. The final statement would look similar to:
“SELECT * FROM textpattern WHERE month(posted) = ‘?previousMonth’ and dayofmonth(posted) = ‘?previousYear’ and year(posted) <> ‘?previousYear’ ORDER BY posted desc”

<txp:variable name="previousDay"/>
<txp:smd_query query="SELECT * FROM textpattern
WHERE posted like '%?previousMonth%'" debug="2">

Bloke · 2010-03-17 19:56:14

dinoact wrote:

I’m trying to display a list of posts from other years with matching months and days

Without trying this, here’s something that might get you close:

<txp:php>
global $variable;

$ts = posted(array('format' => '%s')); // UNIX timestamp of the current article
$variable['entry_year'] = strftime('%Y', $ts);
$variable['entry_month'] = strftime('%m', $ts);
$variable['entry_day'] = strftime('%d', $ts);

</txp:php>

So that sets up three txp:variables of the current year, month and day. Then:

<txp:smd_query query="SELECT Title, Posted, YEAR(Posted) AS theYear
         FROM textpattern
         WHERE DATE_FORMAT(Posted, '%m') = '?entry_month'
         AND DATE_FORMAT(Posted, '%d') = '?entry_day'
         AND YEAR(Posted) != '?entry_year'
         ORDER BY Posted desc"
     wraptag="ul" break="li">
{theYear} : {Title}
</txp:smd_query>

I think that should do it, or at least get you moving in the right direction. Note that this version uses DATE_FORMAT and not MONTH / DAYOFMONTH. That’s because DATE_FORMAT returns values with the leading zero. Though it might not be necessary, it’s one less thing to worry about whether 9 == 09 in MySQL.

Hope that helps.

EDIT: fixed variable names to contain all lower case letters.

Last edited by Bloke (2010-03-17 22:17:13)

dinoact · 2010-03-17 21:59:32

Bloke wrote:

Without trying this, here’s something that might get you close:

For some reason it doesn’t seem to like the variables in the Where clause. Though the variables seem to look fine if I display them through <txp:variable />. I can enter numeric values where the variables would go and that works fine. I also tried just month, just day, setting $variable[‘entryDay’] to int. All with no luck. Not quite sure what is going on.

Bloke · 2010-03-17 22:14:32

dinoact wrote:

For some reason it doesn’t seem to like the variables in the Where clause.

D’oh! My fault. The names should all be in lower case (it’s a plugin quirk/feature). I fixed the code in the above post.

Last edited by Bloke (2010-03-17 22:18:25)

dinoact · 2010-03-17 23:12:09

Ahh, thanks. I didn’t catch that either. It works perfectly now. Thanks.

jakob · 2010-03-18 07:53:54

dinoact, that’d make a nice “this time last year…” txptip for (photo)blog authors with lots of posts.

mlarino · 2010-03-24 09:34:39

Stef! thanks for that intro on how to format the pagination, but I was working on it and just found a little problem with your plugin.

When I navigate to page 2 of the pagination smd_query creates, it ads /?pg=2 to the URL and it messes up my CSS and my JS
When I remove manually the slash / it all works ok.

You can test it in www.mlarino.com/asesoria/inmobiliaria
make a search of “todos” and “todos” (then when you see the map click on “ver todos” on the right side and you will get the results with pagination.

Dont know why it messes up the CSS or JS of top of the site but, is there a way to remove that / from the code?
thanks!

Bloke · 2010-03-24 10:31:43

mlarino wrote:

is there a way to remove that / from the code?

Have you tried putting a <txp:permlink /> inside the anchor tags before the ?pg= bits? Not sure if it’ll work but it might.

mlarino · 2010-03-24 11:09:48

Bloke wrote:

Have you tried putting a <txp:permlink /> inside the anchor tags before the ?pg= bits? Not sure if it’ll work but it might.

<txp:permlink /> ? I dont understand, and what do you mean with anchor tags? the /?pg= is generated by smd_querys pagination…

When I see the list of articles and click on NEXT> I reach http://www.mlarino.com/asesoria/resultado-fichas/?pg=2

That Slash in /?pg=2 is what is wrong.

Last edited by mlarino (2010-03-24 11:10:51)

Bloke · 2010-03-24 11:20:10

mlarino wrote:

what do you mean with anchor tags? the /?pg= is generated by smd_querys pagination…

Are you using that huge wodge of code I posted above? The ?pg= part is generated by that, not smd_query. As an example, the last part reads:

<txp:smd_query_if_next>
<a href="?pg={smd_nextpage}"><b>NEXT</b></a>
<txp:else />
NEXT
</txp:smd_query_if_prev>

I was just wondering if the browser or TXP was adding the slash automatically for some reason, so perhaps hardcoding the URL like this might fix it:

<txp:smd_query_if_next>
<a href="<txp:permlink />?pg={smd_nextpage}"><b>NEXT</b></a>
<txp:else />
NEXT
</txp:smd_query_if_prev>

?

Last edited by Bloke (2010-03-24 11:21:10)

mlarino · 2010-03-24 11:20:55

Oh wait! :)

think I got what you meant.

<txp:smd_query_if_prev> <a href="<txp:permlink />?pg={smd_prevpage}"> Previous {smd_rows_prev}</a> </txp:smd_query_if_prev> <txp:smd_query_if_next> <a href="<txp:permlink />?pg={smd_nextpage}"> Next {smd_rows_next}</a> </txp:smd_query_if_next>

It works!

Textpattern CMS

Textpattern CMS support forum

#196 2010-03-13 16:15:14

Re: smd_query: Talk to the database directly via SQL

#197 2010-03-13 16:49:23

Re: smd_query: Talk to the database directly via SQL

#198 2010-03-14 15:28:43

Re: smd_query: Talk to the database directly via SQL

#199 2010-03-14 17:31:47

Re: smd_query: Talk to the database directly via SQL

#200 2010-03-17 19:26:30

Re: smd_query: Talk to the database directly via SQL

#201 2010-03-17 19:56:14

Re: smd_query: Talk to the database directly via SQL

#202 2010-03-17 21:59:32

Re: smd_query: Talk to the database directly via SQL

#203 2010-03-17 22:14:32

Re: smd_query: Talk to the database directly via SQL

#204 2010-03-17 23:12:09

Re: smd_query: Talk to the database directly via SQL

#205 2010-03-18 07:53:54

Re: smd_query: Talk to the database directly via SQL

#206 2010-03-24 09:34:39

Re: smd_query: Talk to the database directly via SQL

#207 2010-03-24 10:31:43

Re: smd_query: Talk to the database directly via SQL

#208 2010-03-24 11:09:48

Re: smd_query: Talk to the database directly via SQL

#209 2010-03-24 11:20:10

Re: smd_query: Talk to the database directly via SQL

#210 2010-03-24 11:20:55

Re: smd_query: Talk to the database directly via SQL

Board footer