smd_query: Talk to the database directly via SQL

gomedia · 2009-02-21 22:49:27

Thanks Stef. This was one of the first examples I tried & it threw me a bit. Perhaps next time I’ll start with Example 1 like everybody else!

eivl · 2009-04-09 17:13:19

Hello, first time user of Textpattern and i have found many of your plugins usefull.

I am getting info from a php calendar called supercali event calendar, i have the calendar database in the same database as Txp, with your plugin i have a sql query (tested in phpmyadmin) that gets the name of an event and that from todays date-time and later.

SELECT DISTINCT events.event_id, events.title,events.description, dates.date, dates.event_id,dates.end_date from events,dates WHERE events.event_id=dates.event_id and dates.end_date > now() order by dates.end_date asc

i have more info then i need in the end, but the part that is not working is … dates.end_date > now() …

if anyone can help me with this or maby have a better idea. the output should only be current events, not including old ones (where end_date is lower then systemdate)

Bloke · 2009-04-09 17:25:05

eivl wrote:

but the part that is not working is … dates.end_date > now() …

When you say “not working”, do you mean it’s giving errors, or not returning what you expect? A few things that might help track this down:

Put site in debugging mode to check there are no actual errors the plugin gives back
Use debug="2" in the smd_query tag to see if the plugin’s debug gives you any decent diagnostic info
Confirm you are using TXP >= 4.0.7 (otherwise the > sign in the query will cause the plugin to fall over)
Post your actual, full smd_query tag
Post a tag trace

Or perhaps try smd_calendar so you can do the date stuff in-house without smd_query :-p

Last edited by Bloke (2009-04-09 17:26:08)

eivl · 2009-04-09 17:29:25

Bloke wrote:

eivl wrote:

but the part that is not working is … dates.end_date > now() …

When you say “not working”, do you mean it’s giving errors, or not returning what you expect? A few things that might help track this down:

Put site in debugging mode to check there are no actual errors the plugin gives back
Use debug="2" in the smd_query tag to see if the plugin’s debug gives you any decent diagnostic info
Confirm you are using TXP >= 4.0.7 (otherwise the > sign in the query will cause the plugin to fall over)
Post your actual, full smd_query tag
Post a tag trace

Or perhaps try smd_calendar instead of a 3rd party calendar :-p

Thanks for the quick reply, not working means no output and no errors, i will try to put the site in debugging mode and use debug=2 as well.

i use the latest TXP version.

full tag:

<txp:smd_query query=“SELECT DISTINCT events.event_id, events.title,events.description, dates.date, dates.event_id,dates.end_date from events,dates WHERE events.event_id=dates.event_id and dates.end_date > now() order by dates.end_date asc”> <txp:permlink>{title} {description} {date} {end_date} </txp:permlink>
</txp:smd_query>

the reason for using 3rd party celendar is because i am not the user of the site, the person who wants it is familiar with putting up events in supercali =)

———

EDIT:

in debugging mode and with debug=“2” the site doesnt give me anything

Last edited by eivl (2009-04-09 17:35:31)

eivl · 2009-04-09 17:42:38

Tag Trace with the problem (lower is the same trace, but then i have removed the now() function)

