English
English
When working with PHP for database operations, especially when inserting data using MySQLi prepared statements (mysqli_stmt), we often need to obtain the auto-increment ID of the newly inserted record to perform follow-up queries, updates, or related operations. This article will explain in detail how to retrieve the last inserted ID through mysqli_stmt::$insert_id and demonstrate how to use this ID for subsequent queries.
mysqli_stmt::$insert_id is a property of the mysqli_stmt object that retrieves the auto-increment primary key ID generated by the record inserted via the prepared statement. It is important to note that this property only returns a valid ID if the executed operation is an INSERT and the table has an AUTO_INCREMENT field.
Below is a complete example demonstrating how to insert a record, obtain the inserted ID, and then query the data just inserted using this ID.
<?php
$mysqli = new mysqli("localhost", "username", "password", "test_db");
<p>// Check connection<br>
if ($mysqli->connect_errno) {<br>
die("Connection failed: " . $mysqli->connect_error);<br>
}</p>
<p>// SQL to insert data<br>
$insert_sql = "INSERT INTO users (username, email) VALUES (?, ?)";<br>
$stmt = $mysqli->prepare($insert_sql);</p>
<p>if (!$stmt) {<br>
die("Prepare failed: " . $mysqli->error);<br>
}</p>
<p>// Bind parameters and execute insert<br>
$username = 'john_doe';<br>
$email = '<a class="cursor-pointer" rel="noopener">[email protected]</a>';<br>
$stmt->bind_param("ss", $username, $email);<br>
$stmt->execute();</p>
<p>// Get the inserted ID<br>
$inserted_id = $stmt->insert_id;<br>
echo "Insert successful, ID is: " . $inserted_id . "\n";</p>
<p>// Close the insert statement<br>
$stmt->close();</p>
<p>// Use the inserted ID to perform a query<br>
$select_sql = "SELECT * FROM users WHERE id = ?";<br>
$stmt = $mysqli->prepare($select_sql);</p>
<p>if (!$stmt) {<br>
die("Query prepare failed: " . $mysqli->error);<br>
}</p>
<p>$stmt->bind_param("i", $inserted_id);<br>
$stmt->execute();<br>
$result = $stmt->get_result();</p>
<p>if ($row = $result->fetch_assoc()) {<br>
echo "Query result:\n";<br>
print_r($row);<br>
} else {<br>
echo "No matching record found.\n";<br>
}</p>
<p>// Close resources<br>
$stmt->close();<br>
$mysqli->close();<br>
?><br>
Ensure the same database connection is used
If insert and query operations use different connections, insert_id will not correctly reflect the inserted ID.
The table must contain an AUTO_INCREMENT field
Otherwise, even if the insert succeeds, insert_id will not return a valid value.
insert_id is a property of the statement object, not the connection object
You can only get $stmt->insert_id after preparing a statement; otherwise, use $mysqli->insert_id.
Obtain the primary key ID after inserting orders, users, articles, etc., to establish one-to-many or many-to-many relationships.
Implement automatic redirection or feedback after user data submission.
Record unique identifiers of operation results in logging or auditing systems.
Using mysqli_stmt::$insert_id, we can conveniently retrieve the last inserted record ID, enabling more complex data operation logic. In practical development, this method helps ensure data accuracy and consistency and is the recommended approach for safe database operations using MySQLi.
Related Tags:
mysqli_stmt
