English
English
In PHP, the mysqli_autocommit() function is used to enable or disable the auto-commit feature for transactions. By default, MySQL automatically commits transactions after each query execution. However, in some cases, developers may need to turn off auto-commit to maintain transaction consistency when executing multiple queries. In such cases, developers need to manually call commit() or rollback() to commit or roll back the transaction.
However, issues can arise when committing a transaction, especially when using mysqli_autocommit(). To troubleshoot these issues, the mysqli::$errno and mysqli::$error properties are particularly important. mysqli::$errno helps us retrieve the error code of a MySQL operation, while mysqli::$error provides the error message. By using both of these properties, developers can easily debug and identify the cause of the problem.
First, let's look at a basic PHP example that demonstrates how to use mysqli_autocommit() for transaction control and how to use mysqli::$errno to debug errors that occur when committing a transaction.
<?php
// Create a database connection
$mysqli = new mysqli("localhost", "username", "password", "database");
<p>// Check if the connection is successful<br>
if ($mysqli->connect_error) {<br>
die("Connection failed: " . $mysqli->connect_error);<br>
}</p>
<p>// Disable auto-commit mode<br>
$mysqli->autocommit(false);</p>
<p>// Start a transaction<br>
$mysqli->begin_transaction();</p>
<p>try {<br>
// Execute the first query<br>
$mysqli->query("INSERT INTO users (username, email) VALUES ('user1', '<a class="cursor-pointer" rel="noopener">user1@m66.net</a>')");</p>
$mysqli->query("INSERT INTO orders (user_id, product) VALUES (1, 'product1')");
// Check for errors
if ($mysqli->errno) {
// If an error occurs, roll back the transaction
throw new Exception("Error: ".$mysqli->error);
}
// Commit the transaction
$mysqli->commit();
echo "Transaction successfully committed!";
} catch (Exception $e) {
// If an exception is caught, roll back the transaction
$mysqli->rollback();
echo "Transaction rolled back, error message: " . $e->getMessage();
}
// Close the database connection
$mysqli->close();
?>
Database Connection: We first create a database connection using new mysqli() to connect to the MySQL server. If the connection fails, we retrieve the error message via $mysqli->connect_error and terminate the program.
Disable Auto-commit: We use $mysqli->autocommit(false) to disable auto-commit mode. This means that after each query, the transaction will not be automatically committed, and we need to manually call commit() to commit the transaction.
Execute Transaction: We start the transaction with $mysqli->begin_transaction() and execute insert operations using $mysqli->query(). If any query fails, $mysqli->errno will return the error code, and $mysqli->error will return the error description.
Error Handling: If an error occurs during query execution, we check and capture it with if ($mysqli->errno). If an error occurs, we throw an exception with throw new Exception() and roll back the transaction ($mysqli->rollback()).
Commit the Transaction: If all queries succeed, we call $mysqli->commit() to commit the transaction.
Close Connection: Finally, we close the database connection using $mysqli->close().
In the above code, $mysqli->errno is mainly used to determine whether the query executed successfully. If a query fails, $mysqli->errno will return a non-zero error code, while $mysqli->error will provide the specific error message. With this information, we can quickly locate the problem.
If $mysqli->errno is 0, it means no error occurred.
If $mysqli->errno is a non-zero value, an error has occurred, and we can check $mysqli->error for the specific error message.
For example, if a INSERT query fails because of a duplicate entry in the table, MySQL will return error code 1062 (duplicate key error). In this case, $mysqli->errno will return 1062, and $mysqli->error will return an error message like "Duplicate entry 'user1' for key 'PRIMARY'".
1062 - Duplicate entry error (e.g., duplicate primary key)
1046 - No database selected
1146 - Table doesn't exist
2002 - Failed to connect to MySQL server
By using $mysqli->errno and $mysqli->error, we can effectively identify the cause of errors and take appropriate actions in a timely manner.
Summary
When using mysqli_autocommit() for transaction control, mysqli::$errno and mysqli::$error are very useful debugging tools when encountering commit issues. They help developers quickly locate the cause of errors, making debugging and problem-solving more efficient. In practical development, remember to use transactions and error handling mechanisms properly to ensure the reliability and stability of database operations.
