SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    First of all, things like "the results are wrong" are not helpful at all at locating the error. We don't know what the results are, and what they should be. You should try to be more clear in describing your problems.

    Second, take a look at that code, especially at what you're doing inside the loop. At each iteration, you give the variables a new value, based on that iteration only. But the query is executed only at the end of the loop. So you only insert the data from the latest iteration.

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I attach the output of my query.

    I mean is all data in Doz was same also in other fields that I update..
    So, what should I need to revise or change in my code

    Thank you
    Attached Files Attached Files

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    In your update query you don't have any WHERE conditions, so all rows will be updated?
    Isn't that what you want? Then what do you want? You have to specify what rows you want to update (just like you do when you SELECT rows).

    And I see no attachment.

  5. #5
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    In your update query you don't have any WHERE conditions, so all rows will be updated?
    Isn't that what you want? Then what do you want? You have to specify what rows you want to update (just like you do when you SELECT rows).

    And I see no attachment.
    Sorry, now you can view my attachment.

    I just want to update all rows base on the SKUCode because its a unique.

    I want to update is Doz, KgDoz, TotalKg, BatchNeeded

    Thank you

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    I just want to update all rows base on the SKUCode because its a unique.
    I see no SKUCode in the table definition you posted, I see no SKUCode in the select query, I see no SKUCode in the update query...

    Ok, I see it in your attachment. So select that value as well, and add it to the WHERE condition of your update query.

  7. #7
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    I see no SKUCode in the table definition you posted, I see no SKUCode in the select query, I see no SKUCode in the update query...

    Ok, I see it in your attachment. So select that value as well, and add it to the WHERE condition of your update query.
    you mean I will order by SKUCode?

    Thank you

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    you mean I will order by SKUCode?

    Thank you
    No I didn't say that. You might need that too, I don't know.

  9. #9
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Why is it wrong? Give an example of what you want, and what you are getting.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict tom8's Avatar
    Join Date
    Mar 2012
    Location
    New Jersey
    Posts
    310
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    When you’re starting out, sometime it is better to do it the long way. After using regular expressions in over ten years, I’m still having problem of condensing the code to a minimum. I only can see how the match is done by expanding each segment.

    I would do what guido suggests, get the output from the query and compare that to what you are expecting. So just echo $sqlupdate1 and you might see the reason why.

  13. #13
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I will try your suggestion to put calculation inside the update.
    Thank you


Bookmarks

Posting Permissions

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