CODEWITHSUNDEEP
phpmysql
Steven
Steven

Reputation: 13975

MySQL IF Statement?

I have a big giant sql query where I select and format a bunch of rows for export into Excel. And I want to add one more role, pretty much if the rid (role id) EXISTS and IS 3 then set it to be Role 1 otherwise set it to be Role 2.

Mysql Query Currently

SELECT uc_orders.billing_first_name AS 'First Name', uc_orders.billing_last_name AS 'Last 
Name', users.name AS 'Username', uc_orders.billing_street1 AS 'Address', 
uc_orders.billing_city AS 'City', uc_zones.zone_name AS 'State',uc_orders.billing_postal_code
 AS 'Postal Code', ROUND(uc_orders.order_total,2) AS 'Order Total'  FROM uc_orders, users,
 uc_zones WHERE uc_orders.uid!='0' AND uc_orders.uid = users.uid AND uc_orders.billing_zone =
 uc_zones.zone_id AND uc_orders.modified BETWEEN '1271124575' AND '1274978899'

HOWEVER the user will not ALWAYS exist in the users_role database (it only exists if it is 3 or some other number, normally it will not exist). So if it does not exist, it would also return 'Role 2';

So

  IF users.uid EXISTS IN users_role.uid THEN
    IF users_role.uid EQUALS users.uid AND users_role.rid = 3
      THEN
        return 'Role 1'
      ELSE
        return 'Role 2';
  ELSE return 'Role 2';

How can I do this in MySQL? Or would PHP be the only way?

Upvotes: 1

Views: 1923

Answers (3)

RC.
RC.

Reputation: 28207

Use a CASE statement. CASE(rid) WHEN 3 THEN 'Role 1' ELSE 'Role 2' END;

Info on the CASE statement can be found here

    -- Kolinks SQL using a CASE statement
    SELECT
        `a`.`billing_first_name` as `First Name`,
        `a`.`billing_last_name` as `Last Name`,
        `b`.`name` as `Username`,
        `a`.`billing_street1` as `Address`,
        `a`.`billing_city` as `City`,
        `c`.`zone_name` as `State`,
        `a`.`billing_postal_code` as `Postal Code`,
        ROUND(`a`.`order_total`,2) as `Order Total`,
        CASE (`d`.`rid`) WHEN 3 THEN 'Role 1' ELSE 'Role 2' END; as `Role`
    FROM `us_orders` as `a`
    JOIN `users` as `b` ON `a`.`uid`=`b`.`uid`
    JOIN `uc_zones` as `c` ON `a`.`billing_zone`=`c`.`zone_id`
    LEFT JOIN `users_role` as `d` on `a`.`uid`=`d`.`uid`
    WHERE `a`.`modified` BETWEEN '1271124575' AND '1274978899'

Upvotes: 1

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324640

You should be using JOIN for multi-table queries. Also consider formatting your query and using aliases for easier reading.

SELECT
    `a`.`billing_first_name` as `First Name`,
    `a`.`billing_last_name` as `Last Name`,
    `b`.`name` as `Username`,
    `a`.`billing_street1` as `Address`,
    `a`.`billing_city` as `City`,
    `c`.`zone_name` as `State`,
    `a`.`billing_postal_code` as `Postal Code`,
    ROUND(`a`.`order_total`,2) as `Order Total`,
    IF(`d`.`rid` IS NOT NULL AND `d`.`rid`=3,'Role 1','Role 2') as `Role`
FROM `us_orders` as `a`
JOIN `users` as `b` ON `a`.`uid`=`b`.`uid`
JOIN `uc_zones` as `c` ON `a`.`billing_zone`=`c`.`zone_id`
LEFT JOIN `users_role` as `d` on `a`.`uid`=`d`.`uid`
WHERE `a`.`modified` BETWEEN '1271124575' AND '1274978899'

The part that answers your actual question is the line with the IF and the one with the LEFT JOIN.

Upvotes: 4

Surreal Dreams
Surreal Dreams

Reputation: 26380

Yes, there is an IF statement. There's also an IF() function. Here's the MySQL reference page for the IF statement:

http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

Upvotes: 1

Related Questions