How to Retrieve the Last Inserted ID in MySQLi for Cross-Table Inserts?

Retrieving Last Insert ID with "mysqli_insert_id" for Cross-Table Insertion

You are attempting to associate an image with data in another table. To do so, you need to retrieve the last row inserted into the first table and use its ID to perform the insert in the second table. However, the method you are using to obtain the last insert ID is not working.

Solution:

The correct syntax to retrieve the last inserted ID using the MySQLi interface is mysqli_insert_id($conn). You can then use this ID to bind to the insert statement.

Here's a corrected version of your code:

$last_id = mysqli_insert_id($mysqli); // Get the last inserted ID

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

Additional Considerations:

Ensure that the ID field in the first table is an auto-increment field. This ensures that a unique ID is generated each time a row is inserted.

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