smd_query: Talk to the database directly via SQL

roelof · 2010-02-15 19:03:33

Hello,

I have a question.
Can I use this plugin to look for what’s in custom field 1 of the latest article of a month of a category.

Roelof

aslsw66 · 2010-02-15 21:55:56

The simple answer is that if you can construct a valid SQL statement to extract what you want from the database, then this plugin can do whatever you want it to.

Sorry, but I can’t construct the specific query for you (may days as a database expert are long over) but what I have done is test my SQL query on a local version of textpattern – that keeps it clean and simple, and allows me to test my queries by trial-and-error without damaging anything.

mlarino · 2010-02-24 16:05:46

Hi!
Thanks everyone for your help with this plugin, I finaly managed to completely understand it and my search form and results are working great, using maps.
www.mlarino.com/asesoria/inmobiliaria (its ugly now, but will style it soon :)

But I encountered a little problem using pagination,
txp tells me I have a problem with article_custom:
- incorrect use of a tag attribute for article_custom

I am using a Spanish txp instalation:

Error de etiqueta  ->  Textpattern Notice: La plantilla de página resultado-fichas no contiene una etiqueta txp:article  on line 514

It only shows up after clicking on next page.

Any idea why this hapends?

Last edited by mlarino (2010-02-24 16:05:58)

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.

Textpattern CMS

Textpattern CMS support forum

#205 2010-02-15 19:03:33

Re: smd_query: Talk to the database directly via SQL

#206 2010-02-15 21:55:56

Re: smd_query: Talk to the database directly via SQL

#207 2010-02-24 16:05:46

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

Board footer