Next Autoindex

[Michael]

I know this is probablly really, really simple, but I can't figure out
how to retreive the next autoindex in a table, so when I want to
obtain it, I've been inserting a new row, then finding the unique
index from some combination of other values that I know will be
unique.

So I would appreciate you're help in this regard:
1. What's the appropriate SQL query to get the next autoindex from a
table (say 'user').
2. How do I obtain that value in PHP (as in, what would I use as the
index in the array returned by mysql_fetch_assoc())

Thanks!

PS - I'm asking a PHP question in a MySQL forum because I didn't get a
response after several days in alt.php.sql - not because I'm a
clueless idiot who expects all MySQL users to use PHP.

[Axel Schwenke]

Hi Michael,

Post by Michael
I know this is probablly really, really simple, but I can't figure out
how to retreive the next autoindex in a table,

Nobody is doing *that* anyway. There is no reliable way to answer
"What AUTO_INCREMENT value will I get if I execute $INSERT now?".
Just think of race conditions with other clients inserting data.

The ususal way is to just do the INSERT and then ask MySQL
"What AUTO_INCREMENT did you create for my last INSERT?"

MySQL answers that with the LAST_INSERT_ID() function:
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

<cut>

Post by Michael
2. How do I obtain that value in PHP (as in, what would I use as the
index in the array returned by mysql_fetch_assoc())

PHP gives you another possibility: use the mysql_insert_id() function:
http://www.php.net/manual/en/function.mysql-insert-id.php

The difference between the LAST_INSERT_ID() SQL function and the
mysql_insert_id() API function is, that LAST_INSERT_ID() remembers
the latest generated AUTO_INCREMENT value, even if there were other
statements in between. OTOH mysql_insert_id() remembers the
AUTO_INCREMENT value of the last statement. If you do some SQL
statement that does not generate an AUTO_INCREMENT value, it will
reset mysql_insert_id() to 0.

Beware of the implicite connection sharing in PHP! If you use
multiple database connections in the same PHP script, PHP may share
connections (see here for an explanation)
http://www.php.net/manual/en/function.mysql-connect.php

This may spoil AUTO_INCREMENT values, even those from LAST_INSERT_ID.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

[Peter H. Coffin]

Post by Axel Schwenke

Post by Michael
2. How do I obtain that value in PHP (as in, what would I use as the
index in the array returned by mysql_fetch_assoc())

http://www.php.net/manual/en/function.mysql-insert-id.php
The difference between the LAST_INSERT_ID() SQL function and the
mysql_insert_id() API function is, that LAST_INSERT_ID() remembers
the latest generated AUTO_INCREMENT value, even if there were
other statements in between. OTOH mysql_insert_id() remembers the
AUTO_INCREMENT value of the last statement. If you do some SQL
statement that does not generate an AUTO_INCREMENT value, it will
reset mysql_insert_id() to 0.
Beware of the implicite connection sharing in PHP! If you
use multiple database connections in the same PHP script,
PHP may share connections (see here for an explanation)
http://www.php.net/manual/en/function.mysql-connect.php
This may spoil AUTO_INCREMENT values, even those from LAST_INSERT_ID.

IIRC, it's not impossible to make mysql_insert_id() all but
completely safe at the PHP interpreter level, and there was some
noise about doing so a few years back, so getting into the habit of
using mysql_insert_id() for preference over rolling your own with
LAST_INSERT_ID() might actually pay off someday.