Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 10,285
Website GitHub

Re: smd_query: Talk to the database directly via SQL

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 ;-)


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

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

eivl
Member
Registered: 2009-04-09
Posts: 20

Re: smd_query: Talk to the database directly via SQL

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)

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 10,285
Website GitHub

Re: smd_query: Talk to the database directly via SQL

eivl wrote:

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

Thank you very much! Looking forward to it.


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

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 10,285
Website GitHub

Re: smd_query: Talk to the database directly via SQL

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


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

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

M_i
Member
Registered: 2006-03-05
Posts: 122

Re: smd_query: Talk to the database directly via SQL

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)

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 10,285
Website GitHub

Re: smd_query: Talk to the database directly via SQL

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)


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

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

M_i
Member
Registered: 2006-03-05
Posts: 122

Re: smd_query: Talk to the database directly via SQL

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.

Offline

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

lous
Member
From: Germany
Registered: 2009-01-04
Posts: 57

Re: smd_query: Talk to the database directly via SQL

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

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 10,285
Website GitHub

Re: smd_query: Talk to the database directly via SQL

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?


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

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

lous
Member
From: Germany
Registered: 2009-01-04
Posts: 57

Re: smd_query: Talk to the database directly via SQL

Hey bloke it doesnt work :(

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 10,285
Website GitHub

Re: smd_query: Talk to the database directly via SQL

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 :-)


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

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

lous
Member
From: Germany
Registered: 2009-01-04
Posts: 57

Re: smd_query: Talk to the database directly via SQL

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?

Offline

Board footer

Powered by FluxBB