<!— Runtime: 0.0331 —>
<!— Query time: 0.009439 —>
<!— Queries: 18 —>
<!— Memory: 2456Kb, <txp:link_to_next> —>
<!— txp tag trace:
[SQL (0.00057315826416016): select name, data from txp_lang where lang=‘en-us’ AND ( event=‘public’ OR event=‘common’)]
[SQL (0.0010290145874023): select name, code, version from txp_plugin where status = 1 AND type IN (0,1) order by load_order]
[SQL (0.00019693374633789): select name,code,version from txp_plugin where status = 1 AND name=‘smd_lib’]
[SQL (0.00053501129150391): select ID,Section from textpattern where ID = 8 and Status >= 4 limit 1]
[SQL (0.00020503997802734): select page, css from txp_section where name = ‘articles’ limit 1]
[SQL (0.00050806999206543): select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from textpattern where ID=8 and Status in (4,5)]
[article 8]
[SQL (0.00038599967956543): select ID, Title, url_title, unix_timestamp(Posted) as uposted from textpattern where Posted > ’2009-04-08 19:52:37’ and Section = ‘articles’ and Status=4 and Posted < now() and (now() <= Expires or Expires = ’0000-00-00 00:00:00’) order by Posted asc limit 1]
[SQL (0.001223087310791): select ID, Title, url_title, unix_timestamp(Posted) as uposted from textpattern where Posted < ’2009-04-08 19:52:37’ and Section = ‘articles’ and Status=4 and Posted < now() and (now() <= Expires or Expires = ’0000-00-00 00:00:00’) order by Posted desc limit 1]
[SQL (0.0010409355163574): select host from txp_log where ip=‘87.76.120.65’ limit 1]
[SQL (0.00086688995361328): insert into txp_log set `time`=now(),page=’/V2/index.php?id=8’,ip=‘87.76.120.65’,host=‘87.76.120.65’,refer=’‘,status=‘200’,method=‘GET’]
[SQL (0.0002291202545166): select user_html from txp_page where name=‘archive’]
[Page: archive]
<txp:page_title />
<txp:site_url />
<txp:feed_link flavor=“atom” format=“link” label=“Atom” />
<txp:feed_link flavor=“rss” format=“link” label=“RSS” />
<txp:css format=“link” />
<txp:rsd />
<txp:linklist category=“meny” break=” “ sort=“date” /> [SQL (0.00028419494628906): select *, unix_timestamp(date) as uDate from txp_link where category IN (‘meny’) order by date ] [SQL (0.00018906593322754): select Form from txp_form where name=‘plainlinks’] [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle />
<txp:if_category> [<txp:if_category>: false] <txp:if_search> [<txp:if_search>: false] </txp:if_search> <txp:article limit=“5” /> [SQL (0.00025701522827148): select Form from txp_form where name=‘default’] [Form: default] <txp:if_individual_article> [<txp:if_individual_article>: true] <txp:permlink> <txp:title /> </txp:permlink> </txp:if_individual_article> <txp:posted /> <txp:body /> <txp:permlink> </txp:permlink> <txp:author /> [SQL (0.00039792060852051): select RealName from txp_users where `name` = ‘eivl’ limit 1] <txp:category1 title=“1” link=“1” /> <txp:category2 title=“1” link=“1” /> <txp:comments_invite wraptag=“p” /> <txp:site_url /> [SQL (0.00026202201843262): select Form from txp_form where name=‘comments_display’] [Form: comments_display] <txp:text item=“comment” /> <txp:comments_invite textonly=“1” showalways=“1” showcount=“0” /> <txp:comments /> <txp:if_comments_preview> [<txp:if_comments_preview>: false] </txp:if_comments_preview> <txp:if_comments_allowed> [<txp:if_comments_allowed>: false] <txp:text item=“comments_closed” /> </txp:if_comments_allowed>
</txp:if_category>
<txp:if_individual_article> [<txp:if_individual_article>: true] <txp:site_url /> <txp:link_to_prev> [SQL (0.0003669261932373): select ID as thisid, Section as section, Title as title, url_title, unix_timestamp(Posted) as posted from textpattern where ID = 2] <txp:prev_title /> </txp:link_to_prev> <txp:link_to_next> </txp:link_to_next>
</txp:if_individual_article>
[ ~~~ secondpass ~~~ ] —>

Tag trace without the now() function

<!— Runtime: 0.0463 —>
<!— Query time: 0.010669 —>

<!— Queries: 19 —>
<!— Memory: 2465Kb, <txp:link_to_next> —>
<!— txp tag trace:
[SQL (0.00075578689575195): select name, data from txp_lang where lang=‘en-us’ AND ( event=‘public’ OR event=‘common’)]
[SQL (0.0016450881958008): select name, code, version from txp_plugin where status = 1 AND type IN (0,1) order by load_order]
[SQL (0.00024104118347168): select name,code,version from txp_plugin where status = 1 AND name=‘smd_lib’]
[SQL (0.00077486038208008): select ID,Section from textpattern where ID = 8 and Status >= 4 limit 1]
[SQL (0.00018310546875): select page, css from txp_section where name = ‘articles’ limit 1]
[SQL (0.00055098533630371): select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from textpattern where ID=8 and Status in (4,5)]
[article 8]
[SQL (0.00052595138549805): select ID, Title, url_title, unix_timestamp(Posted) as uposted from textpattern where Posted > ’2009-04-08 19:52:37’ and Section = ‘articles’ and Status=4 and Posted < now() and (now() <= Expires or Expires = ’0000-00-00 00:00:00’) order by Posted asc limit 1]
[SQL (0.0010180473327637): select ID, Title, url_title, unix_timestamp(Posted) as uposted from textpattern where Posted < ’2009-04-08 19:52:37’ and Section = ‘articles’ and Status=4 and Posted < now() and (now() <= Expires or Expires = ’0000-00-00 00:00:00’) order by Posted desc limit 1]
[SQL (0.00093507766723633): select host from txp_log where ip=‘87.76.120.65’ limit 1]
[SQL (0.00075387954711914): insert into txp_log set `time`=now(),page=’/V2/index.php?id=8’,ip=‘87.76.120.65’,host=‘87.76.120.65’,refer=’‘,status=‘200’,method=‘GET’]
[SQL (0.0001828670501709): select user_html from txp_page where name=‘archive’]
[Page: archive]
<txp:page_title />
<txp:site_url />
<txp:feed_link flavor=“atom” format=“link” label=“Atom” />
<txp:feed_link flavor=“rss” format=“link” label=“RSS” />
<txp:css format=“link” />
<txp:rsd />
<txp:linklist category=“meny” break=” “ sort=“date” /> [SQL (0.00023293495178223): select *, unix_timestamp(date) as uDate from txp_link where category IN (‘meny’) order by date ] [SQL (0.00022482872009277): select Form from txp_form where name=‘plainlinks’] [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle />
<txp:if_category> [<txp:if_category>: false] <txp:if_search> [<txp:if_search>: false] </txp:if_search> <txp:article limit=“5” /> [SQL (0.00019502639770508): select Form from txp_form where name=‘default’] [Form: default] <txp:if_individual_article> [<txp:if_individual_article>: true] <txp:permlink> <txp:title /> </txp:permlink> </txp:if_individual_article> <txp:posted /> <txp:body /> <txp:smd_query debug=“2” query=“SELECT DISTINCT events.event_id, events.title,events.description, dates.date, dates.event_id,dates.end_date from events,dates WHERE events.event_id=dates.event_id”> [SQL (0.00035595893859863): SELECT DISTINCT events.event_id, events.title,events.description, dates.date, dates.event_id,dates.end_date from events,dates WHERE events.event_id=dates.event_id] [<txp:smd_query debug=“2” query=“SELECT DISTINCT events.event_id, events.title,events.description, dates.date, dates.event_id,dates.end_date from events,dates WHERE events.event_id=dates.event_id”>: true] <txp:permlink> </txp:permlink> <txp:permlink> </txp:permlink> <txp:permlink> </txp:permlink> <txp:permlink> </txp:permlink> <txp:permlink> </txp:permlink> <txp:permlink> </txp:permlink> </txp:smd_query> <txp:author /> [SQL (0.00018095970153809): select RealName from txp_users where `name` = ‘eivl’ limit 1] <txp:category1 title=“1” link=“1” /> <txp:category2 title=“1” link=“1” /> <txp:comments_invite wraptag=“p” /> <txp:site_url /> [SQL (0.00017094612121582): select Form from txp_form where name=‘comments_display’] [Form: comments_display] <txp:text item=“comment” /> <txp:comments_invite textonly=“1” showalways=“1” showcount=“0” /> <txp:comments /> <txp:if_comments_preview> [<txp:if_comments_preview>: false] </txp:if_comments_preview> <txp:if_comments_allowed> [<txp:if_comments_allowed>: false] <txp:text item=“comments_closed” /> </txp:if_comments_allowed>
</txp:if_category>
<txp:if_individual_article> [<txp:if_individual_article>: true] <txp:site_url /> <txp:link_to_prev> [SQL (0.00057697296142578): select ID as thisid, Section as section, Title as title, url_title, unix_timestamp(Posted) as posted from textpattern where ID = 2] <txp:prev_title /> </txp:link_to_prev> <txp:link_to_next> </txp:link_to_next>
</txp:if_individual_article>
[ ~~~ secondpass ~~~ ] —>

Last edited by eivl (2009-04-09 17:43:19)

eivl · 2009-04-09 17:45:42

It looks like the query isnt able to run when including now() with the < or > operator, might even be true for all WHERE instances. using SQL alias works, used just now for debugging.

Bloke · 2009-04-09 17:57:36

eivl wrote:

<txp:smd_query query="SELECT DISTINCT events.event_id, events.title,events.description, dates.date, dates.event_id,dates.end_date from events,dates WHERE events.event_id=dates.event_id and  dates.end_date > now() order by dates.end_date asc">
   <txp:permlink>{title} {description} {date} {end_date} </txp:permlink>
</txp:smd_query>

Hmmm query looks OK to me and you’re using good replacement {} vars. One thing you will need to address is that the permlink won’t know which article to link to because you haven’t told it about anything in the TXP database. The permlink needs an article ‘context’ or a forced ‘id’ of an article to link to, and since you’re only using the ‘dates’ and ‘events’ tables it will do nothing.

However, that won’t explain why debug="2" gives you nothing out. I would expect at the very least to see ++ QUERY RESULT SET ++ shown and a list of matching rows; especially since the query works in phpMyAdmin. This is a puzzler.

As you found, the tag trace shows that the entire smd_query tag is being ignored when the > now() is present in the attribute; the tag is entirely missing from your first trace. That implies that the plugin is choking on the syntax, which is odd because I’ve done things like that before and it’s worked. *ponder*

EDIT: e.g.

query="select * from textpattern where Posted > now()"

works for me. I wonder if it is the dots causing the problem, though the plugin does nothing more than throw your entire query at the internal getRows() function which can definitely handle this kind of thing.

Last edited by Bloke (2009-04-09 18:03:47)

eivl · 2009-04-09 18:03:16

would it help you to see if the page or be given an user on my TXP site?

i realy have no clue why >now() is making the query skipp it without posting anything in debug..

is this right btw ; <txp:smd_query debug=“2” query=“SELECT DISTI………. ?

Bloke · 2009-04-09 18:05:34

eivl wrote:

i realy have no clue why >now() is making the query skipp it without posting anything in debug..

Me neither, it’s freaky.

would it help you to see if the page or be given an user on my TXP site?

The latter would help yes, if you don’t mind. Drop me a note and I’ll send you my email address

Last edited by Bloke (2009-04-09 18:08:42)

eivl · 2009-04-09 18:09:11

cant find a send message button here, can i email you?

EDIT:

Done! =)

Last edited by eivl (2009-04-09 18:14:57)

Bloke · 2009-04-09 18:20:27

Howzat now? Looks like it’s fixed to me. I think the problem was that you had the smd_query embedded directly in an article. Textile was coming along, processing the contents of the tag and trashing the > bit. Setting the Advanced Options -> Article markup dropdown on the left of the Write tab to Leave text untouched seems to have solved it. Please confirm if that’s dong what you expect now.

Last edited by Bloke (2009-04-09 18:21:25)

eivl · 2009-04-09 18:23:55

Bloke wrote:

Howzat now? Looks like it’s fixed to me. I think the problem was that you had the smd_query embedded directly in an article. Textile was coming along, processing the contents of the tag and trashing the > bit. Setting the Advanced Options -> Article markup dropdown on the left of the Write tab to Leave text untouched seems to have solved it. Please confirm if that’s dong what you expect now.

=)

