Sum to root

Sir, I have these codes

    while($res = sqlsrv_fetch_array($result)) {
echo "<tr>";
	$sno=$sno+1;
		echo "<td align='center'>$sno</td>";	
		echo "<td align='center'>".($res['acc_code'])."</td>";	
		$partycode=($res['acc_code']);
		$query1 = "select code,desc1 from master where code='".$partycode."'";
		$result1 = sqlsrv_query($con,$query1) or die ("Error". sqlsrv_errors($con)) ;  
		while($res1 = sqlsrv_fetch_array($result1)) {
		echo "<td align='left' class='color1'>".($res1['desc1'])."</td>";
		}
		echo "<td align='right'>".($res['dr_amount'] != 0 ? number_format(abs($res['dr_amount'])):'')."</td>";	
		echo "<td align='right'>".($res['cr_amount'] != 0 ? number_format(abs($res['cr_amount'])):'')."</td>";	

}

The query generates this result

But I need this result

I mean the sum of all 7 digit code to 4 digit codes like shown in red square.

please see this image for more detail


Please help

something like (pseudocode)

$results = sqlsrv_fetch_all // or something else to get all records
// make a new array where the code is the key; if the code is unique
foreach($results){
	if(strlen(code) > 4){
		$results[firstfourcharsof(code)][value] += $result[value];
	}
}

Are those commas I see in the amount column values?

What database are you using that allows commas in numeric datatypes?

Sir #chom, thansk for reply, I modify your codes as

$results = sqlsrv_fetch_array($result);
		foreach($results as $key){
		if(strlen($key)>4){
		$results2[substr($acc_code,0,3)][value] += $results[value];
			}
		}

But it says:
Notice: Undefined variable: acc_code in C:\wamp\www\atsql17_php\trial_blance2.php on line 384

Please help

so… where does this variable come from? also sqlsrv_fetch_array only returns one row, not all. and i don’t see that your results are indexed like i menioned on the second line.

Isn’t that number_format() in the original code?

D’oh !!
Right you are.

I was looking at the “I need this result with query” image thinking it was a capture of the actual table data.

@tqmd1 can you please post the code involved with $res and $result
(the code involving the query that comes before what you posted in the OP).

sir here are complete codes

global $mybalance;
$mybalance=0;
$mydrcr='';
$query = "SELECT data.acc_code,max(master.desc1)as desc1, (SUM(data.cr_amount) - SUM(data.dr_amount)) AS my_amount
FROM (
SELECT acc_code, open_dr AS dr_amount, open_cr AS cr_amount FROM master
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM vouchers WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM cashsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM crsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM brsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM mrsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT  acc_code, dr_amount, cr_amount FROM orsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM frsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM Srsalp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM cashprp WHERE date <= '".$t_date1."'
UNION ALL
SELECT acc_code, dr_amount, cr_amount FROM crprp WHERE date <= '".$t_date1."'
) AS data
LEFT JOIN master ON data.acc_code = master.code
GROUP BY data.acc_code
ORDER BY data.acc_code";
$result = sqlsrv_query($con,$query) or die ("Error". sqlsrv_errors($con)) ;  


while($res = sqlsrv_fetch_array($result)) {
echo "<tr>";
    $sno=$sno+1;
        echo "<td align='center'>$sno</td>";    
        echo "<td align='center'>".($res['acc_code'])."</td>";  
        $partycode=($res['acc_code']);
        $query1 = "select code,desc1 from master where code='".$partycode."'";
        $result1 = sqlsrv_query($con,$query1) or die ("Error". sqlsrv_errors($con)) ;  
        while($res1 = sqlsrv_fetch_array($result1)) {
        echo "<td align='left' class='color1'>".($res1['desc1'])."</td>";
        }
        echo "<td align='right'>".($res['dr_amount'] != 0 ? number_format(abs($res['dr_amount'])):'')."</td>";  
        echo "<td align='right'>".($res['cr_amount'] != 0 ? number_format(abs($res['cr_amount'])):'')."</td>";  
}

the database layout looks messed up, why are there so many tables with identical column names?

Becuase data comes from many tables.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.