9

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?

Johan
  • 18,814
  • 30
  • 70
  • 88

6 Answers6

16
$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.

:)

Johan
  • 18,814
  • 30
  • 70
  • 88
  • Works great!! Is it reliable enough to use the next_increment id, as foreign key for inserting into another table? – Marko Aleksić Mar 27 '11 at 16:20
  • From longneck below: "If ... you have multiple users [using the table] at the same time, you'll frequently get the wrong value." – Andrew Apr 24 '11 at 14:40
10

The query should look like this:

SHOW TABLE STATUS WHERE `Name` = 'Media';
Vlad Andersen
  • 366
  • 2
  • 3
2

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.

OIS
  • 9,833
  • 3
  • 32
  • 41
  • any reasons why this is slow? – Vijay Dev Dec 20 '09 at 07:51
  • 2
    This is slow because for databases containing thousands (or millions) or tables, the information_schema db becomes incredibly large. Best to use the `SHOW TABLE STATUS LIKE 'tableName'` query. – Graham Swan Apr 04 '10 at 23:39
0

You can also use this function

function getNextValue(){
$query = "SHOW TABLE STATUS LIKE 'vendors'";
dbconnect();
$results=mysql_query($query);
if(mysql_errno() != 0) {
    $result['count'] = -1;
    $result['error'] = "Error: ".mysql_error();
} else {
    $result['count'] = mysql_num_rows($results);
    for($counter=0;$counter<$result['count'];$counter++) {
        $result[$counter] = mysql_fetch_assoc($results);
    }
}
return $result[0]['Auto_increment'];
mysql_close();
}
Roland
  • 1
0
SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "database_name"
AND TABLE_NAME = "table_name";
Ishmael Mavor Raines
  • 144
  • 5
  • 1
    It would be best if you could explain, for the benefit of other users, what information_schema is. It would also be helpful if you pasted a link to a reference on it. – Rohit Gupta Jul 12 '22 at 01:19
-2

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.

longneck
  • 11,938
  • 2
  • 36
  • 44
  • -1 Because what if the table doesn't have any entries, but the auto increment is there? – Kalle H. Väravas Jan 10 '12 at 10:03
  • then you're still doing something wrong. under what possible circumstance would you want to do this? the OP wants to get the next value so they can set a foreign key in another table. this is a bad idea. the record should be inserted in to this table first, then get the auto increment value that was created, then insert the foreign key. doing it the other way around is like asking how to back your car out of your garage before putting the key in the ignition. – longneck Jan 10 '12 at 14:14
  • Not the point. Point is, that the question was "How to get next auto increment" not "How to get last inserted id". So basically, if the table is empty, and you want to know the next auto increment to create a stamp title (example: "Stamptitle-22"). Then last inserted id wont work. – Kalle H. Väravas Jan 10 '12 at 16:06
  • This is still the wrong approach. Insert to get the ID assigned, then update if you have to. But again, trying to guess the next auto_increment value is universally a bad idea. – longneck Mar 31 '17 at 23:16