Checking MySQL NULL values with PHP

Question

This is how my table looks like..

id col1 col2  
---------------
1  a     x
2  NULL  y
3  NULL  z
4  NULL  t

col1 has a default value of NULL.

I want to use col1 data If col1 is not null, otherwise use col2 data.

function something($col1,$col2)
{
   if(is_null($col1) == true)
      $var = $col2
   else
      $var = $col1

   return $var;
}

function something2($col1,$col2)
{
   if($col1 === NULL)
      $var = $col2
   else
      $var = $col1

   return $var;
}

Here is my problem. Both of these functions returns $col2 values. But as you can see in first row, col2 column is not null. What am I doing wrong? UPDATE: Looking for a solution with PHP and I need both col1 and col2 values.

Also I want to learn, Does using NULL values is the best practice for this example?

Solution 1

I see a slew of problems in your question:

I want to use col1 data If col2 is not null, If It's I will use col2 data.

I assume you mean you want to use col2 data if col1 IS null otherwise use col1. In that case you have issues in your php. Not sure if you provided sample code or not but you're not passing any variables to the function nor declaring them as global inside the func.

function something($col1, $col2){

  if(is_null($col1) == true)
        $var = $col2;
  else
        $var = $col1;

  return $var;
}

function something2($col1, $col2){

  if($col1 === NULL)
        $var = $col2;
  else
        $var = $col1;

  return $var;
}

echo something('a','x');
echo something2('a','x');

This gives you 'a' in both cases

echo something(NULL,'b');
echo something2(NULL,'b');

This gives you 'b'

Solution 2

You should look at using COALESCE() in your sql query, only bring back the data you want to display. This would prevent you from needing this function/logic at all.

SELECT Id, COALESCE(col1, col2)
FROM yourTable

Also, for the PHP, you could consider changing if(is_null($col1) == true)

and using if(is_null($col1)) instead. It is smaller, more concise, and eliminates issues with how many = signs to use, and casing of True

Updating answer to include option proposed by Andreas:

SELECT Id, col1, col2
   , IFNULL(col1, col2) AS NotNullColumn
FROM yourTable

Solution 3

Check out IFNULL, it will give you col1 if that isn't null. Else it will give you col2.

SELECT id, IFNULL(col1, col2) FROM <table>;

Comments

[tuze]

Thanks for your answer and I know about COALESCE function. I need both col1 and col2 columns. So, looking for a solution with PHP.

[Andreas Wederbrand]

Then SELECT col1, col2, IFNULL(col1, col2) as theOneThatIsntNull FROM <table> could help you. Put logic where it belongs, in this case in the database.

[tuze]

I really need some serious help while asking questions. I'm sorry, of course I'm passing values to my functions.

[Alexey Gerasimov]

what's not working then? I just ran the code I put above and it works just fine. If col1 is not null , take col1, else take col2.

[tuze]

I really should jump off a bridge ! I'm calling the function with something($col1,$cok2) for almost 4 hours. Sorry for taking your time...