How to filter articles by custom field's range of values?

hamato · 2009-02-25 19:38:31

Hi everyone,

I am trying to filter articles by custom field. I want to list 3 articles that have customfieldname value more then 5 (numeric).
Something like <txp:article_custom status="sticky" customfieldname=">5" />.

Since <txp:article_custom /> does not have a support for filtering this range of values (>5, more than 5) I tried to accomplish this with various plugins. The closest attempt was with smd_if plugin, and I wrote a same question there. Since a discussion became not necessarily related to that thread, I decided to start a new one to avoid spamming that one with offtopic.

Does anyone have advice how can this be done, or which plugin I should try? Maniqui helped me to get much closer to the solution, but I still can’t make it work. BTW, I think that glz_custom_fields can’t help me here.

Thanks

Last edited by hamato (2009-02-25 19:39:35)

redbot · 2009-02-25 19:52:56

Hi, have you tried with smd_query?

hamato · 2009-02-25 20:10:42

No, but I’ll definitely try it tonight cause it might be my only choice. I’ve avoided that plugin because I have only basic knowledge of SQL syntax… actually, almost none. :| Maybe it’s time for me to learn some…

Thanks for advice!

hamato · 2009-02-26 15:19:53

<txp:smd_query column="*" table="textpattern"
  where="status=5 AND custom_15='Wednesday' AND custom_11 > 5  ORDER BY custom_11 asc LIMIT 3 OFFSET 1" wraptag="ol" break="li" >
{Title}
<txp:else />
<p>Whatever...</p>
</txp:smd_query>

OK, this is what I got so far, and it’s working, but… Now I need to replace Wednesday and number 5 with textpattern’s variables, (or some small piece of PHP) and I’m getting errors because of " ' < > characters.

For example:

<txp:variable name="weekday" value="<txp:php>echo date('l');</txp:php>" />
<txp:variable name="hournow" value="<txp:php>echo date('H');</txp:php>" />
<txp:smd_query column="*" table="textpattern" where="status=5 AND custom_15='<txp:variable name="weekday" />' AND custom_11 > <txp:variable name="hournow" /> ORDER BY custom_11 asc LIMIT 3 OFFSET 1" wraptag="ol" break="li" >
{Title}
<txp:else />
<p>Whatever...</p>
</txp:smd_query>

I guess I made a mistake somewhere in the code, but I’m curious if it can be done this way at all.

I’m so frustrated and sick of this atm. I’ve spent 5 days trying to get this %$&* working and I still don’t know what to do…

redbot · 2009-02-26 15:51:04

Have you tried the other way around?

'status=5 AND custom_15="Wednesday" AND custom_11 > 5  ORDER BY custom_11 asc LIMIT 3 OFFSET 1'

or

 'status=5 AND custom_15=''Wednesday'' AND custom_11 > 5  ORDER BY custom_11 asc LIMIT 3 OFFSET 1'

(note the double single quote in the second example)

If this doesn’t work I suggest you to ask in the smd_query thread

hamato · 2009-02-26 20:33:50

Thanks for reply Redbot.

Sorry, I was wrong about quotes. I think I’ve finally discovered what the problem is. I don’t know how to solve it yet tho…

Problem is that <txp:variable /> stores everything inside value, so if there’s some PHP code inside – it doesn’t output the result, but the whole code.

In this case:

<txp:variable name="weekday" value="<txp:php>echo date('l');</txp:php>" />

<txp:smd_query debug="1" wraptag="ol" break="li" column='*' table='textpattern' where='
status=5 
AND custom_15="<txp:variable name="weekday" />" 
AND custom_11 > 15 
ORDER BY custom_11 asc 
LIMIT 10 
OFFSET 0' >
{Title}
</txp:smd_query>

query looks like this (I can see this code if I add debug="1" to smd_query):

select * from textpattern where 
status=5 
AND custom_15="<txp:php>echo date('l');</txp:php>" 
AND custom_11 > 15 
ORDER BY custom_11 asc 
LIMIT 10 
OFFSET 0

and I need:

select * from textpattern where 
status=5 
AND custom_15="Thursday" 
AND custom_11 > 15 
ORDER BY custom_11 asc 
LIMIT 10 
OFFSET 0

I guess I should ask in the smd_query thread, but if anyone have an idea how to solve this problem with <txp:variable/> feel free to post here.

Thanks in advance.

Bloke · 2009-02-26 20:47:21

hamato

Try:

<txp:variable name="weekday" value='<txp:php>echo date("l");</txp:php>' />

The single quotes will actually parse the contents of the variable and you should then be able to plug the variable into smd_query.

Incidentally, you don’t need to embed the entire <txp:variable /> call inside smd_query at all. I made it easy for you; just use the value directly by preceding it with a question mark, i.e.

<txp:smd_query debug="1" wraptag="ol" break="li" column='*' table='textpattern' where='
status=5 
AND custom_15="?weekday" 
AND custom_11 > 15 
ORDER BY custom_11 asc 
LIMIT 10 
OFFSET 0' >
{Title}
</txp:smd_query>

Last edited by Bloke (2009-02-26 20:48:49)

hamato · 2009-02-26 22:52:19

It’s working! Finally!!!

Thank you Bloke! Thank you Redbot! I love you both! :D

Now I see more possibilities with this incredible plugin, and I will use it much more in the future. Great work Bloke!

Textpattern CMS

Textpattern CMS support forum

#1 2009-02-25 19:38:31

How to filter articles by custom field's range of values?

#2 2009-02-25 19:52:56

Re: How to filter articles by custom field's range of values?

#3 2009-02-25 20:10:42

Re: How to filter articles by custom field's range of values?

#4 2009-02-26 15:19:53

Re: How to filter articles by custom field's range of values?

#5 2009-02-26 15:51:04

Re: How to filter articles by custom field's range of values?

#6 2009-02-26 20:33:50

Re: How to filter articles by custom field's range of values?

#7 2009-02-26 20:47:21

Re: How to filter articles by custom field's range of values?

#8 2009-02-26 22:52:19

Re: How to filter articles by custom field's range of values?

Board footer