CODEWITHSUNDEEP
phpmysqlauto-increment
Johan
Johan

Reputation: 19062

Get next auto increment

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?

Upvotes: 9

Views: 16939

Answers (6)

Ishmael Mavor Raines
Ishmael Mavor Raines

Reputation: 320

SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "database_name"
AND TABLE_NAME = "table_name";

Upvotes: 0

Roland
Roland

Reputation: 1

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();
}

Upvotes: 0

longneck
longneck

Reputation: 12226

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.

Upvotes: -2

OIS
OIS

Reputation: 10033

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.

Upvotes: 2

Johan
Johan

Reputation: 19062

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

:)

Upvotes: 16

Vlad Andersen
Vlad Andersen

Reputation: 366

The query should look like this:

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

Upvotes: 10

Related Questions