Thursday, August 9, 2007

Control Flow Functions of MySQL

While doing web programming many times you fetch data from database and depending on the value fetched, the user is shown something or other.
In case of optional fields in table it is quite likely that the value may be NULL or something default that you have set.
Take for example and option field called product awards which lists all awards bagged by that product.

When you write query
SELECT pro_id, pro_awards FROM products

This will give info regarding all the awards bagged by products.
However since pro_awards is optional many records will contain value NULL.

To tackle this NULL value you check in php whether the fetched value is NULL, if yes you display "No awards bagged by this product".

You can also do this checking at database level to reduce some load on webserver.
And as Databases are configured at their optimum level the processing time is much faster than your php / any script.

This can be done this way:
SELECT pro_id, IF(pro_awards IS NULL,'No awards bagged by this product', pro_awards) as pro_awards
FROM products
Above was use of if-else construct in MySQL

You can also make use of Switch-case construct effectively.
SELECT pro_id, CASE WHEN pro_awards IS NULL THEN No awards bagged by this product' ELSE pro_awards END FROM products

Above examples are very simple to understand, I am sure you can come out with difficult situations and use these Control flow functions of MySQL effectively to reduce script execution time.


No comments: