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 ) )