How Do I Retrieve the Last Inserted ID for Foreign Key Relationships in MySQL?

Retrieving the Last Insert ID for Foreign Key Relationships

In relational databases, it's common to establish foreign key relationships between tables. When inserting a new row into a child table, you may need to associate it with a row in a parent table. To achieve this, you'll typically need to retrieve the last inserted ID from the parent table and use it as a foreign key in the child table.

Challenge:

You want to associate an image (from table2) with a user (in table1) using their first name, last name, and username. However, when you attempt to retrieve the last inserted ID from table2 and bind it as a foreign key in table1, it doesn't work.

Solution:

To successfully retrieve the last inserted ID and use it to populate a foreign key, you need to follow these steps:

1. Ensure that the ID field in table2 is an auto-increment field. This will automatically generate a unique ID for each inserted row.
2. Use the mysqli_insert_id() function after inserting a new row into table2. This function returns the last auto-increment ID generated by the database.

Here's an updated version of your code that incorporates the mysqli_insert_id() function:

$image = mysqli_insert_id($mysqli); // Retrieve the 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, $image);
$stmt->execute();

By making these changes, you can now retrieve the last inserted ID from table2 and use it as a foreign key to link the new row in table1.

The above is the detailed content of How Do I Retrieve the Last Inserted ID for Foreign Key Relationships in MySQL?. For more information, please follow other related articles on the PHP Chinese website!