Geek : MySQL: Get Next Auto_increment

** It is not recommended to use this method for generating foreign keys for lookups. In a multiuser environment with simultaneous requests the results will be utterly useless and unreliable. OK? **

So I was working on the preview feature of the home grown content management system for this site, and I came across an interesting problem for which my friend Google had no answers.

The way the preview feature works is that when editing an entry, you hit the PREVIEW button, and the script posts the edited data unto itself and then writes it to a temp file named {section}_{entry ID}.php. When the posted page loads, it opens a new window with the template for the given section, and the POST data is processed in the content well, just as any other entry would be.

The problem is that if you want to create a NEW entry, you don't have an entry ID to assign to the tempfile name, and therefore the preview feature does not work. So I needed a way of finding out what MySQL would assign as the next auto_increment entry ID if I were to actually insert a record. My first thought was to simply get the highest existing ID from the table and add 1, but that is not a perfect solution. For one thing, there is the possibility that another author might insert a new record during the time that I'm previewing, which will cause some confusion -- we might end up with two instances of the temp file overwriting one another. No good. Also, the highest existing ID + 1 is NOT necessarily the next auto increment ID, because when entries are deleted, the entry ID is not reused. So it's conceivable that if your highest ID is 500, the next ID might be 501, or it might be 5001 for all we know.

I was on a mission.

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)

And the rundown:

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

So what I'm doing here is using mysql_fetch_assoc() to get the "Auto_increment" value.

And there you have it. Our next likely entry ID, without inserting anything.

edited by JD on 2/14/06

Oh the drama

Nice, but this is even better:

SHOW TABLE STATUS LIKE "table_name";

Hey thanks!

SHOW TABLE STATUS LIKE "table_name";

works like a charm and was exactly what I needed. Thanks Jamie (and Nate).

thanks Nate. That's MUCH better!
You're welcome Michael.

Nice work! Your page came up first in my google search for this exact problem.

How can I create a table out of the SHOW STATUS TABLE information ?
e.g.
Create table stati as SHOW STATUS TABLE;
does not work.

Not at all sure what you're trying to do but I don't think it works.
RTFM: MySQL Manual

If I use "SHOW TABLE STATUS LIKE "table_name";" in a phpMyAdmin, I can see auto_increment number, but when I use it with the rest of above code on a php page, I get nothing for "$qShowStatusResult", $entry_id and "$qShowStatus". Do you know how to get auto_increment value on php webpage? Thanks

Here's an example using "SHOW TABLE STATUS LIKE 'table_name'" (again, we've already established a connection to the database. Consult the PHP manual if you don't know how to do this.):

<? $tablename = "tablename"; $next_increment = 0; $qShowStatus = "SHOW TABLE STATUS LIKE 'tablename'"; $qShowStatusResult = mysql_query($qShowStatus) or die ( "Query failed: " . mysql_error() . "<br/>" . qShowStatus ); while ($row = mysql_fetch_assoc($qShowStatusResult)) { $next_increment = $row['Auto_increment']; } mysql_free_result($qShowStatusResult); echo "next increment number: [$next_increment]"; ?>

Thanks guys! Works good except there's a slight omission in the final example.
The line starting with

$qShowStatus = "SHOW TABLE STATUS LIKE 'tablename'";

should read

$qShowStatus = "SHOW TABLE STATUS LIKE '$tablename'";

cheers

Dear Friend.
Thank you very much for the solution for the problem of getting the next auto number.
I really appriciate your kindness for sharing it.

sincerely:
Mikiko

Many thanks

I had the same problem.

From the code above I put together a function
to get the next number.

Maybe it will be usefull to someone.

-I have a function connect_db()
(I am shure you can guess what it does.)


function next_auto_incriment($tablename)
{
connect_db();
//$tablename = "downloads";
$next_increment = 0;
$qShowStatus = "SHOW TABLE STATUS LIKE '$tablename'";
$qShowStatusResult = mysql_query($qShowStatus) or die ( "Query failed: " . mysql_error() . "
" . qShowStatus );


while ($row = mysql_fetch_assoc($qShowStatusResult)) {
$next_increment = $row['Auto_increment'];
}
mysql_free_result($qShowStatusResult);

return $next_increment;
}

Thats what i was searching for :D tried so many possibilities. now it works. Thanks

Why do you have this in a while loop? It's returning only one row...

$row = mysql_fetch_assoc($qShowStatusResult);
$next_increment = $row['Auto_increment'];

But good, learning about SHOW TABLE STATUS is handy!

try this:

select max(id) from my_table;

Thanks peter, but you didn't read the entry.

Wow. This was more helpful than the MySQL manual. Thank you so much. <3

wow very very nice.

i was with the exactly problem doing a Portal for a brazilian university using JSP and i that came up in my first google search... thanks a lot

Fantastic - thanks for this - I spent 2 days trying to sort this problem. Then googled to here and it was sorted in 5 minutes.

"There is no "nextval", please use your own function" - mysql team. Damn. That's the one of reasons why avoid mysql when it is possible. Thank You very much for this tutorial.

Great Job! Thanks everybody for your commments, They were truly useful! :)

