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?

Yes, You should use NULL values in sql tables when there is no data for that row/record/object. — Adam Wenger
– Adam Wenger, Commented Nov 20, 2011 at 23:40
Can you check to see that for the first pass $col1 has data and isn't actually NULL? — Adam Wenger
– Adam Wenger, Commented Nov 20, 2011 at 23:41
That would be nice in HALL OF SHAME! I'm calling the function with something($col1,$cok2) for almost 4 hours. — tuze
– tuze, Commented Nov 20, 2011 at 23:49

Alexey Gerasimov · Accepted Answer · 2011-11-20 23:39:50Z

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'

I really need some serious help while asking questions. I'm sorry, of course I'm passing values to my functions.
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.

Adam Wenger · Accepted Answer · 2011-11-20 23:47:21Z

3

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

edited Nov 20, 2011 at 23:47

answered Nov 20, 2011 at 23:31

Adam Wenger

17.7k8 gold badges54 silver badges65 bronze badges

3 Comments

tuze Over a year ago

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 Over a year ago

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 Over a year ago

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...

Andreas Wederbrand · Accepted Answer · 2011-11-20 23:37:38Z

1

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>;

answered Nov 20, 2011 at 23:37

Andreas Wederbrand

40.5k12 gold badges71 silver badges82 bronze badges

Collectives™ on Stack Overflow

Checking MySQL NULL values with PHP

3 Answers 3

2 Comments

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Linked

Related