We have seen SQL SELECT order alongside WHERE statement to bring information from MySQLi table, yet when we attempt to give a condition, which contrast field or section an incentive with NULL, it doesn't work appropriately.
To deal with such circumstance MySQLi gives three administrators
- IS NULL − administrator returns valid if section esteem is NULL.
- Isn't NULL − administrator returns valid if segment esteem isn't NULL.
- <=> − administrator analyzes values, which (in contrast to the = administrator) is genuine in any event, for two NULL qualities.
Conditions including NULL are uncommon. You can't utilize = NULL or != NULL to search for NULL qualities in segments. Such correlations consistently come up short since it's difficult to tell whether they are valid. Indeed, even NULL = NULL falls flat.
To search for segments that are or are not NULL, use IS NULL or IS NOT NULL.
Using NULL values at Command Prompt
Assume a table tutorials_bks in TUTORIALS information base and it contains two segments id and name, where a NULL name demonstrates that the worth is obscure −
Example
Evaluate the accompanying models −
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> INSERT INTO tutorials_bks (book) values('');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO tutorials_bks (book) values('');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO tutorials_bks (book) values('');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO tutorials_bks (book) values('NULL');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO tutorials_bks (book) values('NULL');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO tutorials_bks (book) values('NULL');
Query OK, 1 row affected (0.08 sec)
+----+---------+
| id | book |
+----+---------+
| 1 | java |
| 2 | java |
| 3 | html |
| 4 | c++ |
| 5 | Android |
| 6 | |
| 7 | |
| 8 | |
| 9 | NULL |
| 10 | NULL |
| 11 | NULL |
+----+---------+
11 rows in set (0.00 sec)
mysql>
To discover records where the name section is or isn't NULL, the inquiries ought to be composed this way −
mysql> SELECT * FROM tutorials_bks WHERE book = "null";
+----+------+
| id | book |
+----+------+
| 9 | NULL |
| 10 | NULL |
| 11 | NULL |
+----+------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM tutorials_bks WHERE book ! = "null";
+----+---------+
| id | book |
+----+---------+
| 1 | java |
| 2 | java |
| 3 | html |
| 4 | c++ |
| 5 | Android |
| 6 | |
| 7 | |
| 8 | |
+----+---------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM tutorials_bks WHERE book = "";
+----+------+
| id | book |
+----+------+
| 6 | |
| 7 | |
| 8 | |
+----+------+
3 rows in set (0.01 sec)
Handling NULL Values in PHP Script
You can utilize if...else condition to set up an inquiry dependent on NULL worth.
Example
Following model takes tutorial_count from outside and afterward contrasts it and the worth accessible in the table.
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_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';
}
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
echo "Author:{$row['tutorial_author']} <br> ".
"Count: {$row['tutorial_count']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>