thanks, this helped.. haha. go corporate work!

oh yeah, my third cent, would be to make the actual title of these dynamic pages the title of the article (MySQL: Get Next Auto_increment). i found it on google easy, but it'll always help. worrrrrrrrrd

Good tip, joe. I've taken your advice.

Thanks for this! Found this from google, worked like a charm.

Found from google, godly information.

Thanks! I had the same problem ... :)

haha much the same everyone else here! that helped so much for message board cheers! :D

This is a nice solution, and I thank you for posting it.
However, you could still have the problem of two records with the same ID and could still end in overwriting records.

Keep in mind I'm only using the retrieved ID for preview purposes -- I don't actually insert the ID value into the table. So the possibility exists that I might be previewing the wrong entry, but overwriting is not an issue.

I just want to thank you for this solution.
I am very thankful for that, and google of course... my way of finding this page.

And that "SHOW TABLE STATUS LIKE 'tablename'" is very good.
Keep up the good work.

OK. Just to clean it up a little, I'm updating the original code snippet to include everyone's helpful comments. Thanks! Here it is, again with a pre-established DB connection:

<? $tablename = "tablename"; $next_increment = 0; $qShowStatus = "SHOW TABLE STATUS LIKE '$tablename'"; $qShowStatusResult = mysql_query($qShowStatus) or die ( "Query failed: " . mysql_error() . "<br/>" . $qShowStatus ); $row = mysql_fetch_assoc($qShowStatusResult); $next_increment = $row['Auto_increment']; echo "next increment number: [$next_increment]"; ?>

Awesome. *Execatly* what I was looking for. Kudos. I think you may have to put a "$" in front of ".qShowStatus" on line 5 your example above.

edited on 2/22 by Jamie: Done. Thanks, OG.

You're my hero :) First Google result for "php get next auto_increment" too...thanks!

First site on google when searching for "mysql 'get the auto_increment value'" Thanks

Thanx!!!!

Thanx, thanx, thanx!! It's second result for "next autoincrement mysql" query in google. It's realy works and it's realy healpful. I also have one tip: If some times U need to save some file using name autoID.ext where autoID is next autoincrement, use a file locking function (open and lock file):

[code]
open&lock_file();
get_nextautoincrement();
save_uploaded_autoID_file();
makeSQLquery();
unlock_file();
[/code]

It's helpful when two instance of your script runs in same time - you make sure that a uploaded file an database entry have same ID.

maciejkiner.oneclick.pl

Nice work. Thanks for sharing.

This is precisely the problem that I've been facing. Brilliant.

Excellent information, that is what I needed. Found you from Google!

How do I do the same using Java and MySQL?
When I ran the query, SHOW TABLE STATUS LIKE '$tablename', it was erroring out. Is there any diff. with the command in Java?

Is this transaction/thread safe? (Obviously, in a very populate application)
Can you rely on the next auto increment to match the one used in your subsequent INSERT statement.

This is why Oracle uses Sequences rather than embedding them into the table.

I am currently doing this.
INSERT INTO mytable (session) VALUES ('$session');
SELECT max(id) from mytable where session=$session;

Any thoughts, am I being too cautious?

Just answered my own point.

Use mysql_insert_id();

Don't know why you would want to predict the number in advance, but if you want to ensure your application is thread safe I'd advise the above.

great solution... but some of your veiwers/readers are a little slow. :)

i'm rather experienced with mysql doing lots of databases with lots of users online at same time... the only situation i need to predict next auto_increment value is if i have to insert/update the id of a fresh record from A-table to one or more fields from B-table (eg. A-table - a new house form a real estate agency database; B-table - pictures table which has more records for same house) the best sollution is to begin a transaction using

$conn=mysql_query("START TRANSACTION"); //lock transaction
$conn=mysql_query("SHOW TABLE STATUS LIKE '$tablename'");//run next-id-returning-query
$nid=mysql_fetch_assoc($conn);//fetch
$conn="INSERT INTO `house` (data1, data2) VALUES ('data1', 'data2')";//insert new house
$conn="INSERT INTO `pictures` (id, pic, thumb) VALUES ('{$nid['Auto_Increment']}', 'pic', 'thumb')"; //in a loop for each picture of that house
$conn=mysql_query("COMMIT");//write data & unlock transaction

this means that since you started the transmision no other user will increment the next_auto_increment value by recording a new house... just don't forget to commit changes...

i forgot to tell you that u can use

$conn=mysql_query("select max(last_insert_id(auto_increment_field_name)) from `table`");

