English
English
When performing database operations in PHP, mysqli is a very common extension that provides object-oriented and procedural interfaces. Today we will discuss how to combine mysqli::stmt_init function with transaction control ( begin_transaction , commit , rollback ) to improve the stability and efficiency of database operations.
mysqli::stmt_init is a method in the mysqli class that is used to initialize a preprocessing statement. Prepared Statements can not only prevent SQL injection attacks, but also improve the execution efficiency of database queries. Because when using preprocessing statements, the query statement will be parsed once, and different parameters can be passed in during execution, reducing the overhead of repeated parsing of SQL statements.
In a database, a transaction refers to a set of database operations that either succeed or fail. Transactions ensure data consistency and reliability. The basic operations of a transaction include:
begin_transaction : start a transaction.
commit : commit transactions so that the modifications to the database take effect.
rollback : Rollback transactions and revoke all operations in the transaction.
When you are performing multiple database operations, you may need to ensure that these operations are either successful or fail. To achieve this, you can use transaction control.
Using mysqli::stmt_init combined with transaction control can ensure the stability of multiple database operations. If an operation fails during execution, you can roll back the transaction to revoke all executed operations to ensure the consistency of the database.
Here is a PHP code example combining mysqli::stmt_init with transaction control:
<?php
// Create a database connection
$mysqli = new mysqli("localhost", "username", "password", "database_name");
// Check if the connection is successful
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
// Start a transaction
$mysqli->begin_transaction();
try {
// Initialize preprocessing statements
$stmt = $mysqli->stmt_init();
// The first insertion operation
if ($stmt->prepare("INSERT INTO users (username, email) VALUES (?, ?)")) {
$username = 'user1';
$email = 'user1@m66.net';
$stmt->bind_param("ss", $username, $email);
$stmt->execute();
} else {
throw new Exception("Failed to prepare statement");
}
// The second insertion operation
if ($stmt->prepare("INSERT INTO orders (user_id, product) VALUES (?, ?)")) {
$user_id = 1; // Assuming the userIDfor1
$product = 'Product A';
$stmt->bind_param("is", $user_id, $product);
$stmt->execute();
} else {
throw new Exception("Failed to prepare statement");
}
// Submit transactions
$mysqli->commit();
echo "Transaction successfully submitted！";
} catch (Exception $e) {
// Roll back the transaction when an error occurs
$mysqli->rollback();
echo "Transaction rollback: " . $e->getMessage();
}
// Close the connection
$mysqli->close();
?>
Establish a database connection: Use the new mysqli() function to connect to the MySQL database. If the connection fails, the program outputs an error message and stops execution.
Start a transaction: Use $mysqli->begin_transaction() to start a transaction. After the transaction begins, all database operations are executed in the transaction until the transaction is committed or rolled back.
Initialize and prepare preprocessing statements: Use the $mysqli->stmt_init() method to initialize a preprocessing statement object. Then use the $stmt->prepare() method to prepare the SQL query.
Bind parameters and execute: Use the $stmt->bind_param() method to bind parameters in SQL statements to ensure that data is inserted safely. Then, use $stmt->execute() to execute the preprocessing statement.
Commit or rollback transaction: If all operations are successful, use $mysqli->commit() to commit the transaction and save all modifications to the database. If any error is encountered during execution, an exception is thrown and the transaction is rolled back using $mysqli->rollback() in the catch statement block, revoking all previous operations.
Close connection: After the operation is completed, close the database connection through $mysqli->close() .
By combining mysqli::stmt_init with transaction control, you can enjoy the following advantages:
Prevent SQL injection: Using preprocessing statements can effectively prevent SQL injection attacks.
Improve efficiency: Preprocessing statements do not need to parse SQL repeatedly when executing, reducing the burden on the database.
Ensure data consistency: Through transaction control, the atomicity of database operations is ensured, either all successful or all fail, avoiding data inconsistencies.
Exception handling: If an error occurs during operation, you can roll back the transaction to ensure that the database is not corrupted.
Combining the mysqli::stmt_init function and transaction control ( begin_transaction , commit , rollback ), you can improve the stability and efficiency of PHP programs for database operations. This not only effectively avoids SQL injection problems, but also ensures data consistency when multiple database operations fail and avoids data corruption.
