How to check if a SQL query result is empty or not empty in PHP

When executing a SQL query to a database, the rows and columns of a table can be returned as the execution result. An empty value can also be returned if no data matching the query is found in the table.

The usual algorithm for processing the received response from the database is to fetch the SQL query result row by row and process each row as an array or object.

But for some situations this is redundant – sometimes we only need to know if something was found in the database or an empty result was returned.

To determine whether the database query result is empty, you can use the mysqli_num_rows() function. This function counts the number of rows in the returned result. If the number of rows is 0, then the result is empty. If the number of rows is greater than zero, then the result is not empty.

An example of using the mysqli_num_rows() function – this script checks if the table has a column with the specified name. If the SQL query result is empty, then the column is missing. If the number of rows in the SQL query results is greater than zero, then the column exists:

<?php

$db_user = 'root';
$db_password = '';
$db_name = 'TestDB';
$mysqli = new mysqli("localhost", $db_user, $db_password, $db_name);
if ($mysqli->connect_errno) 
{
	printf("Не удалось подключиться: %s\n", $mysqli->connect_error);
	exit();
}

$query = "SHOW COLUMNS FROM TestTABLE WHERE field = 'test_column';";
$result = $mysqli->query($query);
if (mysqli_num_rows($result)==0)
{ 
	echo 'The column does not exist' . PHP_EOL;
}
else
{
	echo 'The column exists' . PHP_EOL;
}

Note: This method may not work if the SQL query failed. That is, you need to separately check whether the database query failed. If you do not check the SQL query result for an error, then the returned data may contain information about the error and the number of rows will not be zero. This may disrupt the logic of your script.

Note 2: In older sources, you may see a mention of the mysql_num_rows function. This function is currently deprecated, and the mysqli_num_rows() function should be used instead.