SQL / PHP Repopulate key ID #s for database table

Lets say I have 80 entries in a table, and I had it set to auto-increment when the table was populated. I then removed 10 records from various positions, and now I have entries that look like:

7, 8, 9, 11, 14, 16, etc...

I开发者_开发问答 would like to renumber them in sequential order, but dont want to have to manually do it by hand. How would I be able to create new key ID#'s for them based on alphabetical order?

Thanks in advance!

You don't want to do that nor do you need to do that. It violates many principles of db design and causes unwanted results (trust me on that one).

If you have the need for sequential numbering of something, create a trigger for that, DO NOT rely on primary key for such purposes.

Further to earlier answers if you're just looking for a way to sequentially number your rows when outputted to some view can't you just do it dynamically with php?

table: dogs
id | name 
---------
 1 | Rover
 4 | Fido
 7 | Timothy

Then (modified for whatever loop you're running

<?
$Dogs = mysql_fetch_array(your sql query);

foreach ($Dogs as $cnt => $Dog)
{
    echo ($cnt+1).": ".$Dog['name']."<br />";
}
?>

Would output: 1: Rover 2: Fido 3: Timothy

Whilst still preserving the sanctity of database indexes?