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
}
}
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