English
English
In PHP programming, when using MySQLi to perform database transactions, it is usually necessary to ensure that every operation in the transaction is completed smoothly. If any of these steps fail, we should be able to accurately determine which link is wrong. The mysqli::$errno property can help us diagnose the cause of transaction failure. This article will explain in detail how to determine which step in a database transaction failed through mysqli::$errno .
In MySQLi, mysqli::$errno is a property that represents the error code for the latest operation. Through this property, the wrong numeric code can be obtained to determine whether the operation is successful or failed. If the operation fails, errno will return the corresponding error code, and if successful, it will return 0.
Start a transaction
Before starting a transaction, we need to call mysqli::begin_transaction() to start a transaction.
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
// Start a transaction
$mysqli->begin_transaction();
Perform SQL operations in transactions
In a transaction, we may execute multiple SQL queries. We check the error code of each query to determine whether the steps failed.
// Execute the first one SQL Query
$result1 = $mysqli->query("UPDATE users SET balance = balance - 100 WHERE id = 1");
// 检查第一个Query是否成功
if ($mysqli->errno) {
echo "mistake：Query 1 fail，mistake代码：" . $mysqli->errno;
$mysqli->rollback(); // Roll back transactions
exit();
}
// Execute the second SQL Query
$result2 = $mysqli->query("UPDATE users SET balance = balance + 100 WHERE id = 2");
// 检查第二个Query是否成功
if ($mysqli->errno) {
echo "mistake：Query 2 fail，mistake代码：" . $mysqli->errno;
$mysqli->rollback(); // Roll back transactions
exit();
}
// 如果所有Query都成功，Submit transactions
$mysqli->commit();
In this example, we perform two updates: the first reduces the balance of user 1 by 100, and the second increases the balance of user 2 by 100. If any query fails, we output the error code by checking mysqli::$errno and roll back the transaction.
Check for errors via mysqli::$errno
The value of mysqli::$errno is updated after each SQL operation is performed. If an operation fails, mysqli::$errno will return the corresponding error code, and we can use these error codes to determine the problem. For example, if you perform an update operation, the following error code may appear:
1062 : indicates a duplicate key error.
1451 : Indicates a foreign key constraint error.
In the above code, we check if ($mysqli->errno) whether errno has a value. If so, it means that the query failed, and we can further analyze the problem based on the error code provided by mysqli::$errno .
Errors in handling transactions
If an operation in the transaction fails, we need to roll back the entire transaction. Use mysqli::rollback() to undo all executed SQL operations to ensure the database is consistent. In the above code example, we call rollback() when each query fails, and then stop the execution of the script by exit() .
In addition, after the transaction is over, if there is no error, we use mysqli::commit() to commit the transaction to ensure that all changes are saved to the database.
Through mysqli::$errno , we can accurately determine which operation failed in a database transaction. This approach is very effective for debugging and error handling and can help us locate problems and take appropriate measures in complex transactions. Remember, if any error occurs during transaction processing, the transaction should be rolled back immediately to avoid data inconsistencies.
Hope this article helps you better understand how to use mysqli::$errno in PHP to determine error steps in transactions. If you encounter problems in your actual application, you can further analyze and resolve the problem by looking up the MySQL error code table.
