Deleting records without disrupting LAST_INSERT_ID()

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
phptt
Forum Newbie
Posts: 4
Joined: Thu Dec 31, 2009 3:31 pm

Deleting records without disrupting LAST_INSERT_ID()

Post 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 )
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Deleting records without disrupting LAST_INSERT_ID()

Post 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.
(#10850)
phptt
Forum Newbie
Posts: 4
Joined: Thu Dec 31, 2009 3:31 pm

Re: Deleting records without disrupting LAST_INSERT_ID()

Post 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!
Post Reply