WHERE CASE/IF

3 posts by 2 authors in: Forums > CMS Builder
Last Post: June 15, 2010   (RSS)

By rjbathgate - June 14, 2010

Hey,

I have a where statement:

'where' => "vehicle_category = '2' AND sold_date > (NOW() - INTERVAL 1 MONTH)) ",

However, not all records have a value for sold_date, and therefore are not listed using the above.

I however want it to work the other way around, i.e. where sold_date is within 1 month, or there is no sold date.

So, I was trying to implement an IF into the WHERE using CASE, to only include the date criteria if sold_date is specified:

'where' => "CASE WHEN (@sold_date) THEN (vehicle_category = '2' AND sold_date > (NOW() - INTERVAL 1 MONTH))) ELSE vehicle_category = '2'",

But that errors.

Even a simplified version for testing errors:
'where' => "CASE WHEN (@sold_date) THEN vehicle_category = '2' ELSE vehicle_category = '1'",

Am I writing there WHERE incorrectly, or is it not supported in the 'where' => function... if the latter, is there an alternative short of creating a custom new array?

Thanks in advance!

Re: [Jason] WHERE CASE/IF

By rjbathgate - June 15, 2010

Perfect, I like simple solutions, thanks...

Didn't even consider that as didn't think I'd be able to specific OR is blank...

:)

Cheers Jason