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;
Save and share: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • bodytext
  • Sphinn
  • del.icio.us
  • Mixx
  • Google
  • StumbleUpon
  • Technorati
  • YahooMyWeb

Comments

4 Responses to “Conditional statements inside MySQL queries”

  1. Nick on May 27th, 2008 4:22 pm

    Hello,

    Which MySQL version have you used for these code snippets please?

  2. Bogdan on May 27th, 2008 5:19 pm

    Hy,
    5+ as I recall

  3. Jimmy on May 27th, 2008 7:11 pm

    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?

  4. Bogdan on May 28th, 2008 1:28 am

    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.

Leave a Reply




Advertisements