使用PHP检查MySQL NULL值

问题描述：

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?

这就是我的表格的样子.. p>

  id  col1 col2 
 --------------- 
1 ax 
2 NULL y 
3 NULL z 
4 NULL t 
  code>  pre> 
 
  col1的默认值为 NULL  code>。 p> 
 
 
我想使用col1数据如果col1不为null，否则使用col2数据。   p> 
 
 

  function something（$ col1，$ col2）
 {
 if if（is_null（$ col1）== true）
 $ var = $ col2 
 else \  n $ var = $ col1 
 
返回$ var; 
} 
 
function something2（$ col1，$ col2）
 {
 if if（$ col1 === NULL）
 $ var = $ col2  
其他
 $ var = $ col1 
 
返回$ var; 
} 
  code>  pre> 
 
 这是我的问题。 这两个函数都返回$ col2值。 但正如您在第一行中看到的那样，col2列不为null。 我究竟做错了什么？  更新：寻找使用PHP的解决方案，我需要col1和col2值。 strong>  p> 
 
 
我还想了解，使用NULL值是否是最佳实践 这个例子？ p> 
  div>

答

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'

答

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

答

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

相关推荐