> On Sat, 2007-03-17 at 12:19 -0400, Mark wrote:
>> Robert Cummings wrote:
>>
>> > On Sat, 2007-03-17 at 09:43 -0500, Myron Turner wrote:
>> >> Colin Guthrie wrote:
>> >> > Philip Thompson wrote:
>> >> >
>> >> >> For auto increment values, you don't have to specify the id. For
>> >> >> example:
>> >> >>
>> >> >> INSERT INTO t_users (f_name, l_name, e_mail, b_date, pic)
>> >> >> VALUES ('$f_name', '$l_name', '$e_mail', '$b_date', null);
>> >> >>
>> >> >> Then to find the latest entry:
>> >> >>
>> >> >> SELECT user_id FROM t_users ORDER BY user_id DESC limit 1;
>> >> >>
>> >> >
>> >> > This is not the cleanest way and some databases do not actually
>> >> > increment auto id fields (e.g. they could fill in the blanks from
>> >> > previous deletes etc.).
>> >> >
>> >> > Much better is to use the function in the MySQL API to get the
>> insert
>> >> > id or if you really must use SQL, just run "SELECT
>> LAST_INSERT_ID();"
>> >> > which does much the same thing.
>> >> >
>> >> > Col
>> >> >
>> >> >
>> >> My own knowledge of mysql is about 5 years old and never really used.
>> >> But I was recently asked to do something that required some mysql
>> >> (noting too much, fortunately for me), so I've been doing some
>> reading
>> >> and am interested in questions that come up on the list.
>> >>
>> >> An earlier post called attention to a concurrency problem. Wouldn't
>> >> getting the last inserted ID from LAST_INSERT_ID()
>> >> suffer from the same limitations as any of the other solutions which
>> do
>> >> a select to get the last ID?
>> >
>> > No, it's a MySQL specific feature that is atomic with the insert and
>> so
>> > you are guaranteed that the the returned ID is the exact automatic ID
>> > associated with the most recent INSERT for the connection handle.
>> > Unfortunately auto increment is MySQL specific and so it isn't
>> > transferrable to other database engines.
>>
>> The problem is if the ID returned is not somehow linked to the
>> transaction
>> which inserted the row. If you have an insert followed by a select to
>> retrieve the ID, in the interrum, another user may be created by another
>> process and you will retrieve the wrong ID.
>
> I don't believe this is an issue for MySQL. The last inserted ID isn't
> stored for the database table, it's stored for the connection to the
> database. As such there's no race condition unless the developer does
> something silly, like issue another insert over the same connection
> before requesting the previous last inserted id. Just to be sure though
> the following is from the MySQL online documentation:
>
> ::::
> For LAST_INSERT_ID(), the most recently generated ID is maintained in
> the server on a per-connection basis. It is not changed by another
> client. It is not even changed if you update another AUTO_INCREMENT
> column with a non-magic value (that is, a value that is not NULL and not
> 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously
> from multiple clients is perfectly valid. Each client will receive the
> last inserted ID for the last statement that client executed.
> ::::
>
> http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
>
> Cheers,
> Rob.
> --
There in lies the biggest problem with LAMP, and that's MySQL. The
architecture of your methodology *only* works with MySQL, and not more
capable databases like Oracle, DB2, or even PostgreSQL.
If you rely on oddities of a particular system, then you are doomed to be
stuck with it or pay the price of redesign when you need a more capable
system.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