and then fetch the output for manualy setting the id value to last_insert_id+1 but i'm not sure if in default settings of the server it allows you to actualy do that... this is useful if you have a lot of data in your table with lot of missing records (eg field id's: 1, 45, 200, 1400 are in sequence because others have been deleted) and you are forced to use a for-loop (because you have to extract records by id ((stored and deleted at same time)) from other places) and it's faster doing a a big selection using the between criteria and excluding localy unwanted then using where id=xx for each id because this way ure a using clients resources for each client not servers for thousands of clients

post before is usefull in case inner join is not a solution in your case :)

what about 2 simultaneous insertions for example? will your system give two same ID ? or mixed data?
You should use Transaction for safety, if more than one person can act simultaneously. You can use InnoDB table with transaction for example

great job Jamie ! thanks

great bit of code was exactly what i was looking for. i wrote a clunky thing using MAX() which worked as long as no-one deleted the last record, doh!

cheers, you're a star!

Hi, this is THE BEST OF THE BEST post and you guys are my heroes.
Tnx alot!!!!

Thanks Jamie and all. This was something I needed and it seems to work fine. Worth noting (not sure if this is a new requirement as of php5--which is what I'm using) but the script's opening tag needs to be specified as:

LIKE is slow .. use:
$sql = "SHOW TABLE STATUS WHERE Name='TABLE_NAME'";

Clever solution to what should be a non-problem. Autoinc primary keys should be for internal db use only and have no meaning outside the database: that is a basic principle of db design theory.

Now neither transaction nor table locks are going to stop SHOW TABLE STATUS, indeed that is the prescribed method for examining table lock status. So any number of concurrent users are going to see the same autoinc value. Of course only one will be able to use it correctly, all others will then end up with the wrong value.

The only method to use is that described in the MySQL documentation: do your insert then query for last_insert_id. This is not the documented method for no reason, it is because it is the one that will work.

What continually amazes me is why so many of you continue to try to find a way around fundamental relational database behaviour. This is not peculiar to MySQL, it is normal behaviour in all the RDBMS that I know of.

When you do want to predict and control the next id, eg for an unbroken sequence of invoice numbers, then you have to create your own process which you can use either transactions and/or table locks with. Otherwise, it is what it is so just live with it.

Arrogant, self-righteous, and condescending. And you didn't read the thread either. Glad to have met you, David!

What's the matter, Jamie? Can't take pertinent criticism?

And yes, I read the thread, as I did the one on PHPBuilder where I found the link to your blog.

Calling me names is not going to change the truth of my comments. I'm not the only one to point out the flaws, TimTime has already done so. I just explained the problem in detail.

I don't want to getting into some flaming row - it's your blog after all. I just worry about the poor folk who naively adopt your solution without realising the major bug they are setting up by doing so.

Your criticism is unfair. There is no bug here. I don't want those "poor folks" to use this information in any way that I am not. And I am using it, essentially, for internal db use. It's a PREVIEW feature. For a website that only I have access to. There will never be anyone else getting the same number, so it's a non-issue. That's why I suggested perhaps you didn't read, or didn't soak in, the whole thread. But I appreciate your contribution -- I'd just rather it be delivered in a more diplomatic way.

While this works, it does not work in all cases. Also, to answer the question of "why would you ever do need this?" I have that as well.

I have a table that stores the location of pictures along with its width, height, and a timestamp.

The picture name is the same as the Auto_incremented ID (ProjectImageID in this case). This is done for who knows what reason, but I did not make it this way, but it what I inherited. Anyways, the last column in the table is Timestamp where I put store NOW() for record keeping.

Whenever I run this solution, I get the timestamp value returned and have yet to been able to get the ProjectImageID increment value to return.

You rock! thx a ton!

Just want to say thanks for your original postings. I'd been looking for this code for a while but everyone kept telling me it could not be done, although like you I reasoned that if phpAdmin could display the number then it must be available somewhere.

Thank you very much for that tip! I didn't know about table status. This page came up in Google when searching on how to change AUTO_INCREMENT.

Thanks very much for that tip !

Hey man!! thank u! it worked perfectly!! just what I needed!

Not a solution for this problem, but a related useful function is:
mysql_insert_id()
which returns the autoincrement-number from the last executed insert-command.

Still useful 5 years on!

Excellent and useful piece of coding.

I have used it for my site drurl.net, which allows people to shorten their urls.

I needed to know if someone is linking to a number that has been permanently removed or not yet been created, as I delete a lot of urls there may be a big gap at the end of the database auto_increment and +1 to the last ID number does not work.

Your code allows this exactly.

Cheers.

David

PS. I hate captcha

your Name

your Email (optional)

your URL (optional)

your Comment

Enter the Code shown below

Phonetic spelling (mp3)