How To Fix Mysql Broke IDs

ak007

For Example Ive Got Two Fields id And Name.Id Is An int + Autoincrement And Also A Primary Key and name is a Varchar(20) If I Insert A Record In The Table , Id Is Auto Incremented But When I Delete Any Record It Leaves A Gap Between Numbers . For Example If I Delete 2nd Row Then Id After Record One Comes Directly Record 3 How Do I Fix So That Record 3 Becomes Record 2. if you cant follow me here is an example:
Normal:
ID Name
1 Alex
2 John
3 ByteCode

After Ive Deleted Record 2 (John)

ID Name
1 Alex
3 ByteCode

See Record#3 DirectlyComes After Record#1
How Do I Do So Whenever I Delete Any Record The Id Get Fixed (In ABove Case 3 Becomes 2)

Thanks In Advance

Lars_Berg

Why do you need consecutive values for the primary key? Those gaps will not affect the behavour of the DBMS either performance or storage wise.

solodesignz

You would need another column, an OrderId column

$select = ('SELECT ID FROM table');
$result = mysql_query($select) or die('blah: ' . mysql_error());
if(mysql_num_rows($result)== 0){
   echo('no results');
}else{
   $i=1;
   while($row=mysql_fetch_array($result)){
      $update='UPDATE table SET OrderId = ' . $i . ' WHERE ID=' . $row['ID'] ;
      $result2 = mysql_query($update) or die('error: ' . mysql_error());
      $i++;
   }
}

Something like that should work... but it would really bog down the more data you have in the database...
Then you would just act on the OrderId column instead of your ID column...
But just like the previous post said... i dont see why you need to do this

ak007

i really need i cant tell you the whole software and how it works cuz its tooo big But I need Them To Be Fixed

laserlight

It aint broke, dont fix it.

If you disagree, then you should state why you need the primary key to be in running order. Chances are you dont.

Sxooter

What LaserLight said ++.

It's a recipe for disaster to go renumbering your primary keys like that.