On Thu, Sep 20, 2001 at 11:47:05AM -0700, Joe Crawford wrote: > noah wrote: > > When I insert a new record into a MySQL table (via PHP), I use MySQL's > > auto-increment to create a unique id number for that record. What I'd like > > to do is immediately have access to the id number in PHP. I could use a > > separate select statement, but since I don't know the unique id, I run the > > risk of returning multiple records, rather than just the one I just inserted. > > > > Is there anyway to immediately have access to the id number generated when > > the record is inserted? > > I believe mysql_insert_id() is a php function to do what you want -- > documentation here: > http://www.php.net/manual/en/function.mysql-insert-id.php > > I'm not sure if there's a native mysql way to do what you want though. > :-\ You can almost do this in native MySQL by locking the table for writes while you add the new record. You can use LAST_INSERT_ID() if your field is auto_incrementing, but the general solution is: LOCK TABLES Animals READ; /* this assumes we're creating a complete * record with a single * insert statement */ SELECT MAX(Id)+1 FROM Animals; /* ... and store it in scripting var $__id__ */ INSERT INTO Animals SET Id=$__id__, Name="Fox", Colour="Brown", Speed="Quick"; UNLOCK TABLES; You have to store the ID in a scripting language variable, then interpolate it into the INSERT statement. How that happens is dependent on the scripting language you use, so I've left that bit out. Have a look at a MySQL manual for the difference between a READ and a WRITE lock, and consider whether it's OK for other client processes to be able to read a partial record. -- Andrew Chadwick, UNIX/Internet Programmer, PR Newswire Europe, Oxford -- The views or opinions above are solely mine and are not necessarily those of PR Newswire Europe. The message may contain privileged or confidential information; if you are not a named recipient, notify me, and do not copy, use, or disclose this message. <andrew.chadwick at prnewswire.co.uk>.