I have a database table with the name dt_account data and the structure is more or less like this :
and a transaction table with the name dt_jurnaltransaksi and a structure like this :
This is the result of querying the table above with the child parent tree, and this is what happens in my code:
and this is the result that should be and what I want like this, so the total data is also in the parent account
this is the php file that I use
<?php
#MUTASI DEBIT KREDIT
$sql_labarg = "SELECT
T1.uq_account,
T1.kd_parent,
T1.nama_akun,
T1.main,
SUM(T2.debit) AS debit,
SUM(T2.kredit) AS kredit
FROM dt_account AS T1
LEFT JOIN dt_jurnaltransaksi AS T2
ON T2.uq_account = T1.uq_account
GROUP BY T1.uq_account
ORDER BY T1.id_sort ASC, T1.kd_account
"; ?>
<div class="mt-4 p-6 shadow-[0_2px_20px_-5px_rgba(0,0,0,0.1)] rounded bg-white">
<div class="w-full max-lg:px-3 max-lg:mt-8">
<div class="p-0 m-0"> <?php
$data = array();
$query = mysqli_query($db, $sql_labarg);
while($row = mysqli_fetch_assoc($query)) {
$sb_countv = $db->query("SELECT
SUM(T2.kredit) - SUM(T2.debit) AS nominal_hr
FROM dt_account AS T1
LEFT JOIN dt_jurnaltransaksi AS T2
ON T2.uq_account = T1.uq_account
WHERE T1.kd_parent = '{$row['uq_account']}'
")->fetch_assoc();
$tmp[$row['uq_account']]['uq_account'] = $row['uq_account'];
$tmp[$row['uq_account']]['kd_parent'] = $row['kd_parent'];
$tmp[$row['uq_account']]['nama_akun'] = $row['nama_akun'];
$tmp[$row['uq_account']]['main'] = $row['main'];
#AKUN
$tmp[$row['uq_account']]['debit'] = $row['debit'];
$tmp[$row['uq_account']]['kredit'] = $row['kredit'];
$tmp[$row['uq_account']]['nominal_hr'] = $sb_countv['nominal_hr'];
if ($row['kd_parent'] == '0') {
$data[$row['uq_account']] = &$tmp[$row['uq_account']];
} else {
$tmp[$row['kd_parent']]['child'][$row['uq_account']] = &$tmp[$row['uq_account']];
}
}
function build_laba($array, $child = false) {
$html = '<table class="table w-[calc(100%+1px)] left-[-1px]">';
foreach ($array as $arr) {
$nominal = $arr['pos_saldo'] == 'debit' ?
$arr['kredit'] - $arr['debit'] :
$arr['debit'] - $arr['kredit'];
$nomittl = $arr['nominal_hr'];
$nominal = number_format($nominal, 0, '.', '.');
if($arr['main'] == 0){
$html .= '
<tr class="border-0 border-l">
<td class="p-0 pl-4">
<div class="py-1 my-1">
<div class="flex flex-nowrap space-x-2 px-2">
<div class="flex-1">
<i class="fa-regular fa-folder-closed"></i>
<span class="font-semibold text-sm">
'.$arr['nama_akun'].'
</span>
</div>
</div>
</div>';
if (key_exists('child', $arr)) {
$html .= build_laba($arr['child'], true);
}
$html .= '
</td>
</tr>';
} else{
$html .= '
<tr class="border-0 '.$brdr.'">
<td class="p-0 '.$hder.'">
<div class="py-1 hover:!bg-gray-100 text-gray-600">
<div class="flex flex-nowrap space-x-2 px-2">
<div class="flex-1">
<i class="fa-regular fa-folder"></i>
<span class="text-sm">'.$arr['nama_akun'].'</span>
</div>
<div class="flex-0">'.$nominal.'</div>
</div>
</div>
</td>
</tr> ';
}
#TOTAL AKUN
if($arr['main'] == 0){
$html .= '
<tr class="border-0 '.$brdr.'">
<td class="p-0 '.$hder.'">
<div class="py-1 my-1 '.$hdbg.'">
<div class="flex flex-nowrap space-x-2 px-2">
<div class="flex-1">
<i class="fa-regular fa-folder-closed"></i>
<span class="font-semibold text-sm">
Total '.$arr['nama_akun'].'
</span>
</div>
<div class="flex-0">
'.number_format($nomittl, 0, '.', '.').'
</div>
</div>
</div>
</td>
</tr>';
}
}
$html .= '</table>';
return $html;
}
echo build_laba($data);
?>
</div>
</div>
</div> any help I hope, thank you very much
>Solution :
Here’s a modified version of your code that should correctly calculate the sum of the child nodes and add it to the parent node’s total.
function calculateTotal(&$array) {
$total = 0;
foreach ($array as &$arr) {
if (isset($arr['child'])) {
$arr['nominal_hr'] += calculateTotal($arr['child']);
}
$total += $arr['nominal_hr'];
}
return $total;
}
// Call the function on your data array
calculateTotal($data);
// Now you can build your table as before
echo build_laba($data);