How to get all last_insert_ids after running a single statement multiple insert query?

Question

I have a 'location' table and a 'location_detail'[for inserting different language data] table. 'location_detail' contains FK of location table

I need to enter multiple location at a time. So what I am doing is:

run a 'for' loop inside that first I enter data into 'location' table get the loc_id then insert into location_detail table[Here in 'location_detail' table if more than one language present, again I want to run the query multiple times].

So if I want to add 3 locations -> Outer 'for' loop will run 3 times total no of query exec. is 6 [If more than one language is present this will multiple]

==>My aim is to insert all 3(say) locations into 'location' table using multiple insert in a single statement and get all 3 last_insert_ids.

==>Next I can run single statement multiple insert query for adding into 'location_details' table

Here, how will I get this last_insert_ids in an array?

Answer 1

You can use transaction and get last_insert_id and calculate previous id's relate to your AUTO_INCREMENT settings.

Important
If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.

from Last Insert ID documentation.

Answer 2

I'll do this in the same transaction/connection :

INSERT INTO location (col1, col2) VALUES (val1a, val2a); 
SET @string_ids = LAST_INSERT_ID();

INSERT INTO location (col1, col2) VALUES (val1b, val2b); 
SET @string_ids = CONCAT(@string_ids , ",", LAST_INSERT_ID());

INSERT INTO location (col1, col2) VALUES (val1c, val2c); 
SET @string_ids = CONCAT(@string_ids , ",", LAST_INSERT_ID());

SELECT @string_ids ;

Then in php, I would explode this variable :

$array_ids = explode (",", $string_ids ).

Then build your request with php :

INSERT INTO location_detail(id, fk_id) VALUES 
//foreach loop
     (val_id1b, $array_ids[$i] )
     (val_id2b, $array_ids[$i] )
     (val_id3b, $array_ids[$i] )

I haven't tried the @array_ids but it should work.

Look at this link for more help if you need it.

I hope it fits your needs.