English
English
When developing MySQL-based PHP applications, the mysqli extension is usually used to interact with the database. Although mysqli provides many useful features, it also has some potential errors and exceptions that may cause application crashes or problems that are not easily debugged if not caught and handled in time.
This article will focus on how to capture and process mysqli_stmt::$error error information in PHP scripts in real time to debug and optimize database operations more efficiently.
mysqli_stmt::$error is a property in the mysqli_stmt class that contains error messages generated when executing preprocessing statements. When the execute() method of the mysqli_stmt class is called to execute SQL query, if an error occurs, the $error attribute will save the error description information. By checking this property, we can understand the specific error content and conduct further debugging.
When we perform database operations, if the error message is not captured, we may miss potential database problems. Especially when SQL query fails or data exceptions, the application may continue to run, resulting in inconsistent data or abnormal functionality. Therefore, capturing and processing error information in real time can help us:
Discover and fix SQL errors in time.
Provide clearer debugging information to help developers locate problems.
Optimize database operations and improve application stability and performance.
In PHP, we can capture SQL error messages through the mysqli_stmt::$error attribute. Here is a basic example that demonstrates how to use preprocessing statements from mysqli extension to catch and handle errors.
<?php
// Database connection information
$host = 'localhost';
$username = 'root';
$password = 'password';
$database = 'test_db';
// Create a database connection
$mysqli = new mysqli($host, $username, $password, $database);
// Check if the connection is successful
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
// Create a preprocessing statement
$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
if ($stmt === false) {
die("Failed to prepare statement: " . $mysqli->error);
}
// Bind parameters and execute statements
$id = 1;
$stmt->bind_param("i", $id);
if (!$stmt->execute()) {
// Catch and handle errors
echo "An error occurred while executing a query: " . $stmt->error;
} else {
// Processing results
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo "userID: " . $row['id'] . ", user名: " . $row['username'] . "<br>";
}
}
// Close statements and connections
$stmt->close();
$mysqli->close();
?>
Database connection : We first create a database connection using the mysqli extension and check whether the connection is successful through $mysqli->connect_error .
Prepare SQL statements : Create a preprocessing statement using $mysqli->prepare() . If the creation fails, we directly output the error message.
Execute SQL query : When calling execute() to execute preprocessing statement, if an error occurs, we capture the error through mysqli_stmt::$error and output detailed error information. Otherwise, continue to process the query results.
Close the connection : After executing the database operation, remember to close the statement and the database connection to avoid resource leakage.
In actual development, simply capturing and outputting error information may not be enough to meet debugging requirements, especially in production environments. To debug and optimize database operations more efficiently, we can log error messages into log files instead of outputting them directly in the browser.
<?php
// Define log file path
define('LOG_FILE', '/var/log/php_errors.log');
// Custom error handling functions
function logError($error_message) {
// Get the current timestamp
$timestamp = date('Y-m-d H:i:s');
// Format error message
$log_message = "[$timestamp] - $error_message\n";
// Write to log files
file_put_contents(LOG_FILE, $log_message, FILE_APPEND);
}
// Database connection and execution part absent...
// Catch errors when executing a query
if (!$stmt->execute()) {
$error_message = "An error occurred while executing a query: " . $stmt->error;
// Log error log
logError($error_message);
// Output concise error message（No detailed database error information is exposed）
echo "Database operation failed，Please try again later。";
}
// Close the connection
$stmt->close();
$mysqli->close();
?>
Log file : We define a constant LOG_FILE to store the path to the log file. In actual use, you can select the appropriate path according to the server environment.
Custom error handling function : The logError() function records error information into a log file. Each record will contain the current timestamp for later viewing and analysis.
Log errors when executing the query : After catching the error, we write the error message to the log file through the logError() function, rather than outputting it directly on the page.
In this way, we can maintain the privacy of errors in production environments while facilitating developers' post-debugging.
In PHP, using the mysqli_stmt::$error attribute to capture and process database error information can help us discover problems more timely and fix them. In order to enhance error handling capabilities, it is recommended to combine logging systems to capture detailed error information during the development process to facilitate debugging and optimization of applications.
In addition, reasonable error handling can greatly improve the stability and user experience of the system. When developing and deploying PHP applications, it is important to pay attention to error capture and handling of database operations.
