Page 1 of 1
How to predict the next auto-increment number?
Posted: Tue Oct 18, 2005 9:55 am
by ljCharlie
My primary ID in a MySQL database table is set to auto-increment. My question is, how do I predict or know what the next number is ahead of time before the entry is submitted to the database? Is this possible to know? In most cases, I found that this number is sequential but other cases I found that this number also jumps or skips if for example, I have ID 1, 2, 3, 4, 5, 6 and then I deleted entry 6, the next number would be 7 and not 6.
Help is appreciated.
Posted: Tue Oct 18, 2005 10:10 am
by Weirdan
http://dev.mysql.com/doc/refman/5.0/en/ ... tatus.html
However, why do you want to know the next autoincrement value?
[edit]
URL corrected
[/edit]
Posted: Tue Oct 18, 2005 10:15 am
by pickle
You can also call:
Code: Select all
SELECT LAST_INSERT_ID() FROM `my_table`;
But ya, why do you want to predict the next id?
Posted: Tue Oct 18, 2005 10:23 am
by Weirdan
pickle wrote:You can also call:
Code: Select all
SELECT LAST_INSERT_ID() FROM `my_table`;
I think you did mean
and it would be the
last insert id generated during the db connection session, not the
next autoinc value for a table.
Posted: Tue Oct 18, 2005 10:35 am
by pickle
Ah, my bad. You're right ~Weirdan - using LAST_INSERT_ID() will only really work if one's only inserting into a single table with an auto_increment value. Otherwise, the values could be screwed up.
To get the next value, you can just add one:
Posted: Tue Oct 18, 2005 10:59 am
by ljCharlie
Many thanks for all your help. The reason I want to know the id is in the following situation. I'm creating a website that its contents are store on a MySQL database tabel. So when the user click on a navigation menu, it has the following link characteristics:
<a href='found.php?id=3'>Welcome</a>
And so on. The id 3 is related to the primary id that is auto-increment. Now, for creating new pages, I need to know this id ahead of time so I can insert into the URL field on the table at the same the new page is inserted by the user.
Posted: Tue Oct 18, 2005 11:13 am
by timvw
Just change the order of inserts... First insert the page, then insert the url...
Posted: Tue Oct 18, 2005 11:37 am
by ljCharlie
Thanks for the help. I see that changing the order or delay the insert of the URL field after the primary ID has been assigned will work; however, I'm not sure I know how to delay the process until the id has been assigned then do the URL field insert.
Posted: Tue Oct 18, 2005 12:37 pm
by ljCharlie
Many thanks for all your help. As it turns out, I don't need to store the id in the URL field after all. I can combine the url field and id on the fly.
Again, thanks!