...

View Full Version : How to get the Next Auto Increment number in Mysql



Bhagya
08-25-2006, 10:47 AM
Dear Friends.

I want to get the How to get the Next Auto Increment number in Mysql using PHP code ?

can i get the Next Auto Increment number in Mysql Usiing PHP code??

Pl help me.

Thanks
Casper.

Alize
08-25-2006, 11:55 AM
Im not quite sure what you mean...From what i understand i think you mean a counter? You could add a counter to your query and put it in a while loop so it auto increments after every load...

Here is an example:


$counter=0;

while($counter =< 50)
{
$query = "SELECT * FROM tablename WHERE id='$counter';
$result = blah_blah_blah;
}

Mwnciau
08-25-2006, 12:05 PM
Assuming id is the auto_increment column:


$query = mysql_query("SELECT MAX(id) FROM `table`");
$results = mysql_fetch_array($query);
$cur_auto_id = $results['MAX(id)'] + 1;

ronverdonk
08-25-2006, 12:53 PM
Nonono! You can ONLY get the next_id by using it after an insert or update statement.

The MAX() will not work, it will only give you the highest number used, and that plus 1 is not alway the next assigned number. Example:
You have a table with auto_increment column 'id' and values:
1
2
3

The MAX(id) will give you 3.
Now you delete 3. The MAX(id) value will give you 2.
After inserting a new row, the table will be:
1
3
4
and the MAX(id) will return 4.

The only way to get the the last insert id is after you have issued an UPDATE or SELECT statement, and you get it either via a LAST_INSERT_ID() or mysql_insert_id().

Have a look at the MySql documentation:
http://dev.mysql.com/doc/refman/4.1/en/getting-unique-id.html
http://dev.mysql.com/doc/refman/4.1/en/mysql-insert-id.html


Ronald :cool:

bustamelon
09-23-2006, 05:39 AM
http://blog.jamiedoris.com/geek/560/

PassiveSmoking
09-23-2006, 08:27 AM
You could try SELECT * FROM 'table' ORDER BY 'id' DESC LIMIT 1 to get the mose recently added row, get the id number from that and add 1 to it, but if someone else happens to be running the script at the same time and inserts a new row in the window between you getting the number of the last record and the time you actually use the number then the number you got will be wrong.

The only reliable way of doing it would be to INSERT a dummy record into the table, get the ID of it with LAST_INSERT_ID() or mysql_insert_id() and then UPDATE the newly created row with the actual data you want to store.

ronverdonk
09-23-2006, 10:42 AM
You could try SELECT * FROM 'table' ORDER BY 'id' DESC LIMIT 1 to get the mose recently added row, get the id number from that and add 1 to it, but if someone else happens to be running the script at the same time and inserts a new row in the window between you getting the number of the last record and the time you actually use the number then the number you got will be wrong.

The only reliable way of doing it would be to INSERT a dummy record into the table, get the ID of it with LAST_INSERT_ID() or mysql_insert_id() and then UPDATE the newly created row with the actual data you want to store.

Is this contribution different from the one added at August 25?? Or do I miss something here?
And you should definitely NOT do the first thing in this quote. Read my entry again and you know why not.

Ronald :cool:

NancyJ
09-23-2006, 10:48 AM
just to reiterate what has been said previously - the correct answer is here:
http://blog.jamiedoris.com/geek/560/



<?
$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]";
?>

ronverdonk
09-23-2006, 12:28 PM
just to reiterate what has been said previously - the correct answer is here:
http://blog.jamiedoris.com/geek/560/



<?
$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]";
?>

Thanks NancyJ! I didn't know this. Anyway, using MAX() is wrong.

Ronald :cool:

