using if statement in mysql query

Question

I would like to use if statement in sql query :

what I want :

if(tractions_delivery.send_date_id !=0 ){

           date_send_commodities.id  = tractions_delivery.send_date_id
}

my query :

        from

            tractions_delivery,user_address,province,city,date_send_commodities,users

        WHERE

        tractions_delivery.tr_id = $tr_id
        AND
        tractions_delivery.address_id = user_address.id
        AND
        user_address.province_id = province.id
        AND
        user_address.city_id      = city.id
        AND
        //not work
        (tractions_delivery.send_date_id IS NOT 0 date_send_commodities.id  = tractions_delivery.send_date_id)

        AND
        users.id = user_address.user_id

... AND CASE WHEN tractions_delivery.send_date_id != 0 THEN date_send_commodities.id = tractions_delivery.send_date_id ELSE 1=1 END AND users.id = user_address.user_id — iiro
– iiro, Commented Jan 18, 2016 at 9:05
@S.M_Emamian :Try this: IF(tractions_delivery.send_date_id <> 0, date_send_commodities.id, <some_other_column>) = tractions_delivery.send_date_id — Rohan Kushwaha
– Rohan Kushwaha, Commented Jan 18, 2016 at 9:25

iiro · Accepted Answer · 2016-01-18 10:09:17Z

1

You could use the CASE-statement

SELECT 
*
FROM
 tractions_delivery,
 user_address,
 province,
 city,
 date_send_commodities,users
WHERE
 tractions_delivery.tr_id = $tr_id AND
 tractions_delivery.address_id = user_address.id AND
 user_address.province_id = province.id AND
 user_address.city_id = city.id AND
 CASE WHEN tractions_delivery.send_date_id != 0 THEN date_send_commodities.id = tractions_delivery.send_date_id ELSE 1=1 END AND
 users.id = user_address.user_id

answered Jan 18, 2016 at 10:09

iiro

3,1381 gold badge22 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bdifferent · Accepted Answer · 2016-01-18 09:07:45Z

1

You can only use if statements in stored procedures or functions. If you just write a sql statement unfortunately you cannot use if statements around the query. But you can use logic in the query itself, e.g.:

SELECT CASE WHEN col1 = col2 THEN'col1 equals col2' else 'col1 doesnt equal col2' ELSE
FROM table1

So around doesnt work, but in the field list you can create CASE WHEN ELSE END logic.

answered Jan 18, 2016 at 9:07

bdifferent

7034 silver badges12 bronze badges

Comments

Dinesh Mandal · Accepted Answer · 2016-01-18 09:35:07Z

1

CASE or IF() operators can be of help.

Examples,

SELECT (CASE 1 WHEN 1 THEN 'One' WHEN 2 THEN 'Two' ELSE 'More' END) 'Result';

OR

SELECT IF(1=1, 'One', 'Two') 'Result';

These CASE and IF() operators can be used in the SELECT clause to conditionally interpret column values and return in the resultset.

Note: Do not confuse CASE operator here with 'CASE conditional syntax block' that ends with END CASE.

answered Jan 18, 2016 at 9:35

Dinesh Mandal

176 bronze badges

Collectives™ on Stack Overflow

using if statement in mysql query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related