# Re-numbering MySQL Table Rows

**URL:** <https://forum.kirupa.com/t/re-numbering-mysql-table-rows/244751>\
**Category:** programming\
**Created:** [November 21, 2007, 1:27pm UTC](https://forum.kirupa.com/t/re-numbering-mysql-table-rows/244751 "2007-11-21T13:27:21Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![iNeed\_Help](https://avatars.discourse-cdn.com/v4/letter/i/49beb7/32.png) [@iNeed\_Help](https://forum.kirupa.com/u/iNeed_Help)\
**Post date:** [November 21, 2007, 1:27pm UTC](https://forum.kirupa.com/t/re-numbering-mysql-table-rows/244751/1 "2007-11-21T13:27:21Z")

</div>

## 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:

```php

$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 🙂