Handling NuLL Example
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 = '[email protected]'; $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.
- SELECT * FROM GrowthMindSets
- WHERE EmailAddress IS NOT NULL;
MySQL SET NULL Values in UPDATE Statement
By using the assignment operator (“=”), you can set any value of a column to NULL by using the Update Statement.
Example
- UPDATE growthmindsets
- SET EmailAddress = NULL
- WHERE GMS_ID = 4 and GMS_ID = 5
By using the following query, you can check the output.
- SELECT * FROM GROWTHMINDSETS
- WHERE EmailAddress IS NULL AND PhoneNo IS NULL
NULL VALUES RELATED FUNCTIONS IN MYSQL
1) In MYSQL there are some functions intended specifically for use with NULL values which include ISNULL () and IFNULL (). ISNULL () is true if its argument is NULL and false otherwise.
Example
- SELECT ISNULL(NULL), ISNULL(0), ISNULL(1);
2) IFNULL() takes two arguments. If the first argument is not NULL, that argument is returned; otherwise, the function returns its second argument.
Example 1
- SELECT IFNULL(NULL,‘a’), IFNULL(0,‘b’);
Example 2
In this example, I will show you a query that returns the empty string (‘’) if its value is NULL.
- SELECT First_Name, Last_Name, Description, IFNULL(EmailAddress, ”), IFNULL(PhoneNo, ”) FROM GROWTHMINDSETS;
3) Other functions handle NULL values in various ways, so you have to know how a given function behaves. In many cases, passing a NULL value to a function results in a NULL return value.
Example
Any NULL argument passed to CONCAT() causes it to return NULL:
- SELECT CONCAT(‘a’,‘b’), CONCAT(‘a’,NULL,‘b’);
