loop to update products grouped by catalogID

[dragon_sa]

I am trying to create a quick script to update all my products in my product table, I want to set a prodOrder starting at 1 and incrementing by 1 for each product in the catalogID group.

eg if I have 12 product with a catalogID of 1 then I want to number the prodOrder for each product in my table 1-12

and so on for each catalogID group

whats the best way to do this

here is my starter code but no where near performing this function

$sql=("SELECT * FROM product ORDER BY productID ASC GROUP BY catalogID");
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)) {
$catID=$row['catalogID'];
$id=$row['productID'];
$value='1';
foreach ($catID AS $id => $value) {
$qty=("UPDATE product SET prodOrder='$value' WHERE productID='$id'");
$change=mysql_query($qty);
echo $row['name']." from $catID - order = $value<br/>";
$value++
}
}

[dragon_sa]

I managed to nut it out

 

$sql=("SELECT * FROM product GROUP BY catalogID ORDER BY catalogID ASC");
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)) {
$cat[]=$row['catalogID'];
}
foreach ($cat AS $catID) {
$value='1';
$pql=("SELECT * FROM prodtest WHERE catalogID='$catID'");
$res=mysql_query($pql);
while ($r=mysql_fetch_array($res)) {
$pID=$r['productID'];
$qty=("UPDATE product SET prodOrder='$value' WHERE productID='$pID'");
$change=mysql_query($qty);
$value++;
}
}