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: [rjbathgate] WHERE CASE/IF

By Jason - June 15, 2010

Hi,

There is actually an easier way to create this.
Since we always want vehicle_category=2, we can use that in either case. Apart from that, we want to select any record that have either been sold in the past month, or not sold at all.

Try this query:

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

This should return the records you're looking for.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/