I was wondering if this is possible from mysql.
SELECT username FROM users_table
If username is NULL I would like to display something like "Unknown" I can do it on php comfortable with it just wondering if this something worth looking into :)
Add a comment
|
3 Answers
SELECT IFNULL(username, 'Unknown') FROM users_table
OR
SELECT COALESCE(username, 'Unknown') FROM users_table
4 Comments
Val
nice one :) thnx m8 question, using this method instead of the php version which one do you think is faster?... its just a stupid question but worth knowing :)
ajreal
Both should be identical,
IFNULL are mysql specifically, while COALESCE is a common string function for other sql as well. (COALESCE might slightly slower as it accept multiple arguments), but I think the difference is too small to mention
Álvaro González
@Val If you use PHP you are able to handle blank users differently (e.g., assign a CSS class to display them in italics).
Val
you can do it by adding username value on the class and just do the same
class="<?php echo $username=='unknown'?'italic':'normal'; ?>"You can use IF function.
IF(condition, if_true, if_false)
So, your query is:
SELECT IF(NULL(username), "Unknown", username) FROM users_table
Read more: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if
1 Comment
Piskvor left the building
Did you mean
IF(ISNULL(...? I can't seem to find a NULL() function in the reference...Last but not least, CASE:
SELECT
CASE
WHEN username IS NULL THEN '(Unknown)'
ELSE username
END AS username
FROM users_table
I like it because it's easy to generalise when you have complex expressions (you can add as many WHEN as you want) and it works in many DBMS.
(In this case I'd use COALESCE from @ajreal's answer.)
