CODEWITHSUNDEEP
phpmysqlpdo
Steven
Steven

Reputation: 19425

Is there a better way to return last inserted ID for multi insert?

I'm using PHP to add 95 new rows to my MySQL DB and return new row ID. The execution time takes around 24 seconds. If I go past 30 seconds, PHP will stop executing (default time limit 30 seconds).

I need to return the row ID for each row inserted so that I can use it to install associated data.

My current solution is this:

  /* snippets from my class objects to illustrate my code */

  // This foreach takes 24 seconds on just 95 rows
  foreach($list as $row) {
    $id = $this->importRows($sid,$table)

    array_push($id_list,$id);
  }

  /* PDO insertion */
  protected function importRows($row) {
    $sql = "INSERT INTO my_table (name, colour)
            VALUES $row['name'], $row['colour']";

    $result = $this->db->exec($sql);
    return $this->db->lastInsertId();
  }

To reduce insettion time, I'm hoping I can insert multiple rows in one query According to MySQL (scroll down to the red lind and the word IMPORTANT) it says:

If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only.

The solution they suggest is to create another table and inset the new ID's there, then I can fetch the new id's by a select statement at the end.

Has anyone worked on a similar solution? Any suggestions on how I can make thisa bit more time effective?

Upvotes: 4

Views: 1387

Answers (2)

vitkovskii
vitkovskii

Reputation: 62

  1. lock table
  2. add your data using one 'insert'
  3. unlock table
  4. get last insert id
  5. other id's can be computed: id[n]=last_insert_id+n, where n is number of your inserted line

Upvotes: 0

nobody
nobody

Reputation: 10645

this is the trick I used to use in such cases:

$query = "INSERT INTO my_table (name, colour) VALUES";
$i = 0;
foreach( $rows as $row ) {
    if( $i ) $query .= ',';
    $query .= " ( $row[ 'name' ],
              IF( @id$i := LAST_INSERT_ID(), $row[ 'colour' ], $row[ 'colour' ] ) )";
    ++$i;
}
$result = $this->db->exec( $query );

then have a query like this to fetch the ids:

SELECT @id1, @id2, @id3, ...

Upvotes: 3

Related Questions