SQL insert () values (),(),(); how to getauto_increments properly?
in [General]
|
Prev: SQL insert () values (),(),(); how to get auto_increments properly?
Next: confirm subscribe to php-general@lists.php.net
From: tedd on 13 Feb 2010 09:36 At 7:07 AM +0100 2/13/10, Rene Veerman 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. > >-snip- > >Any clues are greatly appreciated.. >I'm looking for the most sql server independent way to do this. Rene: I'm not sure what would be the most efficient way to solve the race problems presented here, but you might want to not confront the race problem at all and solve this a bit more straight forward -- for example: Three steps for each record: 1. Generate a unique value (i.e., date/time). 2. Insert the record with the unique value in a field and the auto_increment ID will be automatically created. 3. Then search for the record with that unique value and retrieve the auto_incremented ID value. While this might take a few more cycles, it would work. If you want your auto_increment ID's to be in sequence, then that's a different problem and if so, maybe you should rethink the problem. I've never seen a problem where the ID's were required to be anything other than unique. Cheers, tedd -- ------- http://sperling.com http://ancientstones.com http://earthstones.com
From: Ashley Sheridan on 13 Feb 2010 09:45
On Sat, 2010-02-13 at 08:46 -0600, Joseph Thayne wrote: > In order to make this as "sql server independent" as possible, the first > thing you need to do is not use extended inserts as that is a MySQL > capability. If you are insistent on using the extended inserts, then look > at the mysql_info() function. That will return the number of rows inserted, > etc. on the last query. > > -----Original Message----- > From: Rene Veerman [mailto:rene7705(a)gmail.com] > Sent: Saturday, February 13, 2010 12:08 AM > To: php-general > Subject: [PHP] SQL insert () values (),(),(); how to get auto_increments > properly? > > 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. > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > But getting the number of rows isn't really all that useful, as it won't tell you what the auto increment id values are, and if any inserts fail, it won't tell you which ones. Thanks, Ash http://www.ashleysheridan.co.uk |