MySQL returning an empty field: CONCAT(nonEmpty1,empty2,nonEmpty3) = NULL

Question

I have PHP 5 code accessing a MyISAM table on MySQL 5 server. The query looks like this:

SELECT CONCAT(fName1,' ',mName2,' ',lName3) AS userName 
    FROM users 
    WHERE level > 10

When there's no mName filled in, I am expecting output like "fname lname" , but I'm getting "" (empty string) instead (the number of rows returned is correct). Where am I making a mistake?

PHP code:

<?php
$result = mysql_query($the_above_query);
while ($result_row = mysql_fetch_assoc($result)) {
    // do stuff with the name
    // except I'm getting empty strings in $result_row['userName']
}

Relevant part of table structure:

CREATE TABLE users {
    /* -snip- */ 
    `fName1` varchar(50) default NULL,      
    `mName2` varchar(50) default NULL,      
    `lName3` varchar(50) default NULL,      
    `level` int(11) default 0,      
    /* -snip- */ 
} ENGINE=MyISAM DEFAULT CHARSET=utf8;

(also, is this way (column concatenation in MySQL) a good idea, or should I fetch the columns to PHP and join them there?)

---

Turns out that I was getting back a NULL; PHP treats a returned NULL and empty string("") similarly, you'd have to compare with === to see the difference.

Answer 1

This was the solution I came up with which included Keeper and Ersatz answer. System would not allow me to vote you guys up though :(

CONCAT_WS(IFNULL(ts_usr_nameDetails.first_name,''),' ',IFNULL(ts_usr_lib_connectionNameDetails.first_name,'')) AS composerName

This allowed for some amazing results

Answer 2

In MySQL concatenating any string to a NULL value results in NULL. You have to check for NULL before concatenate using IFNULL:

SELECT CONCAT(IFNULL(fName1,''),' ',IFNULL(mName2,''),' ',IFNULL(lName3,'')) AS userName 
FROM users 
WHERE level > 10