0

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?

웃웃웃웃웃
  • 11,829
  • 15
  • 59
  • 91
user2609417
  • 259
  • 2
  • 3
  • 9

2 Answers2

0

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.

Community
  • 1
  • 1
kmas
  • 6,401
  • 13
  • 40
  • 62
-2

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.

BaBL86
  • 2,602
  • 1
  • 14
  • 13
  • This doesn't take into account multiple sessions. – Zarathuztra Dec 16 '13 at 13:27
  • @BaBL86 so i can find out other insert_ids by adding no of locations to add, right? – user2609417 Dec 16 '13 at 13:47
  • @Zarazthuztra I didn't understand, please explain – user2609417 Dec 16 '13 at 13:49
  • @user2609417 You could very easily have multiple sessions to the database (multiple users) so running a bunch of queries, then grabbing the last insert ID and subtracting based on the number of queries you ran is most likely going to run into race conditions depending on the number of users. The solution kmas gave is the most likely one to work, because it records the last insert ID first, and then allows it to be used in subsequent queries. – Zarathuztra Dec 16 '13 at 15:19
  • @Zarazthuztra that's why we use transaction. – BaBL86 Dec 16 '13 at 19:12
  • @BaBL86 I think you misunderstood: http://stackoverflow.com/questions/4226766/mysql-transactions-vs-locking-tables – Zarathuztra Dec 16 '13 at 19:55