CODEWITHSUNDEEP
phpmysqlsqldatabase
Blackecho
Blackecho

Reputation: 1290

LAST_INSERT_ID() with multiple records insert MySQL

I have an sql multiple records INSERT, with an 'id' field set to AUTO_INCREMENT, something like this:

 INSERT INTO table1 VALUES (null, "bla"),(null, "bla"),(null, "bla"),(null, "bla") etc..;

and then i have another multiple records INSERT: 

INSERT INTO table2 VALUES (null, "bla"),(null, "bla"),(null, "bla"),(null, "bla") etc..;

Now i want the 'id2' of table2 to be the same of table1, but i don't know how to remember all the last_insert_ids from table1 to insert them to table2, if i use LAST_INSERT_ID() it gives me only the 'id' of the first inserted value. Does anyone know how to take all the last_insert_ids?

Upvotes: 2

Views: 5501

Answers (3)

rashmani
rashmani

Reputation: 63

if I understand correctly, indexes in your second table are references (like FK) to indexes in your first table. In this case you could:

  1. insert a record in first table
  2. get last_insert_id() and store it in an array
  3. loop 1-2 'til you're done
  4. build an insert query using your array values

In PHP this would be something like:

var arrInserted = array();

foreach($newRecords as $newRecord) {
 $sql = "INSERT INTO table1 ($newRecord->valBlah);";
 $stmt = $dbh->prepare($sql);  
 $stmt->execute();

 $sql = "SELECT LAST_INSERT_ID() AS myNewID;";
 $stmt = $dbh->query($sql);  
 arrInserted[] = $stmt->fetchColumn();
 }

if(count($arrInserted) > 0) {
 var $arrAddValues = array();
 foreach($arrInserted as $newID) 
   $arrAddValues[] = '(' . $newID . ',' $anyBlahValue . ')';
 $sql = "INSERT INTO table2 (field1, field2) VALUES " . implode(',', $arrAddValues) . ";";
 $stmt = $dbh->prepare($sql);  
 $stmt->execute();
}

Ok, not so beautiful and untested but it should give you an idea... Watch out for query size when imploding $arrAddValues, I believe MySQL has a limit and others may too.

HTH, rash*

Upvotes: 0

Kescha Skywalker
Kescha Skywalker

Reputation: 489

Try the simple way to use LAST_INSERT_ID() + number of inserts made (-1). If you're using native mysql auto increment, you should get a correct result.

If you're inserting the data in one transaction, the values should be in one row.

Look here how to solve you're question: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Upvotes: -1

Ryan
Ryan

Reputation: 3582

You can't have multiple id's returned for the last insert id. MySQL works this way deliberately (See MySQL docs).

You should know your auto increment value (e.g. 1) so if you know you are inserting 5 rows, you would use the last insert id for the 1st row, and the last insert id + 5 for the 5th row.

Upvotes: -1

Related Questions