CODEWITHSUNDEEP
mysql
Loren Ramly
Loren Ramly

Reputation: 1111

Mysql: How to query with if else condition inside if

I'm getting trouble to query my data. I do not know where to start with query if else condition inside if.

Problem is: How to query if its type = cpm and end_date < now() then change result value type to before, and if its type = cpm and end_date > now() then change result value type to after. I have a data in table, result as below :

enter image description here

And I want to get result as below :

Array(
  [0] => Array
        (
            [id] => 1
            [type] => free
            [end_date] => 2013-06-20
        )
  [1] => Array
        (
            [id] => 4
            [type] => after
            [end_date] => 2013-08-29
        )
  [2] => Array
        (
            [id] => 5
            [type] => before
            [end_date] => 2013-06-20
        )
)

Thanks in advance.

Upvotes: 2

Views: 7136

Answers (3)

HybrisHelp
HybrisHelp

Reputation: 5810

You can use WHEN .. CASE for this .

Here is Link for your solution SQLfiddle

Upvotes: 2

Sean
Sean

Reputation: 12433

You could use MySQL CASE - http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#operator_case

CASE
WHEN `type` = 'cpm' AND `end_date` < NOW() THEN 'before'
WHEN `type` = 'cpm' AND `end_date` > NOW() THEN 'after'
ELSE `type`
END as `type`

Upvotes: 2

vee
vee

Reputation: 38645

Something like this should work:

select 
  id, 
  case 
    when (type = 'cpm' and end_date < now()) then 'before' 
    when (type = 'cpm' and end_date > now()) then 'after' 
    else type
  end as type, 
  end_date
from my_table;

Upvotes: 7

Related Questions