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.
If you have no users, it is unlikely, the more traffic your site has, the
more likely the problem. It is a *bad* design and any 1st year CS student
should know better.
>
>> I assume if you are completely in control
>> of the database, you could create a lock file using flock() and remove
>> the lock once the Id is retrieved using any of these methods.
>
> You can, but locking is expensive, especial if there's a lot of activity
> in the table.
MySQL locks too much already, one more lock won't hurt :-)
>
>> I guess what I'm wondering is whether the simplest suggestion is the one
>> that would use the email address as a condition in the WHERE clause to
>> extract the ID?
>
> No.
insert into the_table (..., email,..);
select user_id from the_table where email = '...';
Is probably the best way given the nature of the table and skill level of
the developer asking the question.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
