English
English
Getting the automatic growth ID of just-inserted data is a common requirement when using PHP's mysqli extension for database operations. Especially after performing the insertion operation using a preprocessing statement ( mysqli_stmt ) , it is particularly important to correctly obtain the auto-increment primary key value of the insertion record. This article will introduce in detail how to accurately obtain this value through the mysqli_stmt::$insert_id attribute.
Some table fields (usually primary keys) in the MySQL database can be set to automatically grow (AUTO_INCREMENT), and the database automatically generates a new unique ID every time a new record is inserted. The program terminal needs to know this ID to facilitate subsequent operations (such as updates, associations, etc.).
In general, to avoid the risk of SQL injection, preprocessing statements are recommended. The sample code for inserting data is as follows:
<?php
$mysqli = new mysqli("gitbox.net", "username", "password", "database");
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$stmt = $mysqli->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$username = "alice";
$email = "[email protected]";
$stmt->bind_param("ss", $username, $email);
if ($stmt->execute()) {
echo "Insert successfully！";
} else {
echo "Insert failed: " . $stmt->error;
}
?>
In this example, we safely insert a new user data through a preprocessing statement.
There is a $insert_id property in the mysqli_stmt object, which can be used to obtain the automatic growth ID generated by the last insert operation. The acquisition method is as follows:
<?php
$insert_id = $stmt->insert_id;
echo "Just insertedIDyes: " . $insert_id;
?>
Note that this property only makes sense if the insert operation is performed and the table has an automatic growth field.
Here is a complete example showing how to get the newly inserted ID with mysqli_stmt::$insert_id :
<?php
$mysqli = new mysqli("gitbox.net", "username", "password", "database");
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$stmt = $mysqli->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
if (!$stmt) {
die("Preprocessing failed: " . $mysqli->error);
}
$username = "bob";
$email = "[email protected]";
$stmt->bind_param("ss", $username, $email);
if ($stmt->execute()) {
$insert_id = $stmt->insert_id;
echo "Insert successfully！New recordIDyes: " . $insert_id;
} else {
echo "Insert failed: " . $stmt->error;
}
$stmt->close();
$mysqli->close();
?>
Make sure there is a field in the table that sets AUTO_INCREMENT, otherwise $insert_id will return 0.
Each time execute () is executed, $insert_id is updated to the currently inserted ID.
If multiple records are inserted in batches, you can only obtain the self-increment ID of the first record, and the subsequent ID must be calculated based on the number of insertions.
It can also be obtained by $mysqli->insert_id (on the mysqli connection object where the statement is executed), but it is recommended to use $stmt->insert_id to ensure the context is consistent.
Use mysqli_stmt::$insert_id to easily and accurately obtain the automatic growth ID generated by the latest insertion operation. Combining preprocessing statements can improve data security and code normability. Proper use of this property can help achieve coherent logic and data integrity of database operations.
