Tag: MySQL

  • Calculate next auto-increment value for MySQL tables

    Sometimes it is necessary to determine the next auto-increment value that will be used by MySQL for inserting values into tables.

    First off, there are several different approaches to determine it. Most people use the MAX() function and simply add 1 to the value. Although this is fine is most cases, you may run into problems when forced to retain data consistency after deleting rows.

    Another approach is to determine the AUTO_INCREMENT value directly:

    SHOW TABLE STATUS LIKE 'your_table'

    In order to determine the next auto-increment value used by MySQL you simply have to get the value of the field called ‘Auto_increment’.

    Using PHP, this can be achieved like so:

    $result = mysql_query("SHOW TABLE STATUS LIKE 'your_table'");
$row = mysql_fetch_assoc($result);
return $row['Auto_increment'];