[thelist] MySQL generated id

[Andrew Chadwick]

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>.