- MySQLi Tutorial
- MySQLi - Home
- MySQLi - Introduction
- MySQLi - PHP Syntax
- MySQLi - Connection
- MySQLi - Create Database
- MySQLi - Drop Database
- MySQLi - Select Database
- MySQLi - Create Tables
- MySQLi - Drop Tables
- MySQLi - Insert Query
- MySQLi - Select Query
- MySQLi - Where Clause
- MySQLi - Update Query
- MySQLi - Delete Query
- MySQLi - Like Clause
- MySQLi - Sorting Results
- MySQLi - Using Joins
- MySQLi - Handling NULL Values
- Obtaining & Using MySQLi Metadata
- MySQL
- MySQL - Installation
- MySQL - Administration
- MySQL - Data Types
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQLi Useful Resources
- MySQLi - Useful Functions
- MySQLi - Quick Guide
- MySQLi - Useful Resources
- MySQLi - Discussion
MySQLi - Handling 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.
Using NULL values at the Command Prompt
Assume that there is a table called tcount_tbl in the TUTORIALS database and it contains two columns namely tutorial_author and tutorial_count, where a NULL tutorial_count indicates that the value is unknown.
Example
Try the following examples −
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> create table tcount_tbl → ( → tutorial_author varchar(40) NOT NULL, → tutorial_count INT → ); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO tcount_tbl → (tutorial_author, tutorial_count) values ('mahran', 20); mysql> INSERT INTO tcount_tbl → (tutorial_author, tutorial_count) values ('mahnaz', NULL); mysql> INSERT INTO tcount_tbl → (tutorial_author, tutorial_count) values ('Jen', NULL); mysql> INSERT INTO tcount_tbl → (tutorial_author, tutorial_count) values ('Gill', 20); mysql> SELECT * from tcount_tbl; +-----------------+----------------+ | tutorial_author | tutorial_count | +-----------------+----------------+ | mahran | 20 | | mahnaz | NULL | | Jen | NULL | | Gill | 20 | +-----------------+----------------+ 4 rows in set (0.00 sec) mysql>
You can see that = and != do not work with NULL values as follows −
mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL; Empty set (0.00 sec) mysql> SELECT * FROM tcount_tbl WHERE tutorial_count != NULL; Empty set (0.01 sec)
To find the records where the tutorial_count column is or is not NULL, the queries should be written as shown in the following program.
mysql> SELECT * FROM tcount_tbl → WHERE tutorial_count IS NULL; +-----------------+----------------+ | tutorial_author | tutorial_count | +-----------------+----------------+ | mahnaz | NULL | | Jen | NULL | +-----------------+----------------+ 2 rows in set (0.00 sec) mysql> SELECT * from tcount_tbl → WHERE tutorial_count IS NOT NULL; +-----------------+----------------+ | tutorial_author | tutorial_count | +-----------------+----------------+ | mahran | 20 | | Gill | 20 | +-----------------+----------------+ 2 rows in set (0.00 sec)
Handling NULL Values in a PHP Script
You can use the if...else condition to prepare a query based on the NULL value.
The following example takes the tutorial_count from outside and then compares it with the value available in the table.
Example
Copy and paste the following example as mysql_example.php −
<html> <head> <title>Handling NULL</title> </head> <body> <?php $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'root@123'; $dbname = 'TUTORIALS'; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); $tutorial_count = null; if($mysqli→connect_errno ) { printf("Connect failed: %s<br />", $mysqli→connect_error); exit(); } printf('Connected successfully.<br />'); 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 NULL'; } $result = $mysqli→query($sql); if ($result→num_rows > 0) { while($row = $result→fetch_assoc()) { printf("Author: %s, Count: %d <br />", $row["tutorial_author"], $row["tutorial_count"]); } } else { printf('No record found.<br />'); } $mysqli→close(); ?> </body> </html>
Output
Access the mysql_example.php deployed on apache web server and verify the output.
Connected successfully. No record found.