Detecting MySQL NULL in PHP

PHP
1

I have this function, which takes some SQL and returns an HTML table:

<?php

function table_sql_result($sql) {

   $str = "<table>";

   $result = mysql_query($sql);
   while ($row = mysql_fetch_assoc($result)) {
      if (!$keys) {
         $keys = array_keys($row);
         $str .= "<tr>";
         foreach ($keys as $key) {
            $str .= "<th>$key</th>";
         }
         $str .= "</tr>";
      }
      $str .= "<tr>";
      foreach ($row as $field) {
         switch ($field) {
         case NULL: $field = "NULL"; break; // This is catching empty strings, too!
         case "": $field = "&nbsp;"; break;
         default: $field = htmlentities($field); break;
         }

         $str .= "<td>$field</td>";
      }
      $str .= "</tr>";
   }

   return $str."</table>";

}

?>

Here’s the problem: I’m getting “NULL” for every empty value in the result. Considering the fact that PHP NULL is different than MySQL NULL, I’d like PHP to print “NULL” when it sees a field containing MySQL NULL, and " " when it sees a field with an empty string (“”). How would I do this?

My problem lies in that I can’t figure out how to detect MySQL NULL reliably. is_null() isn’t working.

2

the problem isn’t mysql, or even is_null(). it’s your case statement. you’re trying to, but can’t do this:

switch ($x) {
  NULL:
    //do something
}

which translates to this:

if ($x == NULL) {
  // do something
}

and if you read the editor’s note at the very bottom of the php manual page for is_null() you’ll see why.

3

instead detecting NULL values in php try to detect them im MySQL:


select if( field IS NULL,'custom_null_text',field)as field from table;

this works like this:
if field is NULL you can write some custom_text …or just plain ‘NULL’ or something else, and parse it later in PHP.

4

That would be ideal, but it doesn’t adapt well to my function (see first post).

5

So there is no way of telling the difference between a NULL field value and an empty string once you’ve queried the database into a PHP result?

6

I think this is already answered in longneck’s post. Just read it again. :wink:

7

…which also offers a solution to your problem.

8 
if ($x === NULL) {
  // do something
}

Is this the solution?

Brandon

9

Yes, it was. Thanks for the suggestion.