Get next auto increment

Question

I know this isn't so complicated but I can't remember how to do.

I just need to know the next auto increment.

$result = mysql_query("
    SHOW TABLE STATUS LIKE Media
");
$data = mysql_fetch_assoc($result);
$next_increment = $data['Auto_increment'];

...but i won't work for me, what am I doing wrong?

Answer 1

$result = mysql_query("
    SHOW TABLE STATUS LIKE 'Media'
");
$data = mysql_fetch_assoc($result);
$next_increment = $data['Auto_increment'];

The name of the table needed to be wrapped with single quotes like this: 'table_name'

So it works just fine now.

:)

Answer 2

The query should look like this:

SHOW TABLE STATUS WHERE `Name` = 'Media';

Answer 3

Another way, but slow, is:

SELECT AUTO_INCREMENT FROM information_schema.`TABLES` T where TABLE_SCHEMA = 'myScheme' and TABLE_NAME = 'Media';

The information_schema is mostly usefull for getting data from many schemes.

Answer 4

if you need to know the next auto_increment, then it's 99% likely you're doing it wrong. instead of the getting the next auto_increment, you should just do the insert you're about to do, then use SELECT LAST_INSERT_ID() to get the auto_increment value from that insert.

if you try to guess the next auto_increment value and you have multiple users doing it at the same time, you'll frequently get the wrong value.