English
English
When using the MySQLi extension for database operations in PHP, we often need to use mysqli_stmt::free_result() to release the result set after execution, preventing memory leaks. However, in practical development, you may encounter the "no result set" error when calling mysqli_stmt::free_result. This article will analyze the common causes of this issue and its solutions in detail.
mysqli_stmt::free_result() is a method in the MySQLi extension used to free a query result set. After executing a SELECT query, if the result set is no longer needed, calling this method releases the memory and reduces resource consumption.
For example:
$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $userId);
$stmt->execute();
$stmt->store_result(); // Store the result
// Data processing can occur here...
$stmt->free_result(); // Free the result set
The "no result set" error typically occurs when calling free_result() while no query has been executed, or the query has not returned a result set. Specifically, this error means that the free_result() method is attempting to release a result set that doesn't exist.
Query Type Mismatch:
If a non-SELECT type query is executed (such as INSERT, UPDATE, DELETE, etc.), no result set is generated. Therefore, calling free_result() will result in this error.
Solution:
Ensure that free_result() is only called after a SELECT query.
$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
$stmt->execute();
// Do not call free_result() since no result set is returned
store_result() or bind_result() Not Called:
After executing a SELECT query, a result set is only generated if either store_result() or bind_result() is called. If free_result() is called without generating a result set, an error will occur.
Solution:
Before calling free_result(), ensure that either store_result() or bind_result() has been called.
$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $userId);
$stmt->execute();
$stmt->store_result(); // Ensure the result set is stored
$stmt->free_result(); // Now you can safely call it
Query Execution Failed:
If an SQL query fails to execute for any reason (such as syntax errors or connection issues), no result set is returned, and calling free_result() will result in an error.
Solution:
Ensure that the SQL query has successfully executed before calling free_result(). You can avoid this issue by checking whether the query was executed successfully.
$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
if ($stmt === false) {
die('MySQL prepare failed: ' . $mysqli->error);
}
$stmt->bind_param("i", $userId);
$stmt->execute();
if ($stmt->affected_rows > 0) {
$stmt->store_result();
$stmt->free_result();
}
Statement Closed Too Early:
If the statement is closed ($stmt->close()) before the query is executed and the result stored, the result set will not be accessible, leading to an error when calling free_result().
Solution:
Ensure that the statement is not closed until all operations, including result processing, are complete.
$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $userId);
$stmt->execute();
$stmt->store_result(); // Results must be stored first
$stmt->free_result(); // Then you can free the result
$stmt->close(); // Close the statement last
Query Returns an Empty Result Set:
If a SELECT query returns no results (for example, due to unmatched query conditions), calling free_result() typically does not trigger an error, though some configurations or environments may exhibit inconsistent behavior. While this isn't a direct cause of the error, it may sometimes lead to unexpected issues.
Solution:
Before calling free_result(), check whether the query returned any data.
$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $userId);
$stmt->execute();
$stmt->store_result();
<p>if ($stmt->num_rows > 0) {<br>
// There are results<br>
$stmt->free_result();<br>
} else {<br>
echo "No results found";<br>
}<br>
The "no result set" error in mysqli_stmt::free_result typically occurs when no result set is returned by the query or when free_result() is called without properly calling store_result() or bind_result(). To avoid this error, developers should take note of the following:
Ensure that free_result() is called only after a SELECT query.
Always call store_result() or bind_result() before calling free_result().
Check if the SQL query was executed successfully.
Complete all operations before closing the statement.
By following these methods and tips, you can effectively avoid this error and ensure smooth database operations.
