Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?

On Sat, Feb 13, 2010 at 2:07 PM, Rene Veerman <[email protected]> wrote:

> Hi.
>
> I'm looking for the most efficient way to insert several records and
> retrieve the auto_increment values for the inserted rows, while
> avoiding crippling concurrency problems caused by multiple php threads
> doing this on the same table at potentially the same time.
>
> I'm using mysql atm, so i thought "stored procedures!"..
> But alas, mysql docs are very basic.
>
> I got the gist of how to setup a stored proc, but how to retrieve a
> list of auto_increment ids still eludes me; last_insert_id() only
> returns for the last row i believe.
> So building an INSERT (...) VALUES (...),(...) at the php end, is
> probably not the way to go then.
>
> But the mysql docs don't show how to pass an array to a stored
> procedure, so i can't just have the stored proc loop over an array,
> insert per row, retrieve last_insert_id() into temp table, and return
> the temp table contents for a list of auto_increment ids for inserted
> rows.
>
> Any clues are greatly appreciated..
> I'm looking for the most sql server independent way to do this.
>
>
Rene

 I have not been worked with mysql multi-insert before.
But just did a simple test on my mysql 5.0 copy.

I assume that you are using MyISAM table and will lock its read, writel
when inserting data.

When multi-insert was done, and did a select last_insert_id(). I saw that
only
the first inserted id was returned. Please take a look the following steps:


mysql> select * from temp;
Empty set (0.00 sec)

mysql> insert into temp (firstname, price) values ('dd', 10), ('cc', 3),
('bb', 99);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into temp (firstname, price) values ('dd', 10), ('cc', 3),
('bb', 99);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                4 |
+------------------+
1 row in set (0.00 sec)


So, let's say three records was inserted, and the first inserted id was 1.
You get id from 1 to 3.

! This will not work on transaction-based insert !

Just a thought and tested on mysql but not on php.



Regards,
Eric

> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>