How to Find Null Values in a MySQL Table Using PHP

In this article, we show how to find if there are any null values in a MySQL table using PHP.

Maybe you want to delete rows that have null values or insert values into rows that are null.

So this can have several application.

To do this in MySQL, you check to see whether a column is NULL. We show this below.

SELECT * FROM table_name WHERE column_name is NULL;

So the above is the MySQL code to select a table, named table_name, and check to see if column1 is NULL.

We'll now show how to tie this in with PHP.

PHP Code

The PHP code to return the rows of a table in any random order is shown below.

<?php $user = "user"; $password = "password"; $host = "host"; $database= "database"; $connection= mysql_connect ($host, $user, $password); if (!$connection) { die ('Could not connect:' . mysql_error()); } mysql_select_db($database, $connection); $result= mysql_query( "SELECT * FROM table_name WHERE column_name is NULL" ) or die("SELECT Error: ".mysql_error()); //Retrieves and outputs the null rows while($row = mysql_fetch_array($result)) { $name= $row['name']; echo $name . "<br>"; } ?>

So the following code gets the data required to make a connection to a database and actually selects a database.

We then create a variable named $result that selects the table, table_name, and checks the column, column_name, to see if it is NULL.

So all you have to do is select a table and check to see if a particular column is NULL.

The next block of code, consisting of a while loop, loops through all the rows and retrieves those rows which contains a NULL value for the column_name column. One of the columns is name. So we echo out the name that that has a NULL value for column_name.

Example

So now we show an actual example of a MySQL table and we will echo out the rows that are NULL.

Below is a MySQL table representing a customer information, customers' names and emails.

I use the same PHP code as shown above, only specific to this table. The output of this is shown below.

Actual PHP Output