Get Auto Increment value from MySQL table

Many a times we require the next Auto Increment value from a MySQL table. Most of the people would do something like this:

$query = "Select MAX(id) from users";
$res = mysql_query($query);
$row = mysql_fetch_assoc($res);
$maxid = $row['MAX(id)'];
echo $maxid;

This would give correct results but if we delete the latest row, it will start to give wrong results.

We’ve got another query which can be used to get correct ‘Auto Increment’ values from the table.

$query = "SHOW TABLE STATUS LIKE 'users'";
$res = mysql_query($query);
$row = mysql_fetch_assoc($res);
$maxid = $row['Auto_increment'];
echo $maxid;

This will always give correct values for Auto Increment.