guelphdad
09-23-2006, 01:50 PM
Read this article (http://www.guelphdad.wefixtech.co.uk/sqlhelp/lastinsertid.shtml) on using last_insert_id in mysql. You don't need to use mysql_insert_id because the use of last_insert_id is already used in your insert in the next table.

You don't need to know the value of the auto increment before you use it. THe only reason you should need it is to insert data into a secondary table and that information is available after you do the insert.

ronverdonk
09-23-2006, 03:20 PM
Read this article (http://www.guelphdad.wefixtech.co.uk/sqlhelp/lastinsertid.shtml) on using last_insert_id in mysql. You don't need to use mysql_insert_id because the use of last_insert_id is already used in your insert in the next table.

You don't need to know the value of the auto increment before you use it. THe only reason you should need it is to insert data into a secondary table and that information is available after you do the insert.
Well I DO want to know it beforehand, because I create a unique token based on that number and I want to know it before I insert the row into the database. (don't tell me there are other ways of creating that token, because I already know that).
Btw: the link to the article is not much use, it is a broken link.

Ronald :cool:

felgall
09-23-2006, 10:47 PM
You can't know it beforehand. If you have two (or 2000) people all at approximately the same place in the processing at the same time then the same current value will be returned for all of them but only one of them will actually get that number.

To do it properly you insert the record first before creating your unique token as otherwise you can't guarantee that the token actually is unique. You can always delete the record again if it turns out that it wasn't needed after all and you can always update the record after creating it if you want to store the token in it.

guelphdad
09-24-2006, 12:48 AM
the only reason you should be using an auto increment column in your table is because there are no suitable natural keys (columns or combinations of columns) to create as your primary key. in place of this you create a surrogate key.

if it is necssary for you to create an artificial key it helps that it is an auto_increment key specifically so you can take advantage of getting that value uniquely from other users and installing it as the foreign key in the next table.

you should not rely on the auto increment key for any other reason, you thus do not have to know the value before you use it, you are using it as the foreign key in another table.

if you are trying to create a unique token based on the value of the auto increment key you should not be doing so.

sorry but that isn't standard database practice, the use of the auto increment column as described above is.

guelphdad
09-24-2006, 12:50 AM
just to reiterate what has been said previously - the correct answer is here:
http://blog.jamiedoris.com/geek/560/

I'd say it is AN answer, just not the correct one. How are you guaranteed that value won't be used by another user before you use it?

Fumigator
09-24-2006, 03:21 AM
The problem with that article is the author admits it can sometimes fail, but he doesn't care if it occasionally fails for his particular application. But then others reading the article will try to use it to assign foreign keys in other tables, which of course will have disasterous results on those times it goes wrong.

I'd never use that technique personally.

NancyJ
09-24-2006, 08:02 AM
I'd say it is AN answer, just not the correct one. How are you guaranteed that value won't be used by another user before you use it?

The OP's question was how to get the next autoincrement number - and that IS the answer. He doesnt specify what he wants it for and its not for us to speculate or for us to tell him whatever he wants the information for is wrong. For all we know his application could be making informational data about mysql tables.

guelphdad
09-24-2006, 12:37 PM
and you know that they will be using it to insert a row right? I can make an educated guess that that is what it will be used for. Using select max(id) is just as incorrect of a usage but that doesn't stop people from using it (check out how many posters in this thread alone thought it was an acceptable way of getting that info) Why steer people down the incorrect path?

NancyJ
09-24-2006, 01:27 PM
and you know that they will be using it to insert a row right? ?
You dont know that at all - at no point did the OP say thats what they would be using it for. And it is more accurate than select(max) - since select max will not take into account any rows that have been deleted from the bottom of the table. Getting the next autoincrement value does.
You dont know the circumstances of his application or the purpose for which he wants the information. You are jumping to conclusions and being judgemental based on those assumptions.
The OP asked a simple question and the correct answer was provided to him.

guelphdad
09-24-2006, 06:44 PM
Nancy you have been around this discussion board (and I'm sure others) to know that 99.9999999999999% of the time it is exactly why the question is being asked. Because people want to know the id so they can use it on their insert instead of knowing the data after the fact.

There may be that one in 10,000,000 chance or whatever it is being used for something else and in that case yes it would be okay to use the method described above.

But most people won't and then they will leave this thread thinking that it is any safer to use than using select max(id) and essentially it is not.

I'm not jumping to conclusions. I'm basing my opinion on the thousands of threads I've read and those that I've answered. I've never seen anyone ask the question for any other reason when they've been asked to elaborate on their need to know.

NancyJ
09-24-2006, 09:20 PM
Nancy you have been around this discussion board (and I'm sure others) to know that 99.9999999999999% of the time it is exactly why the question is being asked. Because people want to know the id so they can use it on their insert instead of knowing the data after the fact.

There may be that one in 10,000,000 chance or whatever it is being used for something else and in that case yes it would be okay to use the method described above.

But most people won't and then they will leave this thread thinking that it is any safer to use than using select max(id) and essentially it is not.

I'm not jumping to conclusions. I'm basing my opinion on the thousands of threads I've read and those that I've answered. I've never seen anyone ask the question for any other reason when they've been asked to elaborate on their need to know.

Guilty until proven innocent?

marek_mar
09-24-2006, 10:41 PM
...99.9999999999999% of the time [...] one in 10,000,000 ....
98.45978263854967% of statistics are made up on the spot! :p

guelphdad
09-24-2006, 11:28 PM
Guilty until proven innocent?
From my previous observations on the subject? Then yes!

shihkuang
11-03-2006, 09:08 PM
Hi Nancyj & guelphdad,

I came to this forum because I was looking for the answer what NancyJ has provided. I guess I am the one in 10,000,000 lucky person. The reason I want to get the auto increment number is that I need it before I send it to varify with third party gateway. The original codes only send session id to them. However I need the order id to sync between my ordering system and the third party gateway. Anyhow.. Thanks Nancyj, it took me an hour to look for this answer.

Cheers~

cwoodcox
10-25-2007, 06:51 PM
quite simple actually. run this query.

SHOW TABLE STATUS LIKE 'table_name'

the auto increment value that will be used next is in the 'Auto_increment' column.
ex:


<?php
$sql = "SHOW TABLE STATUS LIKE 'table_name'";
$result = mysql_query($sql);

$row = mysql_fetch_array($result);
$next_id = $row['Auto_increment'];
?>

roadzy
10-26-2007, 07:07 PM
I could be totally off but could you just read them in so that it goes through all the ids... ie:

1
2
3
6
7
8
23

then the last one will be id 23 so you could just add 1 to that knowing that will be the next in line for it to be set to. You would do this when you actually submit it. I'm sort of new at this so not sure if this will help or not.

aedrin
10-26-2007, 07:35 PM
I wonder how many more people will reply to this topic with the same 2 answers.

(Both of which should never be used)

CFMaBiSmAd
10-26-2007, 08:09 PM
Edit: I have a different answer, that works, that is actually used in real world applications, that was already suggested a couple of times in this thread.

Since this old thread has been resurrected, I'll chime in here.

Starting with shihkuang's post. You are not actually just displaying it, which is what NancyJ is suggesting. You are using it and in fact sending it as an order id. You expect that this as yet to be assigned value will be available and be the same for that specific order after you process the payment through the gateway. However, as has already been stated a number of times in this thread, if you have concurrent visitors to your site, you cannot guarantee how many of the visitors will get the same "next id" value, nor which visitor will actually get that value when the data is finally inserted.

So, you are not the 10,000,000th lucky person, because you are not simply displaying this number as the next id available on a database status screen.

cwoodcox, the problem is not being able to get the next id at any point in time, but how it gets used.

PassiveSmoking actually suggest almost the right way in post #6, about inserting a dummy record. However, if you have someone that has started an order (or a sign up sequence or anything else where you need to keep track of where they are in the sequence), you should have inserted an actual record, that is marked as "pending." This "fixes" the record's id number. Then all the following steps can reference that unique, fixed, id number, no guessing about what the number actually is going to be. Once the order (or what ever is being addressed) gets paid (or completed), the existing record is simply changed from "pending" to "paid" (or whatever status you need.) If the order (or sign up sequence) gets canceled, just delete all "pending" records that are over some specific age. This is how every working real world application is written.

If this is anything from just a site membership sign up process, where an id number is needed in a link or as a member ship number... through to an actual ecommerce application, just do it the right way, with no guessing about what the next id will be, make it a known id by actually storing the current data and status in it.

websquid
02-20-2008, 12:24 AM
i know this thread is getting old but the answer is simple

first the reason why you shouldnt try to increment values from the id field etc...

if you have say a list of clients like so...

1 fred
2 bill
3 sufisdhf
4 isduhfsiduhfsdf
5 iudhfsidufh
6 weiruwer

and if you delete from 4 -6 you'll be left with the highest id of 3, but the auto_increment will really be 7

$link = mysql_connect( "localhost", "user", "pass" );
mysql_select_db( "mydb" );
$query = mysql_query( "SHOW TABLE STATUS LIKE 'user';" );
$result = mysql_fetch_object( $query );
echo $result->Auto_increment;
mysql_close( $link );

you can get alot of other information from this command, just do a vardump on the result to see them all

Ultragames
02-20-2008, 01:17 AM
i think what a lot of people are getting at is this:

There is a difference between how something should be done, and weather it should be done.

Many of the posts here are correct, no matter how you get the current, or next auto increment, it will never work 100%. What you need to do is find a way around that this fits your project.

If your relational tables being 0.0005 seconds behind your main insert is okay, then just insert the data and get the ID it was issued. If it is very very important that your relational tables be entered at the same time that the main data is entered, then look into giving the main table (the one being issued the ID) an 'active' setting. Set it to false first, then once your relational tables have been entered, change it to on.

There are many ways to get around many problems, but you always need to consider if what you are doing should be done.

o0O0o.o0O0o
02-20-2008, 03:31 AM
Can anyone descibe the conclusion of this long discussion in one post , rather than reading all 28 posts :)

Ultragames
02-20-2008, 03:34 AM
I think my last post there says it all. There are a few (only one decent) way to do this given through this thread, but the problem is weather you should or shouldn't do it at all. And in this case, you shouldn't.

bustamelon
02-25-2008, 05:09 AM
the author admits it can sometimes fail, but he doesn't care if it occasionally fails for his particular application

Hey y'all. I'm the author of the "solution" in the link, http://blog.jamiedoris.com/geek/560/. I found this thread in my referral logs. ;)

To clarify for those that didn't read it, I **used to** use this method in my own personal CMS backend thingamajig, as the ONLY user, and ONLY to get a more or less arbitrary ID that was used in a content preview feature. This ID was used temporarily, then thrown out. It was never referenced in the database at all.

So, yes, it's sorta-kinda true that I 'admit it can sometimes fail' (thank you for so barbarously putting words in my mouth :cool:) but it never actually failed in my particular application because I was the sole administrator/author. I took pains to explain this to the naysayers in the article comments, in spite of the fact that I make no claims to offer my snippets as safe or guaranteed in any way, and that I take it for granted (foolish though it may be) that people will RTFM before executing ganked scripts on important data.

That said, Fumigator and others are absolutely right -- this is NOT the way to get an ID that will be used in an important app as any kind of future lookup reference. Their suggestion of using last_insert_id() (or some facsimile) in combination with pending/confirmed flag is the proper and safe way, even if it does not actually get the next autoinc without an insert. I wrote that article 5 years ago when I was just a PHP padawan. That particular application is ancient history now, as is just about everything on that dusty old site. But thanks Nancy for standing your ground. You were right too -- the OP did not specify the intent, so this was a perfectly legit answer to the Q. Hope that helps put this "controversy" to bed :).

Fumigator
02-25-2008, 06:48 AM
Yay, so now please everyone let this thread die.

idalatob
02-25-2008, 09:29 AM
use last_insert_id() and add 1.

CFMaBiSmAd
02-25-2008, 12:46 PM
idalatob, firstly, Fumigator suggested to not continue adding comments to this thread.

Secondly, last_insert_id() is only available after an INSERT query has been executed in the same script.

However if you have inserted the record to establish the ID (which is the correct solution to getting a known ID and has already been stated here), there is no need to add 1 to it. Also, adding one to the last ID serves no useful purpose as concurrent visitors will make the number you come up with incorrect and unusable for any purpose except to display it (which has also been stated here.)

Could a moderator please close this thread (too bad we don't have a beating a dead horse smiley. :rolleyes: )



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum