How to Check if a Query Result is Empty and Handle it Correctly When Using mysqli_result

When using PHP to interact with a MySQL database, the mysqli extension is a common choice. After executing a query, you typically receive a mysqli_result object, but many developers overlook an important step: checking whether the query result is empty. This article will explain in detail how to check and handle the case when the query result is empty.

1. Basic Query Example

Let's first look at a simple query example:

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
<p>if ($mysqli->connect_errno) {<br>
die("Connection failed: " . $mysqli->connect_error);<br>
}</p>
<p>$sql = "SELECT * FROM users WHERE status = 'active'";<br>
$result = $mysqli->query($sql);</p>
<p>if (!$result) {<br>
die("Query failed: " . $mysqli->error);<br>
}</p>
<p>// Key part: check if there are any result rows<br>
if ($result->num_rows === 0) {<br>
echo "No active users found.";<br>
} else {<br>
while ($row = $result->fetch_assoc()) {<br>
echo "Username: " . htmlspecialchars($row['username']) . "<br>";<br>
}<br>
}</p>
<p>$result->free();<br>
$mysqli->close();<br>
?><br>

2. Key Points Explanation

1. Check if $result is false
This indicates that there was an error in the SQL query itself, such as a syntax error or a non-existent table. Use:

if (!$result) {
    // Query failed, handle the error
}

2. Check $result->num_rows
If the query was successful but no matching rows were found, num_rows will be 0. Use:

if ($result->num_rows === 0) {
    // No results, handle the empty case
}

3. Avoid using fetch_assoc() to check for empty results directly
Many developers use:

if ($row = $result->fetch_assoc()) { ... }

This approach will not enter the loop if there are no results, but it doesn't explicitly distinguish between "no results" and "rows exist but don't meet the criteria." It is recommended to explicitly check num_rows.

3. How to Handle the Empty Result Gracefully

In real-world projects, simply outputting "no results" is not enough. You can optimize based on the context, such as:

• Return a friendly message page;

• Log the event for later analysis;

• Redirect to another page, for example:

if ($result->num_rows === 0) {
    header("Location: https://m66.net/no-results.html");
    exit;
}