How to Reliably Retrieve the Last Inserted Row ID in MySQL for Multi-Table Associations?

Retrieving Last Inserted Row to Associate with Other Table

When inserting data into multiple tables, it's often necessary to associate the inserted record with another table using its ID. In such cases, retrieving the last inserted row's ID becomes crucial. However, using $mysqli->insert_id directly may lead to incorrect results.

Solution:

To correctly retrieve the last inserted ID, you need to use the mysqli_insert_id() function. This function provides the ID of the last row inserted into the current database connection. The corrected code below demonstrates its usage:

<br>$stmt = $mysqli->prepare("<br>  INSERT INTO table1 (username, firstname, lastname, image) <br>  SELECT ?,?,?,image FROM table2 t2 WHERE username = ?<br>");<br>$stmt->bind_param('ssss', $username, $fname, $lname, $username);<br>$stmt->execute();</p>
<p>$last_id = mysqli_insert_id($conn); // Retrieve the last inserted ID</p>
<p>// Update the image column in table1 using the retrieved ID<br>$stmt2 = $mysqli->prepare("<br>  UPDATE table1 SET image = ? WHERE id = ?<br>");<br>$stmt2->bind_param('si', $image, $last_id);<br>$stmt2->execute();<br>

Note: Ensure that your ID field in the table2 has the AUTO_INCREMENT attribute to generate unique ID values for each inserted row.

The above is the detailed content of How to Reliably Retrieve the Last Inserted Row ID in MySQL for Multi-Table Associations?. For more information, please follow other related articles on the PHP Chinese website!