next auto_increment

[Stooney]

say i have a column named id.  How would I go about retrieving the next auto_increment value without actually inserting anything to the table yet? 

I have two tables, and one table will save data with a 'reference number' which matches the auto_increment 'id' in the first table, so I need to know what that will be before saving any data.  Hope it makes sense.  thanks in advance

[toplay]

Some use describe and some use select. Here's an example:

SELECT id FROM table_name ORDER BY id DESC LIMIT 1;

But it's not safe because the value can change between the time you grab the id and the time you insert the row in the table.

It's always best to insert the row first whenever possible, then use mysql_insert_id() to find what the id written was.

[Stooney]

thnx.

and [i]SELECT id FROM table_name ORDER BY id DESC LIMIT 1;[/i] isn't too reliably due to the fact that say someone deleted a record (this script does) then auto_increment can be ahead of what the last 'id' is. So i'll go with mysql_insert_id()  :D

[toplay]

Another approach is not to delete rows but simply mark them as deleted using a column (and all your code looks at that column to determine whether to use it or not).

This technique is used especially in accounting/financial applications where history is important and is typically not deleted for various reasons including auditing purposes.

[doni49]

You could have it SELECT ALL the rows, returning only the id field.  Get the array.  Then use MAX to find out the highest number.

http://us2.php.net/manual/en/function.max.php

EDIT:  DUH that would have the same effect as the first reply.  But you'd only have that problem IF the LAST row was deleted.

[toplay]

FYI - There's a MAX() MySQL function too:

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column.html