MySQL: Get next auto increment value

by leo on May 30, 2007

MySQL
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.


13 comments

Why would you choose to use the statment SHOW TABLE STATUS LIKE ?
specificly the LIKE.

As to chicago’s comment, why not fight fire with fire?

by Web Design Canada on June 17, 2008 at 4:00 am. #

SHOW TABLE STATUS query is not transactioned query. So do not safely using it in this case

by Darkside on December 13, 2008 at 12:18 am. #

i’m not mysql genius but it solved my little problem.
thanks

by andre on August 1, 2009 at 8:08 pm. #

THANKS!!!

by Gushu on September 23, 2009 at 11:23 am. #

Hi,
you could use the direct select like this:

select auto_increment from information_schema.TABLES where TABLE_NAME=’tablename’ and TABLE_SCHEMA=’basename’;

by Felippe on November 7, 2009 at 6:46 am. #

Hi
The comments you have made are really useful. In fact exactly what I was looking for as I have the same problem. I am trying to solve it a different way however.

$count_query = “select count(location_id) as id from location where location_id is not null”;
$count_result = mysql_query($count_query);
while($c = mysql_fetch_assoc($count_result))
{
$count = $c['id'];
}

I use count(field) name to get all the auto_increment values that are not null. I then somehow plan to use the count function to work through retrieved records. I am just having trouble doing this at the moment.

by Guy Baker on January 1, 2010 at 1:44 am. #

NICE

VERY NICE ARTICLE AND I WANT TO PUT THIS ARTICLE IN MY BLOG.

http://www.mywolfbook.com

by Conner on February 27, 2010 at 5:41 pm. #

hi very nice artical what i am looking i got thanks i will use this artical in mystyle at http://www.oneanall.com/mysql

thanks
ranjeet singh (freelancer)

by ranjeet on July 9, 2010 at 3:27 pm. #

[...] Artikel zu finden unter: http://blog.innerlogic.gr/mysql-get-next-auto-increment-value/ Dieser Eintrag wurde veröffentlicht in MySQL, PHP und getagged abfrage, auto_increment, mysql. [...]

by MySQL: Get next auto_increment on April 26, 2011 at 12:51 pm. #

Nice article, I viewed it yesterday and came back again today!

by Kostas Tsakas on August 4, 2011 at 12:07 pm. #

I got more PHP samples from..
http://www.rapidsharefinds.com

Buwa

by buwa on September 3, 2011 at 7:27 pm. #

Thanks a lot.

by gige domingo on September 26, 2011 at 11:51 am. #

Very Nice article………………..

by Anil Tiwari on October 4, 2011 at 2:42 pm. #

Leave your comment

Required.

Required. Not published.

If you have one.