I belive i have to kiss you! (or maby i can send my wife)

Thanks, it works like a charm now. didnt know article markup was scr@wing around.

you realy should have an donate link on your webpage! =)

Textpattern CMS

Textpattern CMS support forum

#85 2009-02-21 22:49:27

Re: smd_query: Talk to the database directly via SQL

#86 2009-04-09 17:13:19

Re: smd_query: Talk to the database directly via SQL

#87 2009-04-09 17:25:05

Re: smd_query: Talk to the database directly via SQL

#88 2009-04-09 17:29:25

Re: smd_query: Talk to the database directly via SQL

#89 2009-04-09 17:42:38

Re: smd_query: Talk to the database directly via SQL

#90 2009-04-09 17:45:42

Re: smd_query: Talk to the database directly via SQL

#91 2009-04-09 17:57:36

Re: smd_query: Talk to the database directly via SQL

#92 2009-04-09 18:03:16

Re: smd_query: Talk to the database directly via SQL

#93 2009-04-09 18:05:34

Re: smd_query: Talk to the database directly via SQL

#94 2009-04-09 18:09:11

Re: smd_query: Talk to the database directly via SQL

#95 2009-04-09 18:20:27

Re: smd_query: Talk to the database directly via SQL

#96 2009-04-09 18:23:55

Re: smd_query: Talk to the database directly via SQL

Board footer