Skip to content
Advertisement

SUM of the column in foreach array PHP not working

I have created the foreach function and inside of that function, I am trying to calculate the sum of the column. I have read all the related threads on StackOverflow or google, but for 3 days I am trying and I could not get the answer and resolved my issue. Please help me, how I calculate the column, I want to SUM of all [cost] and show in [total].

Please help me.

table = table1
    ID | key    | p_name
    ---------------------
    1  | 123456 | A
    2  | 145236 | B

table = table2
    ID | column | key
    ---------------------
    1  | 10     | 123456
    2  | 5      | 123456
    3  | 20     | 145236
    4  | 30     | 145236

$stmt = $con->prepare("SELECT * FROM `table1`");
$stmt->execute();
$result = $stmt->fetchAll();
$final = [];
foreach ($result as $data => $value) {
    $stmt01 = $con->prepare("SELECT * FROM `table1`");
    $stmt01->execute();
    if($stmt01->rowCount() > 0) {
        while($result001 = $stmt01->fetch(PDO::FETCH_ASSOC)) {
            $name = $result001['p_name'];

            $sql = $con->prepare("SELECT `ID`, SUM(`column`) AS `last_cost_rate`, `key` FROM `table2` WHERE `key` = :key AND `ID` = (SELECT MAX(`ID`) FROM `table2` WHERE `key` = :key)");
            $sql->execute(array(':key' => $value['key']));
            $sum = 0;
            while($sqlvalue001 = $sql->fetch(PDO::FETCH_ASSOC)) {
                $last_costing = $sqlvalue001['last_cost_rate'];
                $sum+=$last_costing
            }

        }
    }

    
    $final[] = [
        'name' => $name,
        'total' => $sum
    ];              
}

OUTPUT

Array
(
    [0] => Array
        (
            [product] => A
            [total] => 5
        )

    [1] => Array
        (
            [product] => B
            [total] => 30
        )
)

Advertisement

Answer

After you have build the array, just use a foreach block to replace all the sumtotal :

So you may continue to use the following:

$sum += $last_costing;

and then use the following foreach to populate the data:

foreach ($final as &$value) {
$value['sumtotal'] = $sum;
}

So, the code (fully working) is:

<?php
// Note: put your real username/password in the following line :

$conn = new mysqli("localhost","user","password","dbname");

$sql="select table1.p_name, table1.`key` , table2.`column` from table1, table2 where table1.`key`=table2.`key` order by table1.id, table2.id desc";

$final = [];
$sum=0;
$stmt = $conn->prepare($sql); 
$stmt->execute();
$result = $stmt->get_result();
$opname="";
    while ($c = $result->fetch_assoc()) {
      if ($opname!=$c["p_name"]) {
          $opname=$c["p_name"];  


      $last_costing=$c["column"];

      $sum += $last_costing;

         $final[] = [
        'product' => $c["p_name"],
        'total' => $c["column"],
        'sumtotal' => 0
         ];      
      }
  }

foreach ($final as &$value) {
$value['sumtotal'] = $sum;
}

print_r($final);
?>

The result is:

Array 
( 
    [0] => Array 
      ( 
      [product] => A 
      [total] => 5 
      [sumtotal] => 35 
      ) 
    [1] => Array 
      ( 
       [product] => B 
       [total] => 30 
       [sumtotal] => 35 
      ) 
)
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement