How to Correctly Use `mysqli_insert_id()` with Foreign Key Relationships?

mysqli_insert_id with Foreign Key Relationship

When working with multiple tables involving foreign key relationships, it's important to correctly associate data between them. This article addresses an issue of retrieving the last inserted ID from one table (table2) and using it to insert a row into another table (table1).

Problem:

The code attempts to bind the last inserted ID from table2 to an image column in table1 using $image = $mysqli->insert_id. However, this approach fails, preventing the proper association between tables.

Solution:

To resolve this issue, the first step is to ensure that the ID column in table2 is defined as an auto-increment field. Next, use mysqli_insert_id() to retrieve the last inserted ID from table2. Here's the modified code:

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

$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();

The mysqli_insert_id() function captures the last inserted ID from table2 and binds it to the image column in table1 using $last_id. This ensures that the image is correctly associated with the firstname, lastname, and username in table1, preserving the foreign key relationship between the two tables.

The above is the detailed content of How to Correctly Use `mysqli_insert_id()` with Foreign Key Relationships?. For more information, please follow other related articles on the PHP Chinese website!