English
English
In PHP, using the mysqli extension can help us interact with the MySQL database, where mysqli_stmt::$error is a very useful function that can be used to obtain error information related to preprocessing statements. When we perform database operations, errors may cause data inconsistency, especially in multi-step operations. To ensure the reliability of the operation, we can use the mysqli_stmt::$error function in combination with the transaction management function to handle errors and roll back when an error occurs, thereby ensuring the consistency and integrity of the data.
mysqli_stmt::$error is a property in the mysqli_stmt class that returns error information related to the recently executed preprocessing statement. This property is very important for debugging and ensuring the correctness of SQL statements. It returns a string containing the error message, and if no error occurs, an empty string.
A transaction is a collection of operations that either succeed or fail. Transaction management in MySQL can help us ensure that when performing multiple operations, we can restore to the pre-operation state even if an error occurs in the middle. By performing multiple operations in a transaction, we can ensure consistency and integrity of our data.
In MySQL, transactions can be controlled through the following SQL statements:
BEGIN or START TRANSACTION : Start a transaction.
COMMIT : Submit transactions so that the changes made permanently.
ROLLBACK : Rollback the transaction and revoke all changes in the transaction.
When performing database operations, especially when multiple steps of data updates are involved, we need to ensure that each step is successful. If any of these steps fail, the entire transaction should be rolled back to avoid some data updates successfully while others fail, resulting in inconsistency in data. We can use mysqli_stmt::$error to capture the error message of SQL statements and roll back the transaction when an error occurs.
Here is a simple example showing how to combine the mysqli_stmt::$error function with transaction rollback:
<?php
// Create a database connection
$mysqli = new mysqli("localhost", "username", "password", "database");
// Check if the connection is successful
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
// Start a transaction
$mysqli->begin_transaction();
try {
// The first query operation
$stmt = $mysqli->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);
$username = 'johndoe';
$email = '[email protected]';
$stmt->execute();
// Check for errors
if ($stmt->error) {
throw new Exception("An error occurred while executing a query: " . $stmt->error);
}
// The second query operation
$stmt = $mysqli->prepare("UPDATE accounts SET balance = balance - 100 WHERE user_id = ?");
$stmt->bind_param("i", $user_id);
$user_id = 1;
$stmt->execute();
// Check for errors
if ($stmt->error) {
throw new Exception("An error occurred while executing a query: " . $stmt->error);
}
// Submit transactions
$mysqli->commit();
echo "Operation is successful！";
} catch (Exception $e) {
// If an error occurs，Roll back transactions
$mysqli->rollback();
echo "Operation failed: " . $e->getMessage();
}
// Close the connection
$mysqli->close();
?>
Start a transaction:
We use $mysqli->begin_transaction(); to start a transaction. This will make the next database operations within the same transaction.
Perform database operations:
Use the mysqli preprocessing statement to perform two database operations: one insert operation and one update operation. After each operation, we check if an error occurred. If an error occurs, we throw an exception via throw new Exception and roll back the transaction in exception handling.
Check for errors:
We obtain the error message when SQL execution is performed through $stmt->error . If an error occurs, we will throw an exception and roll back the transaction. In this way, both the previous operations and the subsequent operations will be revoked to ensure the consistency of the data.
Roll back the transaction:
If any exception is caught during operation, we will roll back the transaction using $mysqli->rollback() ;. This will undo all changes made in the transaction, preventing some data from being updated successfully while others fail to be updated.
Submit transaction:
If all operations are executed successfully, we submit the transaction using $mysqli->commit(); to ensure that the changes are permanently saved to the database.
By combining the mysqli_stmt::$error function and transaction rollback mechanism, we can ensure the reliability of database operations. When multiple database operations are performed, if one operation fails, the transaction can be rolled back to avoid data inconsistencies. This method not only improves the robustness of the code, but also ensures that the data will not be damaged when errors occur. It is a common error handling mode in development.
