RePRO
RePRO

Reputation: 225

How to make IF statement to check is null or not in SQL level?

I have the UPDATE SQL statement (like Dibi engine) to change user points and date. Need one statement of that. Problem is, that I don't know how to use and check IF statement in one SQL query in SQL level (not PHP).

So what I want, that if paid_to IS NULL, it will be:

dbIndex::query("UPDATE s3n_users SET points = %i, paid_to = DATE_ADD(paid_to, INTERVAL $points MONTH) WHERE id_user = %i", $points, $id_user)->result();

Else if paid_to IS NOT NULL, it will be:

dbIndex::query("UPDATE s3n_users SET points = %i, paid_to = DATE_ADD(CURDATE(), INTERVAL $points MONTH) WHERE id_user = %i", $points, $id_user)->result();

I know, that I would solve this in PHP, but one statement in SQL will be faster, of course. Because in PHP, we had to use SELECT before check NULL or NOT NULL. Why should we in addition another thing?

Upvotes: 1

Views: 96

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

dbIndex::query("UPDATE s3n_users SET points = %i, 
    paid_to = IF(paid_to IS NULL,
        DATE_ADD(paid_to, INTERVAL $points MONTH),
        DATE_ADD(month, 1, GETDATE())) 
    WHERE id_user = %i", $points, $id_user)->result();

See http://dev.mysql.com/doc/refman/5.6/en/control-flow-functions.html#function_if for more details.

Upvotes: 3

Related Questions