Problem in updating table data

PHP
1

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

2

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

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 :frowning:

Thank you

4

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

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

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

you mean I will order by SKUCode?

Thank you

8

No I didn’t say that. You might need that too, I don’t know.

9

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.

10

Why is it wrong? Give an example of what you want, and what you are getting.

11

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

12

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

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