markw@xxxxxxxxxxxxxx wrote:
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.
I too thought the same thing, but was corrected when I starting working
on a redesign for the billing system for the company that I am working
for. They use PostgreSQL and we do have a auto incremented unique id
field. It isn't call AUTO_INCREMENT, but rather a sequence value. It
is a value that is controlled by the default value entry.
It then runs this:
nextval('customers_customer_id_seq'::regclass)
and uses this as the value of the id field for the newly inserted row.
Then instead of running a command like SELECT LAST_INSERT_ID(); you have
to do some other stuff like this:
$SQL = "SELECT currval('{$table}_{$column}_seq');";
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
