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.

Hi Michael,

"Michael" <dimo414@gmail.com> wrote:

> 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/...increment.html

<cut>

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

On Wed, 21 Mar 2007 08:12:29 +0100, Axel Schwenke wrote:

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

--
For why should my freedom be judged by another's conscience?
-- Paul (I Corinthians 10:29)