Re-numbering MySQL Table Rows

programming
1

Hi all…
I’ve got another for ya’. Basically… Lets say i have a mysql table with the following:

| table_id | order_id |

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 4 | 4 |

Then I have a script that deletes the row with the table_id of 3, I would end up with something like this:

| table_id | order_id |

| 1 | 1 |

| 2 | 2 |

| 4 | 4 |

Instead, I would like to end up with:

| table_id | order_id |

| 1 | 1 |

| 2 | 2 |

| 4 | 3 |

So I wrote the following code:


$query = "SELECT * FROM table1 ORDER BY order_id ASC";
$result = mysql_query($query, $db_connect);
$rownum = mysql_num_rows($result);
if ($rownum > 0) {
 echo "success! ".$rownum;
} else {
 die("error: " . mysql_error());
}
$order_no = "1";
while ($order_no < $rownum or $order_no = $rownum) { 
 $query = "UPDATE table1 SET order_id = '$order_no'";
 $result = mysql_query($query, $db_connect);
 if (!$result) {
  die("error: " . mysql_error());
 } else {
  echo "i did it!";
 }
 $order_no++;
}

The problem is that this code don’t work… Any ideas why?

Thanx.

P.S. The forum stripped the whitespace out of the table layout in this post, so you have to use your imagination to adjust the layout of it a bit in your minds :slight_smile: