Getting next auto_increment number

Anon

I am building a website using PHP and MySQL database. One of my tables uses an auto_increment field. How can I get the next value for autonumber without actually adding a record to the table?

If this is not possible, how do I get it immediately after adding a record? Is using a SELECT query the only way?

Regards

Peter

Anon

First you INSERT but in place of an ID you use NULL (like: insert into table values(NULL, 'value', 'value'😉. Then you use the mysql_insert_id() function to get the ID of the last inserted row...

[deleted]

A note to clarify:

There is not possibility on this planet to ever get the 'next' value in an auto_increment sequence without actually using that value. Why not? because the purpose of auto_increment is to be unique. No two users may ever get the same value for that sequence. So whenever a user requests such a value, the database will update it to make sure the next user will get a different value.

zalath

But you can figure the number by doing a "SELECT MAX(id)+1" or something like that.
Doesn't seem to serve any purpose, but it is possible.
-L-

[deleted]

That will give you an indication of what kind of numbers are being used at that time,
but indeed it serves no purpose, and should NOT be used for ANYTHING other than to make you go 'ooh look at that, how interesting' 🙂

[deleted]

you can't :

TABLE test
ID int(11) NOT NULL auto_increment,
TXT text

INSERT INTO test (TXT) VALUES('flqqq');
//LAST ID = 1 -> NEXT ID=MAX()+1=2
INSERT INTO test (TXT) VALUES('blqqq');
//LAST ID = 2 -> NEXT ID=MAX()+1=3
looks fine

!!but if
DELETE FROM test WHERE ID=2;
//LAST ID = 1 -> NEXT ID = MAX()+1=2 !!

INSERT INTO test (TXT) VALUES('mlqqq');
//LAST ID=3

[deleted]

🙂) hehe guys you never know whats going to happen
i was working on a tottaly different project
when i came upon this query:
SHOW TABLE STATUS
http://www.mysql.com/doc/S/H/SHOW_TABLE_STATUS.html

one of the things it returns is the next auto_increment number

so here is the solution 🙂

[deleted]

As long as you remember that you get the number that would have been used at the time you did the 'show table status'. As soon as you actually see the number, it's outdated.