MySql – Get different field based on null – coalesce

Displaying different fields based on null. For example, after user logged into our site, we have to display user actions in our site menu where if a user has alias and name both we just have to show alias if he has no alias you have to show name.

If rs is result set then in PHP code will be

foreach($rs as $r)
{
   if($r['alias']=="")
   {
     echo $r['name'];
   }
   else
   {
     echo $r['alias'];
   }
}

But we can do a null check and different field value at the query level itself.

The function coalesce will give first not null of what we sent. i.e., coalesce(null,null,0,null,’a’) will just give 0.

selet coalesce(alias,name) as name  from profiles

With this method, PHP code will only be

foreach($rs as $r)
{
   echo $r['name'];
}

Although we can use switch statements, it is easier to do in query level as inbuilt functions are much more optimized towards performance