Reset row "id's" after arbitrary value deleted

[xander85]

Is there a way to reset the auto_increment value and all subsequent values for that table if you delete an arbitrary row?

example:

 

row 0 - test info

row 1 - test info

row 2 - test info

row 3 - test info

 

new auto_increment value = 4

 

I delete row 2, so table looks like:

row 0 - test info

row 1 - test info

row 3 - test info

 

the auto_increment is still 4, but there are only 3 rows in the table and the values are not correct. Is there a function or easy way to "reorganize" the row values and reset the auto_increment?

 

 

[Jessica]

use a line counter to number them instead of using the index. So when you loop through add 1 to a variable each time.

Unless they can be sorted, then use sort.

[xander85]

I'm confused, can you give me an example?

[xander85]

The column is set to an auto_increment value, if that helps. So it is indexed as unique by nature.

[Jessica]

Well post your code. I'm not going to write a whole bunch of example code when it's probably just one line that needs to be added to yours. Like I said, add a line counter.

[xander85]

Right now I'm using this:

 

$sql = "DELETE FROM mileage WHERE mileid = '$mileid' LIMIT 1";
  $result = mysql_query($sql);
  $idquery = mysql_query("SELECT MAX(mileid) AS new_id from mileage");
  $newid=mysql_fetch_array($idquery);
  $newmileid = $newid["new_id"]+1;
  $sql2 = "ALTER TABLE mileage AUTO_INCREMENT=$newmileid";
  $result2 = mysql_query($sql2);

 

But this will only work if you are deleting the last value in the table. If you delete an arbitrary value it will not work correctly.

[Jessica]

I didn't realize you meant a database table, I thought you were printing them in an html table.

You wouldn't want to change the IDs, that messes up the integrity of the table. It would make the ID a meaningless number if it kept changing.