filter listings by two categories + today's date forward

4 posts by 3 authors in: Forums > CMS Builder
Last Post: March 31, 2010   (RSS)

By Deborah - March 30, 2010

I'd like to show calendar listings from two categories, but only those listings that are for today or the next 365 days in the future.

I can show ALL listings from two categories such as:
'where' => " category LIKE '%\tMusic - Events\t%' or category LIKE '%\tArt - Events\t%' ",

And when I haveone category and add the date requirement, it works with this:
'where' => ' category LIKE "%\tFriends - Events\t%" AND (NOW() + INTERVAL 365 DAY) >= date AND date >= TIMESTAMP(CURDATE(), "00:00:00") ',

But, when combining the two, I end up with NO listings displayed, using the following code:
'where' => ' category LIKE "%\tMusic - Events\t%" OR category LIKE "%\tArt - Events\t%" AND (NOW() + INTERVAL 365 DAY) >= date AND date >= TIMESTAMP(CURDATE(), "00:00:00") ',

I'm hoping this is an obvious syntax error for someone here and would be very grateful for any advice.

Deborah

Re: [Deborah] filter listings by two categories + today's date forward

By Chris - March 30, 2010

Hi Deborah,

Well, you definitely need parenthesis around your OR condition:

'where' => ' (category LIKE "%\tMusic - Events\t%" OR category LIKE "%\tArt - Events\t%") AND (NOW() + INTERVAL 365 DAY) >= date AND date >= TIMESTAMP(CURDATE(), "00:00:00") ',

Does that help? Please let me know if you have any questions.
All the best,
Chris

Re: [Deborah] filter listings by two categories + today's date forward

By Dave - March 31, 2010

A tip for anyone with similar issues: MySQL ignores extra whitespace, so I often add extra spaces and line breaks to make it easier for me to read the code at a glance. Example:

'where' => '(category LIKE "%\tMusic - Events\t%" OR category LIKE "%\tArt - Events\t%")
AND (NOW() + INTERVAL 365 DAY) >= date
AND date >= TIMESTAMP(CURDATE(), "00:00:00") ',


And I often add extra parenthesis as well, as I've been bitten by this more than a few times myself! :)
Dave Edis - Senior Developer
interactivetools.com