English
English
When we perform multiple insert operations or insert data in a transaction, it may be inaccurate to read the autoincrement ID directly through mysqli->insert_id , especially when multi-threaded or multi-user concurrent access. Using the $insert_id property of the preprocessed statement, you can ensure that the insert ID affected by the current statement is obtained.
mysqli_stmt::$insert_id is a new attribute added in PHP 8.1 and later versions. It is specifically used to obtain the self-increment ID after executing preprocessing statements. It is more accurate and safe than $mysqli->insert_id .
The following example demonstrates how to perform an insert operation using mysqli_stmt in a transaction and get the ID of the insert record:
<?php
$mysqli = new mysqli("gitbox.net", "username", "password", "database");
// Start a transaction
$mysqli->begin_transaction();
try {
// Preprocessing insert statements
$stmt = $mysqli->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$username = 'alice';
$email = '[email protected]';
$stmt->bind_param("ss", $username, $email);
// Perform insertion
$stmt->execute();
// Get the inserted ID
$insertId = $stmt->insert_id;
echo "Newly inserted user'sIDyes: " . $insertId . "\n";
// Submit transactions
$mysqli->commit();
$stmt->close();
} catch (Exception $e) {
// An error occurred rollback transaction
$mysqli->rollback();
echo "Transaction failed，Rolled back。error message: " . $e->getMessage();
}
$mysqli->close();
?>
When performing an insert operation in a transaction, first call $mysqli->begin_transaction() to start the transaction. After the operation is completed, call $mysqli->commit() to submit. If it fails, call $mysqli->rollback() to rollback.
Use the mysqli_stmt preprocessing statement to perform insertion to avoid the risk of SQL injection.
After the insertion is successful, get the auto-increment ID of this insert through $stmt->insert_id to ensure accuracy.
Transactions must be rolled back during exception handling to ensure the consistency and integrity of the data.
mysqli_stmt::$insert_id is only available in PHP 8.1 and above. If you are using an old version of PHP, you can consider using $mysqli->insert_id , but pay attention to concurrency issues.
