smd_query: Talk to the database directly via SQL

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! =)

Bloke · 2009-04-09 18:28:18

eivl wrote:

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

lol! Glad it works now. I’d completely forgotten about article markup destroying tags like that. For future reference, it does the same to stuff inside <txp:php> tags.

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

I do ;-)

eivl · 2009-04-09 19:16:48

There, an Amazon package will arive you after easter.. =)

—-
Edit

Delivery #1: 1 item – delivery estimate: 17 April 2009 – 21 April 2009

Last edited by eivl (2009-04-09 21:20:40)

Bloke · 2009-04-09 19:35:44

eivl wrote:

There, an Amazon package will arive you after easter.. =)

Thank you very much! Looking forward to it.

Bloke · 2009-04-11 09:23:49

Forgot to post this a while back. Minor update available that adds $_SERVER variable support so you can make queries based on servery things.

Download

M_i · 2009-05-15 21:56:35

Hello all,

I’m trying to make part of the form conditional, depending on whether or not a field has any data in it.

For example, say custom_1 is set as the article’s subtitle:
_{(And of course you wouldn’t use smd_query at all in this example — i’m just using this very simple query as an example.)}

<txp:smd_query query="SELECT Title, custom_1 FROM textpattern">
{Title}: {custom_1}
</txp:smd_query>

In that example, I’d like a way to make the colon appear only when there is a custom_1 value. An article with a subtitle would appear as “Title: subtitle”, an article without a subtitle as simply “Title”.

Including a txp:if_custom_field in the form doesn’t work, nor does CONCAT(':', custom_1) AS subtitle because an empty custom field isn’t NULL.

I’m probably making this far more complicated that it is but I can’t see how to do this just now. Any help would be much appreciated!!

Last edited by M_i (2009-05-16 08:23:16)

Bloke · 2009-05-16 14:14:55

M_i

At the expense of using another plugin, could you use either chh_if_data or smd_if inside the query? e.g.

<txp:smd_query query="SELECT Title, custom_1 FROM textpattern">
{Title}<txp:smd_if field="custom1" operator="isused">: {custom_1}</txp:smd_if>
</txp:smd_query>

? There might also be some clever way in SQL to omit empty stuff, I’m not really that good with it.

EDIT: of course, in your simple example you could just do:

<txp:title /><txp:if_custom_field name="custom1">: <txp:custom_field name="custom1" /></txp:if_custom_field> :-)

Last edited by Bloke (2009-05-16 14:18:00)

M_i · 2009-05-16 16:34:12

Bloke wrote:

At the expense of using another plugin, could you use either chh_if_data or smd_if inside the query? e.g.

I just tried with smd_if (already use that elsewhere anyway), but couldn’t get anything to show up, not even after wrapping the whole thing in an asy_wondertag. Will try chh_if_data later when I have more time …and i’m probably not running the latest versions of smd_if either (or the latest smd_query, for that matter), so updating is another thing to try. Anyway, thanks a lot for your help!

Bloke wrote:

EDIT: of course, in your simple example you could just do: <txp:title /><txp:if_custom_field name="custom1">: <txp:custom_field name="custom1" /></txp:if_custom_field> :-)

Yeah, like I already said above, of course you’d never use smd_query in this case. I used a very simple example for clarity. My actual query joins three tables, one of which isn’t a textpattern table, so this marvellous plugin is a real godsend.

lous · 2009-05-21 09:30:21

Hey,
im using

<txp:smd_query query='SELECT ID, Title, Category2 FROM textpattern 
     <txp:variable name="sqlmatch" />' wraptag="table">
  <!-- smd_query container -->
  <txp:if_variable name="zebra" value="odd">
    <txp:variable name="zebra" value="even" />
  <txp:else />
    <txp:variable name="zebra" value="odd" />
  </txp:if_variable>
  <tr class="<txp:variable name="zebra" />">
    <td><txp:permlink id="{ID}">{Title}</txp:permlink></td>
    <td><txp:category name="{Category2}" title="1" link="1" /></td>
  </tr>
</txp:smd_query>

Is there a way to show only that query if category like “current category”?

Bloke · 2009-05-21 17:50:37

lous wrote:

Is there a way to show only that query if category like “current category”?

Assuming sqlmatch contains your where clause, you could try adding either:

AND category2 = '?category'

or

AND category2 like '%?category%'

depending on the type of result you desired. It’s untested but I think that would work. Or wrap the whole query in smd_if?

Textpattern CMS

Textpattern CMS support forum

#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

#97 2009-04-09 18:28:18

Re: smd_query: Talk to the database directly via SQL

#98 2009-04-09 19:16:48

Re: smd_query: Talk to the database directly via SQL

#99 2009-04-09 19:35:44

Re: smd_query: Talk to the database directly via SQL

#100 2009-04-11 09:23:49

Re: smd_query: Talk to the database directly via SQL

#101 2009-05-15 21:56:35

Re: smd_query: Talk to the database directly via SQL

#102 2009-05-16 14:14:55

Re: smd_query: Talk to the database directly via SQL

#103 2009-05-16 16:34:12

Re: smd_query: Talk to the database directly via SQL

#104 2009-05-21 09:30:21

Re: smd_query: Talk to the database directly via SQL

#105 2009-05-21 17:50:37

Re: smd_query: Talk to the database directly via SQL

Board footer