English
English
When interacting with MySQL databases using PHP, we often need to perform SQL queries. If the query fails, the correct error handling mechanism can help us diagnose the problem and deal with it accordingly. The MySQLi extension provides several tools to help developers catch and handle SQL query errors, including the mysqli_stmt::$error and mysqli_stmt::store_result() methods.
This article will explain in detail how to use these two methods to detect and process SQL query failures and show the corresponding PHP sample code.
When executing SQL queries, mysqli_stmt::$error is a very useful property that can obtain error information when the current SQL statement is executed. By checking this property, we can quickly determine whether the query is executed successfully.
<?php
// Database connection
$mysqli = new mysqli("localhost", "username", "password", "database");
// Check the connection
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
// Prepare SQL Statement
$query = "SELECT * FROM users WHERE id = ?";
$stmt = $mysqli->prepare($query);
// examine SQL Statement是否Prepare成功
if ($stmt === false) {
die("PrepareStatement失败: " . $mysqli->error);
}
// Bind parameters
$id = 1;
$stmt->bind_param("i", $id);
// Execute a query
if (!$stmt->execute()) {
// use mysqli_stmt::$error Get error message
die("Execute a query失败: " . $stmt->error);
}
// Close the connection
$stmt->close();
$mysqli->close();
?>
In this example, we get the detailed error information when the SQL query fails through $stmt->error . In this way, developers can understand the specific reasons for the query failure and conduct further debugging or repairing.
The mysqli_stmt::store_result() method is used to extract the result set from the MySQLi statement handle and store it. It is very useful for handling result sets of SELECT queries. If the query does not return any rows (such as the query conditions do not match), the store_result() method will return false, so we can use it to check whether the query has successfully obtained the data.
<?php
// Database connection
$mysqli = new mysqli("localhost", "username", "password", "database");
// Check the connection
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
// Prepare SQL Statement
$query = "SELECT * FROM users WHERE id = ?";
$stmt = $mysqli->prepare($query);
// examine SQL Statement是否Prepare成功
if ($stmt === false) {
die("PrepareStatement失败: " . $mysqli->error);
}
// Bind parameters
$id = 1;
$stmt->bind_param("i", $id);
// Execute a query
if (!$stmt->execute()) {
die("Execute a query失败: " . $stmt->error);
}
// Store result sets
$stmt->store_result();
// examine结果集的行数
if ($stmt->num_rows === 0) {
echo "No user that meets the criteria was found。";
} else {
// The result set is not empty，Perform data processing
echo "Query successful，The results are as follows：";
// Processing results
}
$stmt->close();
$mysqli->close();
?>
In this example, we use the store_result() method to store the result of the query into memory, and then use $stmt->num_rows to check the number of returned result rows. If no data rows are returned, we can handle this "no result" situation.
When using mysqli_stmt::$error and mysqli_stmt::store_result() for error processing, in addition to capturing error information and checking the number of rows of results, you can also do some logging and debugging. Here are some common error handling tips:
Logging : Record error information into a log file instead of directly displaying it to the user, which can avoid leaking sensitive information from the database.
User-friendly error message : For end users, provide friendly prompt information to avoid exposing the underlying database error message.
SQL query optimization : Before executing queries, make sure that SQL statements are valid and avoid common SQL injection vulnerabilities.
By using mysqli_stmt::$error and mysqli_stmt::store_result() , we can better handle and detect SQL query failures. In actual development, a good error handling mechanism can not only improve the robustness of the code, but also help us quickly locate and solve problems in a production environment.
Using a combination of these two methods, you can effectively capture the details of query failures and allow developers to react quickly to deal with query failures.
