How to add values to in the result of while loop in php

Question

How to add the result of the while loop which is stored on the variable $profit? results are 5 and 70

the code

  <?php
        $sql2 = "SELECT * from `products`";
        $result2 = $link->query($sql2);

        while($row2 = $result2->fetch_assoc())
      {

      $sql3 = "SELECT * from `orders` where product_id = '".$row2['prod_id']."'";
      $result3 = $link->query($sql3);
      $row3 = $result3->fetch_assoc();
      
      $sql = "SELECT  SUM(product_qty) from orders where product_id = '".$row2['prod_id']."'";
      $result = $link->query($sql);
      $row = mysqli_fetch_array($result);

      $res = bcmul($row2['prod_price'], $row[0]);
      $profit = $res - $row2['prod_cost'];

      if($row[0] == null){
          $row[0] = 0;
      }
    }?>

Please add sample data along with the desired result. There is probably an easier way to do what you want here. — Tim Biegeleisen, May 20 '22 at 06:07
Why the nested queries - looks like this could be done with a single query? The `$row3` and `$result3` never get used so why are they there? — Professor Abronsius, May 20 '22 at 06:38
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, May 20 '22 at 11:19

score 0 · Accepted Answer · answered May 20 '22 at 06:11

Try this code

  <?php
        $sql2 = "SELECT * from `products`";
        $result2 = $link->query($sql2);
        $totalProfit = 0;
        while($row2 = $result2->fetch_assoc())
      {

      $sql3 = "SELECT * from `orders` where product_id = '".$row2['prod_id']."'";
      $result3 = $link->query($sql3);
      $row3 = $result3->fetch_assoc();
      
      $sql = "SELECT  SUM(product_qty) from orders where product_id = '".$row2['prod_id']."'";
      $result = $link->query($sql);
      $row = mysqli_fetch_array($result);

      $res = bcmul($row2['prod_price'], $row[0]);
      $profit = $res - $row2['prod_cost'];
      $totalProfit = $totalProfit + $profit
      if($row[0] == null){
          $row[0] = 0;
      }
    }?>

now you can use $totalProfit variable value as total.

thank you sir Roshan Twanabasu that worked. Others are just criticizing the flow of code and not answering what I am asking. Thank you very much — Kenneth John Correo Ribay, May 21 '22 at 03:30

How to add values to in the result of while loop in php

1 Answers1