innerlogic

Recently, I needed to get the auto increment value for the next inserted row in a MySQL table.  Generally, this is not a good idea and you should stop reading right now.
You may end up with duplicate entries in your application, which is not a good thing.
At a first glance you may think: “I can get the top id value from my table, add 1 and that’s all, I got the next auto_increment value I need.”
Something like this:

$query = mysql_query(SELECT MAX(id) as max_id FROM tablename);
$row = mysql_fetch_array($query);
$next_id = $row[‘max_id’] + 1;

Using the above example, you got the current max auto_increment id plus 1.
As you may already notice, this is wrong. Auto increment values use unique ids.  Imagine the following table:

Id country
1 Italy
2 Germany
3 Greece
4 Turkey

If you delete the record 4 and run the above script, you will get “4” as next_id.
MySQL will use “5” as next_id.
The solution is to use the “SHOW TABLE STATUS” query.

$query = mysql_query(SHOW TABLE STATUS LIKE tablename);
$row = mysql_fetch_array($query);
$next_id = $row[‘Auto_increment’] ;

The “SHOW TABLE STATUS” command returns a two dimensional array with the “Auto_increment” value among others.