PearDB question I'm sure has been asked a million times

I have a question regarding Pear:B inserts that I'm sure has been asked a million times, but I haven't been able to find any answers by googl'ing around.

When I do an insert into the database in Pear:B, how do I find out the ID/Key of the newly inserted row? Do I really have to do a new select statement with all of the values that I inserted with?

I noticed there is a function under Pear DB Dataobject, but I'd rather not bring another package into the mix...

Thanks in advance,
Jason

Well if you are using MySQL you can use LAST_INSERT_ID() though that is rather DB specific.



If you want to be as portable as possible - how about selecting the MAX(ID)?

Hmm... is there any more efficient method (read: one not involving another database call)? Also, if the site is under heavy traffic, don't race conditions become an issue? What if one user does something that induces an INSERT, and another user's PHP thread does another INSERT before the first user's PHP thread selects the last inserted ID?

I found the answer to my own question, although only after finding about last_insert_id and googling arond ont hat for abit:

"The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behaviour ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions."

Any method call will involve another DB call (What do you think the method does). As for the race part - yeah you can have issues with it (though how much of one I don't know - so see the docs). How much traffic are you expecting?

From the docs, and the fact that the last insert id is remembered on a per-connection basis, it looks like race conditions may be eliminated entirely here.

As for how much traffic, I don't know exactly, but we're talking about a production web site with potentially hundreds of concurrent users.

There is an example of this method on:
http://laughingmeme.org/archives/001701.html

In the discussion following this example it is suggested to use the PEAR method nextId().

alternatively you can switch to PEAR::MDB2 which provides a lastInsertID() method for this purpose ..

regards,
Lukas