Condense MySql Database

[VitaminWater]

I have a mysql database that has more and more data added to it daily. Sometimes however, i delete a certain record from the database, then add another, created a gap in the information. IE:
[1][John Smith]
[2][Another Name]
[-][------]
[4][Yet Another]

Is there a way to condense this database down to look like this?
[1][John Smith]
[2][Another Name]
[3][Yet Another]

Thanks

[forum_amnesiac]

If the field to which you are referring is an autoincrement field then I think there is only 1 way to 'condense' the table.

You need to delete the field, and then recreate it, with same name and as an autoincrement.

This will renumber the field with sequential numbers.

You need to take care if the number sequence is important to whatever you are doing, although this is not good use of the autoincrement feature, because this will give an autoincrement number to each record in the sequence that they appear in the table.

[VitaminWater]

Im not quiet sure how to delete the field and then recreate it since it is an auto increment column.
The basics of what im trying to do is have people post certain things on my site. If they decide to look at a 'random' one, that page generates a random number between 1 and (the number of rows) in that table, this number is the auto incremented ID. However, like i said in my previous post, when i delete a post the code stops working properly.
If anyone has any other ideas on how i can achieve this idea, i would appreciate it, thanks.

[VitaminWater]

I used this solution


$sql = "DELETE FROM tablename WHERE id=$id";
$sql1 = "alter table tablename drop column id";
$sql2 = "alter table tablename add id int(11) primary key auto_increment not null";

$result = mysql_query($sql);

$result1 = mysql_query($sql1);

$result2 = mysql_query($sql2);

[forum_amnesiac]

You have just done what I said, you deleted and then recreated the autoincrement field.

I don't think it was necessary for the first line, the DELETE statement, it should be impossible to have an autoincrement field that is null