Problem in updating table data

[newphpcoder]

Hi...

 

I have table with

PO_No

POReq

CompKg

PlugWt

Doz

KgDoz

TotalKg

BatchNeeded

 

Now,

 

I have problem in updating  field (Doz, KgDoz, TotalKg, BatchNeeded).

 

 

here is my code:

 

$sql = "SELECT CompKg, PlugWt, POReq FROM sales_order ";
   $res = mysql_query($sql, $con);
   
  while($row = mysql_fetch_assoc($res)){
   
   $CompKg = $row['CompKg'];
   $PlugWt = $row['PlugWt'];
   $POReq = $row['POReq'];
   
   $Doz = (($CompKg * 1000) / $PlugWt) / 12 / 2;
   $KgDoz = ($CompKg / $Doz);
   $TotalKg = ($POReq * $KgDoz);
   $BatchNeeded = ($POReq / $Doz);
  } 
   $sqlupdate = "UPDATE sales_order SET 
   Doz = '$Doz',
   KgDoz = '$KgDoz',
   TotalKg = '$TotalKg',
   BatchNeeded = '$BatchNeeded'";
   $res_update = mysql_query($sqlupdate, $con);

 

the result are wrong.

 

Thank you

 

 

[tipsmail7]

So, could you elaborate what's the "wrong" means?

 

PS: Are you intend to update ALL row? (because you have no "WHERE" in 2nd query)

[newphpcoder]

I revise my code:

 

   $sql_ud = "SELECT CompKg, PlugWt, SKUCode FROM sales_order ORDER BY SKUCode";
   $res_ud = mysql_query($sql_ud, $con);
   
  while($row_ud = mysql_fetch_assoc($res_ud)){
   $SKUCode = $row_ud['SKUCode'];
   $CompKg = $row_ud['CompKg'];
   $PlugWt = $row_ud['PlugWt'];
   
   $Doz = @(($CompKg * 1000) / $PlugWt) / 12 / 2;
   $KgDoz = @($CompKg / $Doz);
   
   $sqlupdate = "UPDATE sales_order SET 
   Doz = '$Doz',
   KgDoz = '$KgDoz'
   WHERE SKUCode = '$SKUCode'";
   
  // echo $sqlupdate;
   $res_update = mysql_query($sqlupdate, $con);
  }
  
  $sql = "SELECT POReq, Doz, KgDoz, SKUCode FROM sales_order ORDER BY SKUCode";
  $res_up1 = mysql_query($sql, $con);
  
  while($row_up1 = mysql_fetch_assoc($res_up1)){
  $SKUCode = $row_up1['SKUCode'];
  $POReq = $row_up1['POReq'];
  $Doz = $row_up1['Doz'];
  $KgDoz = $row_up1['KgDoz'];
      
  $TotalKg = @($POReq * $KgDoz);
  $BatchNeeded = @($POReq / $Doz);
  
  $sqlupdate1 = "UPDATE sales_order SET 
   TotalKg = '$TotalKg',
   BatchNeeded = '$BatchNeeded'
   WHERE SKUCode = '$SKUCode'";
   
   $res_update1 = mysql_query($sqlupdate1, $con);
      
  }

 

And now I have wrong output in my second update. for TotalKg and BatchNeeded.

 

[mikosiko]

do you have any reason to make SELECT's and then UPDATE's ?... do you know that you can make the calculations directly in the UPDATE?... therefore all your code could be replaced with just 1 UPDATE

 

 

[newphpcoder]

Ok I will try your suggestion to put calculation inside the update.

Thank you