Get next auto increment value using 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:

1. $query = mysql_query(SELECT MAX(id) as max_id FROM tablename);
2. $row = mysql_fetch_array($query);
3. $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:

1. Id country
2. 1 Italy
3. 2 Germany
4. 3 Greece
5. 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.

So, I looked all over the web for a solution, and found several similar scenarios described, but no answers. In fact, I came across several forums and help sites where it was actually stated as fact that you CANNOT get the next auto_increment. I knew this to be false, because this information is displayed in the table structure view of phpMyAdmin. So I scoured the source code of phpMyAdmin, and after many desperate combinations of GREPs, FINDs, and XARGSs, I finally came up with this  (assumes we’re already connected to the dB) solution is to use the “SHOW TABLE STATUS” query.

1. $query = mysql_query(“SHOW TABLE STATUS LIKE ‘$ tablename'”);
2. $row = mysql_fetch_array($query);
3. $next_id = $row[‘Auto_increment’] ;

“SHOW TABLE STATUS” produces a two-dimensional array with one row for each table, and a slew of columns: Name, Type, Row_format, Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, etc.

Author: Leo and Jamie


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: