Determine if all values in all rows of a query result set are null

Question

I have values pulled from a MySQL database where NULL is in the context of MySQL, if that makes a difference.

I need a way to know if all the values are NULL. What would be the most efficient and effective way of determining if ALL values of an array (as well as the values of the arrays within that array) are NULL?

So basically, search array, if all values are NULL, $flag = true

Answer 1

If i remember well db null fields become empty strings in php so you can use a function like this:

function isEmptyArray($array){
   foreach($array as $val)
      if((is_array($val) && !isEmptyArray($val))||(!is_array($val) && $val!="")) return false;
   return true;
}

Answer 2

Depends on what would you use that flag for, but I think the best would be to query the database so it retrieves only non null values, like

select col1,col2,col3 from table where col1 is not null and col2 is not null 
and col3 is not null

That said, a simple way to do something like that in PHP would be

//array_filter filters values that evaulate to fals
$result = array_filter($input); 
if (count($result) == 0) { $flag = true; }

This will fail for multidimensional arrays and if zeroes or other values that get automatically converted to false are valid values in your array, if this is the case you have to build a recursive callback function and pass it as a second parameter to array_filter.

Answer 3

You may want to look at the array_filter() function, by default it strips out values that equate to false (this includes NULL).

I'm not sure if it'll work as is for mutli-dimensional arrays, but it has the option to use call back functions which would easily allow you to handle that.

Long story short, if you run array_filter on the array and you get an empty array returned then all your values are Null or equal to false - if you need to differentiate between these values in a strict sense, then you should use a call back.

Answer 4

Can be done recursively:

arrayIsFullyNull ($arr)
{
   if (is_array($arr))
      foreach ($arr as $val)
         if (!arrayIsFullyNull($val))
            return false;
   else if (!is_null($arr))
       return false;
   else
      return false; // it's not null, and not array => value

   return true; 
}

Answer 5

You could use a RecursiveArrayIterator to walk over all the values (leaves of the tree of of nested arrays).

Untested code:

function isNullOrNestedArrayOfNulls($array)
{
  if (is_null($array)
  {
    return true;
  }
  else if (!is_array($array))
  {
    return false;
  }

  $allNull = true;
  $iter = new RecursiveIteratorIterator(new RecursiveArrayIterator($array),
                                        RecursiveIteratorIterator::LEAVES_ONLY);

  while ($iter->valid())
  {
    if (!is_null($iter->current())
    {
      $allNull = false;
      break;
    }
  }

  return $allNull;
}

This would have the advantage of avoiding a recursive function (which have the reputation of being rather inefficient in PHP).