Updating keys in database

rougue

Hello everyone:

Have a a php and database system setup when you can add records and delete records but i would like to know how to change the key value in the database to be correct:

ie. the table has a field called id that is autoincremented so lets say there are 10 records in the table and numer 3 is deleted. is there a way to get it to update the id values for all of the records below it to not be skipping numbers? i think i've explained it well... thanks in advance.

Pig

$result = mysql_query("SELECT * FROM table_name ORDER BY id ");
$i = 1;
while( $row = mysql_fetch_array($result) )
{
mysql_query("UPDATE table name SET id='$i' WHERE id='$row[id]");
$i++;
}

Sxooter

Originally posted by rougue
Hello everyone:

Have a a php and database system setup when you can add records and delete records but i would like to know how to change the key value in the database to be correct:

ie. the table has a field called id that is autoincremented so lets say there are 10 records in the table and numer 3 is deleted. is there a way to get it to update the id values for all of the records below it to not be skipping numbers? i think i've explained it well... thanks in advance.

Sorry, but that's not what autoincrement fields are for. They're there to make sure every record has a unique key OVER TIME. changing an autoincrementing field from one value to another loses this and means that someday, an old backup could associate the wrong sub records with the newly changed master.

If you need to number them for the user do so in php, and don't show them the real id number in the backend.

Pig

Sxooter is right. I cannot think of a reason to do so, but maybe you have some special need. /me shrugs.

You could also create a second column with sequential numbering, or build that into how you display the records.