for loop - error

[LogicOpinion]

hello.
i came to a lil problem i was not able to solve myself.
so here it is..

i have some table in db:

Structure is : ID | ORDER_NO | TITLE | SUBTITLE | FULLTEXT | ATTACHMENT |

and if i have 5 rows in my table and remove one of them
forexample: 2nd one the order no then becomes: 1, ,3,4,5... 2 is missing

so i wanted to reorder it when deleting and tried something like this

$q = "SELECT * FROM bps2news";
$re = mysql_query($q) or die (mysql_error());
$ro = mysql_fetch_array($re);
$num = mysql_num_rows($re);
$newnum = $num + 1;
for ($i = 1; $i < $newnum; $i++)
{
mysql_query("UPDATE bps2news SET order_no = '$i'");
} 

and this code does not do what i want but gives every row maximum value of $i

any ideas?

THANKS

[scragar]

$numRemoved = 2;
mysql_query("UPDATE bps2news SET order_no = order_no-1 WHERE order_no > $numRemoved"); 

[LogicOpinion]

this is if i remove 1 row..
but what if i remove several rows

2nd, 5th, 15th.. etc..

[scragar]

$numsRemoved = array(2, 5, 15);
$query = ('UPDATE bps2news SET order_no = order_no - (order_no>'.
              implode(')-(order_no>', $numsRemoved).
              ')'); 

I'd echo out $query first, should result in something like:

UPDATE bps2news SET order_no = order_no - (order_no>2)-(order_no>5)-(order_no>15)

if that code should work(but I've not tested it).