How to Retrieve the Last Inserted ID in MySQLi and Use It for Another Table Insertion?

Retrieve Last Insert ID and Use It to Insert into Another Table with MySQLi

To insert a record into another table while associating it with the last inserted ID from a different table, you need to follow these steps:

Create an Auto-Increment Field

Ensure you have an auto-increment field in your primary key column to track the most recently inserted record's ID.

Retrieve Last Insert ID

Use the mysqli_insert_id() function to retrieve the last inserted ID from the first table. This ID will be used to link to the newly inserted record in the second table:

$last_id = mysqli_insert_id($conn);

Prepare Statement

Create a prepared statement that will insert the necessary data into the second table, including the association with the last inserted ID:

$stmt = $mysqli->prepare("
  INSERT INTO table1 (username, firstname, lastname, image) 
  SELECT ?,?,?,? FROM table2 t2 WHERE username = ? AND t2.id = ? 
");

Bind Parameters

Bind the parameters as usual, including the $image parameter with the value of the last inserted ID:

$stmt->bind_param('sssss', $username, $fname, $lname, $last_id, $username);

Execute Statement

Execute the prepared statement to insert the data into the second table:

$stmt->execute();

By following these steps, you can successfully retrieve the last inserted ID from one table and insert the record into another table while associating the data correctly.

The above is the detailed content of How to Retrieve the Last Inserted ID in MySQLi and Use It for Another Table Insertion?. For more information, please follow other related articles on the PHP Chinese website!