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.
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.
<?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);
?>