? nested loop to update MySQL rows, using WHILE or IF or BOTH ??

[kthibeault1965]

I need to update rows in a table with serial integer values for each GROUP  For example  1, 2, 3, 4, 5, 6, ... and so on, for each grouping of rows

BEFORE:

GROUP, LINE_NUMBER

100  0

100  0

100  0

200  0

200  0

AFTER:

GROUP,LINE_NUMBER

100  1

100  2

100  3

200  1

200  2

 

 

I have been working  on the solution with no luck, please help if you can

 

$result=mysql_query("select * from csv_stage2 order by parent_id");

$i = 1;

while($row=mysql_fetch_array($result)){

if ($parent_id = $row[parent_id]) {

mysql_query("update csv_stage2 set prod_variant_id = $i where uid = $row[uid]") or die (mysql_error());

 

} else {

$parent_id = $row[parent_id];

$i = 1;

mysql_query("update csv_stage2 set prod_variant_id = 1 where uid = $row[uid]") or die (mysql_error());

}

 

++$i;

}

[kthibeault1965]

I have been working on this version, still no luck tho.

 

$query="select * from csv_stage2 order by parent_id";

$result=mysql_query($query);

$num=mysql_numrows($result);

 

$i=0;

while ($i < $num) {

$id=mysql_result($result,$i,"uid");

$query1="update csv_stage2 set prod_variant_id = $i where uid = $row[uid]";

mysql_query($query) or die(mysql_error());

++$i;

}