after a series of update queries i need to add up the fields and update a total column

it is working but it only puts the last records total in all records

$query=mysql_query("SELECT   2hsm1p + 2hsm2p + 2lledm1p + 2lledm2p + 2pos_1p + 2pos_1bp + 2pos_2p + 2pos_2bp + 2pos_3p + 2pos_3bp + 2pos_4p + 2pos_4bp + 2pos_5p + 2pos_5bp + 2pos_6p + 2pos_6bp + 2pos_7p + 2pos_7bp + 2pos_8p + 2pos_8bp + 2pos_9p + 2pos_9bp + 2pos_10p + 2pos_10bp AS Total2, 4hsm1p + 4hsm2p + 4lledm1p + 4lledm2p + 4pos_1p + 4pos_1bp + 4pos_2p + 4pos_2bp + 4pos_3p + 4pos_3bp + 4pos_4p + 4pos_4bp + 4pos_5p + 4pos_5bp + 4pos_6p + 4pos_6bp + 4pos_7p + 4pos_7bp + 4pos_8p + 4pos_8bp + 4pos_9p + 4pos_9bp + 4pos_10p + 4pos_10bp AS Total4 FROM 13picks WHERE race_id = $mxrace");

 while($rows = mysql_fetch_array($query))

{
$total250 = $rows['Total2'];
$total450 = $rows['Total4'];

mysql_query("UPDATE 13picks SET round_250_total ='".$total250."', round_450_total = '".$total450."'");

}

    BTW if i echo $total250 and $total450 it shows the totals

      Because you don't restrict the UPDATE statement to only work on specific records (based on something such as race_id). So every UPDATE statement gets applied to all records.

      Not incidentally, [font=monospace]UPDATE 13picks SET round_250_total=2hsm1p + 2hsm2p + ....[/font] would work.

      Not incidentally, the MySQL extension is deprecated and should not be used for new code.

        Write a Reply...