Apr
13
Conditional statements inside MySQL queries
Filed Under MySQL, Programming
Often, when updating different values inside a MySQL table, extra selects, for example, might be needed. But that’s not always the case, because conditional statements can be used inside ordinary queries.
Let’s take a real world example: the table which holds informations about the timing in a test. So, the user is presented a javascript counter and upon submitting, the elapsed time is checked.
UPDATE $table_name SET end_time= IF(UNIX_TIMESTAMP(CURRENT_TIMESTAMP) - UNIX_TIMESTAMP(start_time) < $number_of_seconds, CURRENT_TIMESTAMP, '') WHERE timing_id='$timing_id'
The IF statement takes 3 parameters: the expression to be evaluated, the value if the evaluation returns true, the value if the evaluations returns false (in the above example, the value is blank - ”, which means the default field value will be inserted).
The next step would be to nest another IF. For example, instead of ” as the last parameter, something like
IF(userid > 1, '', CURRENT_TIMESTAMP)
meaning that if the userid=1 (the admin, let’s say), than bypass the constraint and still update the field with the CURRENT_TIMESTAMP.
The finished query would look like:
UPDATE $table_name SET end_time= IF(UNIX_TIMESTAMP(CURRENT_TIMESTAMP) - UNIX_TIMESTAMP(start_time) < $number_of_seconds, CURRENT_TIMESTAMP, IF(userid > 1, '', CURRENT_TIMESTAMP)) WHERE timing_id='$timing_id'
Another useful construct is CASE WHEN THEN.
A simple self-explanatory example:
UPDATE users, books SET users.book = CASE books.is_available WHEN 0 THEN 0 WHEN 1 THEN books.book_id ELSE 0 END WHERE users.book = books.book_id AND users.user_id = $uid;
Comments
5 Responses to “Conditional statements inside MySQL queries”
Leave a Reply
Hello,
Which MySQL version have you used for these code snippets please?
Hy,
5+ as I recall
Very interesting, and good to know information.
Do you have any information about performance? Is it more efficient to do this method, or perform this logic prior to the SQL Query?
Well, it depends very much on what you are doing. If it’s a site with high number of concurrent connections to the DB, than this will surely be faster(smaller overhead, fewer connections to the DB - trust me, it matters a lot!, fewer queries to parse). If not and large amounts of data are processed, it’s better to do it prior.
thanks, very useful info