Null Values

MySQL - Handling MySQL NULL Values

We have seen the SQL SELECT command along with the WHERE clause to fetch data from a MySQL table, but when we try to give a condition, which compares the field or the column value to NULL, it does not work properly.

To handle such a situation, MySQL provides three operators −

  • IS NULL − This operator returns true, if the column value is NULL.

  • IS NOT NULL − This operator returns true, if the column value is not NULL.

  • <=> − This operator compares values, which (unlike the = operator) is true even for two NULL values.

The conditions involving NULL are special. You cannot use = NULL or != NULL to look for NULL values in columns. Such comparisons always fail because it is impossible to tell whether they are true or not. Sometimes, even NULL = NULL fails.

To look for columns that are or are not NULL, use IS NULL or IS NOT NULL.

Handling NULL Values in a PHP Script

You can use the if...else condition to prepare a query based on the NULL value.

Example

The following example takes the tutorial_count from outside and then compares it with the value available in the table.

<?php
   $dbhost = 'remotemysql.com:3036';
   $dbuser = 'your_username;
   $dbpass = 'your_password';
   $conn = mysqli_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die('Could not connect: ' . mysqli_error());
   }

   if( isset($tutorial_count )) {
      $sql = 'SELECT tutorial_author, tutorial_count
         FROM  tcount_tbl
         WHERE tutorial_count = $tutorial_count';
   } else {
      $sql = 'SELECT tutorial_author, tutorial_count
         FROM  tcount_tbl
         WHERE tutorial_count IS $tutorial_count';
   }

   mysqli_select_db('TUTORIALS');
   $retval = mysqli_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysqli_error());
   }

   while($row = mysqli_fetch_array($retval, mysqli_ASSOC)) {
      echo "Author:{$row['tutorial_author']}  <br> ".
         "Count: {$row['tutorial_count']} <br> ".
         "--------------------------------<br>";
   } 
   echo "Fetched data successfully\n";
   mysqli_close($conn);
?>