Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Problem in updating table data

    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:

    PHP 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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,407
    and what is the problem?

    would it not be better to do this as a single update statement
    eg
    update sales_order set Doz = (($CompKg * 1000) / $PlugWt) / 12 / 2, KgDoz = $CompKg / (($CompKg * 1000) / $PlugWt) / 12 / 2......

    Im surprised there is no where clause to limit he rows processed
    Im surprised there is no where clause ont he update to limit the rows updated with the new value
    Im surprised you iterate through a recordset but only update outside that loop
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    the problem is all data save in Doz is same. also to other fields.

    I attach my sample output.

    Thank you
    Attached Thumbnails Attached Thumbnails update.JPG  

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    860
    Have you looked at your UPDATE statement? You have not specified that the UPDATE for a for one row in the table. Also your while loop should probably contain the UPDATE statement as your table will only be updated with the calculations from the last row of your SELECT statement.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com

  5. #5
    Join Date
    Dec 2010
    Posts
    136
    I revise my code:

    PHP 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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why are you selecting and then using php to update one row at a time?

    that's ~so~ inefficient

    please, accept healdem's suggestion and do everything with a single UPDATE statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  8. #8
    Join Date
    Dec 2010
    Posts
    136
    Ok I will try your suggestion to put calculation inside the update.
    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •