Mysql Insert get ID then insert returned Id

Question

When executing multiple MySQl statements, is there a way I can can get the last inserted id and insert it in another table? For example if a primary key is a foreign key in my second table.

Table one

user_id | username|
-------------------
1       |userone
2       |usertwo
3       |userthree

Table 2

contact_id | user_id | contact_name
------------------------------------
12         | 1       | John Doe
18         | 2       | Jane Doe
23         | 3       | An other

Basically I want to insert on two tables at the same time, insert on table one then use the value to insert on table two rather than insert on table one then on table two.

Answer 1

Yes, the function LAST_INSERT_ID() returns the most recent id generated by an auto-increment column in the current session.

So you can insert into your primary table:

INSERT INTO `table1` (username) VALUES ('...');

Then insert into the child table:

INSERT INTO `table2` (user_id, contact_name) VALUES (LAST_INSERT_ID(), '...');

If you have multiple rows to insert into the child table, and the child table's primary key itself generates new values, this means you must save the value in a variable to reuse it multiple times. Otherwise, the child table's PK will become the new most recent generated value. Here's an example of saving the value in a MySQL session variable:

SET @user_id = LAST_INSERT_ID();
INSERT INTO `table2` (user_id, contact_name) VALUES (@user_id, '...');
INSERT INTO `table2` (user_id, contact_name) VALUES (@user_id, '...');
INSERT INTO `table2` (user_id, contact_name) VALUES (@user_id, '...');