MySQL query in PHP gives obvious wrong result.

Question

Hi there,

I'm using PHP and PHPMyAdmin to create a small profile site. I'm giving members an ID number, based on which is the biggest number currently in the database, +1

I did 25 tests before I got the PHP script where I wanted it to be.

I then deleted those 25 entries using PHPMyAdmin.

But now, when my PHP code does this:

function getLatestID() {
    $query = "SELECT max(member_id) FROM members";
    $result = @mysql_query($query) or showError("unable to query database for user information");
    if (!($record = mysql_fetch_array($result))) return null;
    return $record[0];
}

I get the wrong number.

Test scenario: the database table holds 3 entries, with ID's 1, 2 and 3.

I start a debugging session and put a breakpoint on the return $record[0]. I check its contents and instead of 3, which is the biggest number, it's 28.

As in 25+3=28, the 25 entries that I allready deleted...

Does anybody know what's causing this and how I can fix it?

Answer 1

If you're using auto_increment in MySQL then deleting records won't decrease the next value.

You can empty a table with TRUNCATE TABLE mytable - this will reset the value.

Answer 2

It's probably because you have auto_increment set and the query is returning the highest id. When you deleted the other records, you probably didn't reset the auto increment count.

Answer 3

You can also change value that auto-increment thinks is the next value to allocate:

ALTER TABLE members AUTO_INCREMENT = 3;

Note that if you put in a value that is less than the current max value in the auto-increment column, it'll change the value to that MAX+1. To see what the current next value is set to, do this:

SHOW CREATE TABLE members;

At the end of the table definition, it'll show "AUTO_INCREMENT = 26" or whatever it's current value is.