SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Not yet perfect mattalexx's Avatar
    Join Date
    Oct 2005
    Location
    Taos, NM, US
    Posts
    441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Detecting MySQL NULL in PHP

    I have this function, which takes some SQL and returns an HTML table:
    PHP Code:
    <?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 "&nbsp;" 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.
    Matt Alexander
    Alexander Site Design

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    the problem isn't mysql, or even is_null(). it's your case statement. you're trying to, but can't do this:
    PHP Code:
    switch ($x) {
      
    NULL:
        
    //do something

    which translates to this:
    PHP Code:
    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. #3
    SitePoint Member
    Join Date
    Mar 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    instead detecting NULL values in php try to detect them im MySQL:

    Code:
    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.
    my little Blog

  4. #4
    Not yet perfect mattalexx's Avatar
    Join Date
    Oct 2005
    Location
    Taos, NM, US
    Posts
    441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kodi
    instead detecting NULL values in php try to detect them im MySQL:

    Code:
    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.
    That would be ideal, but it doesn't adapt well to my function (see first post).
    Matt Alexander
    Alexander Site Design

  5. #5
    Not yet perfect mattalexx's Avatar
    Join Date
    Oct 2005
    Location
    Taos, NM, US
    Posts
    441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Matt Alexander
    Alexander Site Design

  6. #6
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think this is already answered in longneck's post. Just read it again.

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by myself
    and if you read the editor's note at the very bottom of the php manual page for is_null() you'll see why.
    ...which also offers a solution to your problem.

  8. #8
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    466
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    if ($x === NULL) { 
      
    // do something 

    Is this the solution?

    Brandon
    Home Recording Forum -
    Make 60% Commission Pushing my new mega system
    Killer Home Recording

  9. #9
    Not yet perfect mattalexx's Avatar
    Join Date
    Oct 2005
    Location
    Taos, NM, US
    Posts
    441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by brandondrury
    PHP Code:
    if ($x === NULL) { 
      
    // do something 

    Is this the solution?

    Brandon
    Yes, it was. Thanks for the suggestion.
    Matt Alexander
    Alexander Site Design


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •