Page 1 of 1

Deleting records without disrupting LAST_INSERT_ID()

Posted: Thu Dec 31, 2009 3:51 pm
by phptt
Hi guys

I am making a site where the user can leave their contact details with a message. I used MySQL, and for each message a user leaves, my php script adds a new record to a table, with and id value of NULL, as it is on AUTO_INCREMENT. However, a big problem occurs when the records are deleted and there are gaps in the id field:

I have an administrative area where the admin can view and delete messages that were left by other people. When the admin logs on, I have made a php script that goes like this so that the admin can view all the messages in the table:

Code: Select all

 
function lastid($table) {
    $q = "SELECT LAST_INSERT_ID() FROM $table";
    return mysql_num_rows(mysql_query($q)) + 1;
}
$lastid = lastid("table_name");
for($id=1; $id<=$lastid; $id++) {
    $tmp = mysql_fetch_assoc(mysql_query("SELECT * FROM table_name WHERE id=$id", $dbp));
    if($tmp['firstname']!==NULL) {
        //Code for information layout, I won't write it down because it's too long
    }
}
 
The main gist of this was that starting from id 1, the loop runs and displays the message for the admin until it reaches the $lastid, which is the last message to be inserted. If there is a gap in the id, it doesn't do anything and moves on, which is what I wanted as messages will be deleted. However, when the admin deletes a message, my script deletes the record, and somehow, my lastid() function goes crazy. For example, let's say I have this first:
id firstname email
1 John john@example.com
2 James james@example.com
3 Peter peter@example.com

When the admin deletes, say, James' message, there will be a gap, with id 1 and id 3 but without id 2. From here, my lastid() seems to think because id 2 was deleted most recently, that the last inserted id is 2. So now, lastid() returns 2, when theoretically it should return 4. When the number of records increase, because of this problem, my admin page won't display some of the records and of course, there is a problem when a user tries to write a message whose id already exists.
I'm really exasperated at this problem, I have been googling and googling but I still havn't found the answer!
Please help!
(And thanks for reading this long essay :D )

Re: Deleting records without disrupting LAST_INSERT_ID()

Posted: Thu Dec 31, 2009 5:40 pm
by Christopher
phptt wrote:When the admin deletes, say, James' message, there will be a gap, with id 1 and id 3 but without id 2. From here, my lastid() seems to think because id 2 was deleted most recently, that the last inserted id is 2. So now, lastid() returns 2, when theoretically it should return 4. When the number of records increase, because of this problem, my admin page won't display some of the records and of course, there is a problem when a user tries to write a message whose id already exists.
I'm really exasperated at this problem, I have been googling and googling but I still havn't found the answer!
I think you are misunderstanding autoincrement, and not using last_insert_id correctly. First, the value for id for the next inserted record will be based on the autoincrement value -- not the values of keys in the table. Second, it will only report a valid value after and insert, not a delete. If you want to know the maximum value then use MAX(), otherwise just insert records with no value for 'id' and it will just work.

Re: Deleting records without disrupting LAST_INSERT_ID()

Posted: Fri Jan 01, 2010 12:00 pm
by phptt
Sorry, I didn't mean inserting records with AUTO_INCREMENT went wrong - i know I've wrote it down, but I was mistaken. The INSERT works fine, but LAST_INSERT_ID() goes wrong. Anyway, MAX() works very well, so I'll be fine with it. Thanks!