Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

roelof
Member
Registered: 2005-03-27
Posts: 647

Re: smd_query: Talk to the database directly via SQL

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

Offline

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

aslsw66
Member
From: Canberra, Australia
Registered: 2004-08-04
Posts: 342
Website

Re: smd_query: Talk to the database directly via SQL

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.

Offline

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

mlarino
Member
Registered: 2007-06-29
Posts: 367

Re: smd_query: Talk to the database directly via SQL

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)

Offline

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

mlarino
Member
Registered: 2007-06-29
Posts: 367

Re: smd_query: Talk to the database directly via SQL

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)

Offline

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

jakob
Admin
From: Germany
Registered: 2005-01-20
Posts: 4,628
Website

Re: smd_query: Talk to the database directly via SQL

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


TXP Builders – finely-crafted code, design and txp

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,299
Website GitHub

Re: smd_query: Talk to the database directly via SQL

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)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

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

jakob
Admin
From: Germany
Registered: 2005-01-20
Posts: 4,628
Website

Re: smd_query: Talk to the database directly via SQL

…that’s just off the top of my head

Wow, that’s quite impressive!


TXP Builders – finely-crafted code, design and txp

Offline

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

dinoact
New Member
Registered: 2010-03-17
Posts: 9

Re: smd_query: Talk to the database directly via SQL

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">

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,299
Website GitHub

Re: smd_query: Talk to the database directly via SQL

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)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

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

dinoact
New Member
Registered: 2010-03-17
Posts: 9

Re: smd_query: Talk to the database directly via SQL

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.

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,299
Website GitHub

Re: smd_query: Talk to the database directly via SQL

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)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

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

dinoact
New Member
Registered: 2010-03-17
Posts: 9

Re: smd_query: Talk to the database directly via SQL

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

Offline

Board footer

Powered by FluxBB