smd_query: Talk to the database directly via SQL

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?

lous · 2009-05-22 18:54:21

Hey bloke it doesnt work :(

Bloke · 2009-05-23 10:01:22

lous wrote:

Hey bloke it doesnt work :(

That’s not very helpful without some context. How does it not work? Any errors? What’s your new Form look like? What have you put in sqlmatch? Do you mean the first or second line of code didn’t work when you added it ? Or when you tried smd_if?

I assume you’re using the example from either jakob or myself? Please give me some kind of diagnostics so I can help. I’m many things, but a clairvoyant is not one of them :-)

lous · 2009-05-23 21:06:51

hmm i made it a bit more complicated now, but it works :D
But theres another thing

<txp:smd_query query='SELECT ID, Title, Image, custom_1, custom_2, custom_7 FROM textpattern ORDER BY custom_7 desc limit 10'>
           <tr>
    <td><txp:permlink id="{ID}">{Title}</txp:permlink></td>
    <td><txp:category name="{Category2}" title="1" link="1" /></td>
    <td>{custom_1}</td>
    <td>{custom_2}</td>
    <td>{custom_7}</td>
</tr>
</txp:smd_query>

custom_7 is a counter for my articles, by default they get an empty value. It just increase by clicking on an article. So i want a list with the top 10 most clicked. If im not mistaken, my query should be ok, but its not in my frontend :)

Do you know the solution?

Textpattern CMS

Textpattern CMS support forum

#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

#106 2009-05-22 18:54:21

Re: smd_query: Talk to the database directly via SQL

#107 2009-05-23 10:01:22

Re: smd_query: Talk to the database directly via SQL

#108 2009-05-23 21:06:51

Re: smd_query: Talk to the database directly via SQL

Board footer