Sum to Root

[phphp]

Sir, I have these PHP 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 help

[rubberman]

So, how are you calculating that?

[diafol]

OK think I know what you want, but the calc in the image seems wrong.
Also what's the first sql query? Looks like you may be repeating it in the loop. So include your code from the strt of the first query

[phphp]

Thanks for reply.
Sir #diafol, here are complete codes including first query

// data comes from 11 tables,

$query = "SELECT
data.acc_code, sum(data.dr_amount)as debit1,sum(data.cr_amount)as credit1,
(case when sum(data.dr_amount) > sum(data.cr_amount)  then sum(data.dr_amount) - sum(data.cr_amount) end) as dr_amount,
(case when sum(data.dr_amount) < sum(data.cr_amount)  then sum(data.dr_amount) - sum(data.cr_amount) end) as cr_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
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>";
if ($res['dr_amount']!= 0 or $res['cr_amount']!= 0) {

    $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>";  

}

echo "</tr>";
}

[diafol]

My take on it would be this:

$query = "SELECT data.acc_code, master.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)) ;

$output = [];
$sno = 1;
$stringOutput = '';

//Preparing amounts
while($res = sqlsrv_fetch_array($result)) {

    $debitAmt = 0; $creditAmt = 0;
    $code = (string)$res['code'];
    $output[$code] = $res;

    if(strlen($code) == 7){
        $cut = substr($code,0,4);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$code]['my_amount'];
        }
    }

}

//Formatting
foreach($output as $v){
    $cr = '';
    $dr = '';
    if($v['my_amount'] > 0) $cr = number_format($v['my_amount']);
    if($v['my_amount'] < 0) $dr = number_format(abs($v['my_amount']));

    $stringOutput .= '<tr>';
    $stringOutput .= "<td align='center'>$sno</td>";
    $stringOutput .= "<td align='center'>{$v['acc_code']}</td>";
    $stringOutput .= "<td align='left' class='color1'>{$v['desc1']}</td>";
    $stringOutput .= "<td align='right'>$dr</td>";
    $stringOutput .= "<td align='right'>$cr</td>";
    $stringOutput .= '</tr>';
    $sno++;
}

?>

<table>
    <thead>
        <!-- whatever -->
    </thead>
    <tbody>
        <?=$stringOutput?>
    </tbody>
</table>

Unfortunately it uses two loops, but getting SQL to do the adding data to a previous record is a bit wonky.
All-in-one query which gets the description of the as well.
Not tested.

[phphp]

Sir many thank for helping,
I replaced code with acc_code in this section

//Preparing amounts
while($res = sqlsrv_fetch_array($result)) {
    $debitAmt = 0; $creditAmt = 0;
    $code = (string)$res['code'];
    $output[$code] = $res;
    if(strlen($code) == 7){
        $cut = substr($code,0,4);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$code]['my_amount'];
        }
    }
}

Now the above section is like this

while($res = sqlsrv_fetch_array($result)) {
    $debitAmt = 0; $creditAmt = 0;
    $acc_code = (string)$res['acc_code'];
    $output[$acc_code] = $res;
    if(strlen($acc_code) == 7){
        $cut = substr($acc_code,0,4);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$acc_code]['my_amount'];
        }
    }
   }

Becuase the actual field name in query is acc_code

But When I run php, there is neither error message nor result.

when I use this command

var_dump($output);

then it reply as

Please

[diafol]

Looks OK to me so far. Could do with a tweak maybe. Here are my suggestions:

//Preparing amounts
while($res = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {

    $code = (string)$res['acc_code'];
    $output[$code] = $res;

    if(strlen($code) == 7){
        $cut = substr($code,0,4);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$code]['my_amount'];
        }
    }

}

//Quick Test
$r = array_slice($res, 0, 20, true);
echo "<pre>";
print_r($r);
echo "</pre>";

//Formatting
foreach($output as $v){
    $cr = '';
    $dr = '';
    if($v['my_amount'] > 0) $cr = number_format($v['my_amount']);
    if($v['my_amount'] < 0) $dr = number_format(abs($v['my_amount']));

    $stringOutput .= '<tr>';
    $stringOutput .= "<td align='center'>$sno</td>";
    $stringOutput .= "<td align='center'>{$v['acc_code']}</td>";
    $stringOutput .= "<td align='left' class='color1'>{$v['desc1']}</td>";
    $stringOutput .= "<td align='right'>$dr</td>";
    $stringOutput .= "<td align='right'>$cr</td>";
    $stringOutput .= '</tr>';
    $sno++;
}

Remember that the data is contained in the $stringOutput variable. It is not going to display to the screen until you specifically echo it somewhere -prefereably in your html table. The quick test, limits the array to 20 rows, so that they can be read easily. Also change the sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC) - you probably don't need the numerical index too.

[phphp]

Thanks sir for writting this advice

Remember that the data is contained in the $stringOutput variable. It is not going to display to the screen until you specifically echo it somewhere -prefereably in your html table.

At the end of following cods I used this line

<?=$stringOutput?>

Now data is showing in relevant fields but the amount in 4 digit rows is still empty.

Here are complete codes set, ma be I am doing somthing wrong, please have a look .

       <?php

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)) ;  
//var_dump($result);
$result = sqlsrv_query($con,$query) or die ("Error". sqlsrv_errors($con)) ;
$output = [];
$sno = 1;
$stringOutput = '';
//Preparing amounts
while($res = sqlsrv_fetch_array($result)) {
    $debitAmt = 0;
    $creditAmt = 0;
    $acc_code = (string)$res['acc_code'];
    $output[$acc_code] = $res;
    if(strlen($acc_code) == 7){
        $cut = substr($acc_code,0,4);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$acc_code]['my_amount'];
        }
    }
}
////Quick Test
//$r = array_slice($res, 0, 20, true);
//echo "<pre>";
//print_r($r);
//echo "</pre>";
//var_dump($output);
//Formatting
foreach($output as $v){
    $cr = '';
    $dr = '';
    if($v['my_amount'] > 0) $cr = number_format($v['my_amount']);
    if($v['my_amount'] < 0) $dr = number_format(abs($v['my_amount']));
    $stringOutput .= '<tr>';
    $stringOutput .= "<td align='center'>$sno</td>";
    $stringOutput .= "<td align='center'>{$v['acc_code']}</td>";
    $stringOutput .= "<td align='left' class='color1'>{$v['desc1']}</td>";
    $stringOutput .= "<td align='right'>$dr</td>";
    $stringOutput .= "<td align='right'>$cr</td>";
    $stringOutput .= '</tr>';
    $sno++;
}

?>

<!--=============================================================================================
// Printing
//=============================================================================================-->
   <table>
    <thead align="center">
    <tr>
    <td width="10%">S.No</td>
    <td width="10%">Code</td>
    <td width="45%" align="left">Title of Account</td>
    <td width="15%">Debit</td>
    <td width="15%">Credit</td>
    </tr>
    </thead>
    <tbody>
    <?=$stringOutput?>
</tbody>
</table>

[diafol]

Ok now you have progress of a sort. Go into the loop for adding data to the four digit record and investigate. Am busy until tomorrow.

[phphp]

Sir,

I used this command

var_dump($output);

But 4 digit code still nat have values as shown in image

Please

[diafol]

Well from your var dump, you can see that the acc_code is set to 7 characters for some reason, even though they contain no chars, 1 char, 2 chars etc. You'll need to trim in this case. I stupidly assumed that your acc_code field was an integer field, not varchar. If you insist on using varchar for your integer data, then you just need this:

$code = trim($res['acc_code']); //instead of (string)$res['acc_code'];
$output[$code] = $res;

This may cause issues if you the trim() function returns an empty string, as in the first record - why do you have an empty record there?

[phphp]

Thanks Thanks sir #diafol

with your help I modified some code and know result is ok
I removed empty rows with adding this line

    if($dr>0 or $cr>0)  {   

The final code are here

       <?php

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

$output = [];
$sno = 1;
$stringOutput = '';
//Preparing amounts
while($res = sqlsrv_fetch_array($result)) {

    $debitAmt = 0;
    $creditAmt = 0;
    $acc_code = trim($res['acc_code']); //instead of (string)$res['acc_code'];
    $output[$acc_code] = $res;

        $cut = substr(trim($acc_code),0,4);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$acc_code]['my_amount'];

        }

        $cut = substr(trim($acc_code),0,2);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$acc_code]['my_amount'];
        }

        $cut = substr(trim($acc_code),0,1);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$acc_code]['my_amount'];
        }

}

//Formatting
foreach($output as $v){

    $cr = '';
    $dr = '';
    if($v['my_amount'] > 0) $cr = number_format($v['my_amount']);
    if($v['my_amount'] < 0) $dr = number_format(abs($v['my_amount']));

    if($dr>0 or $cr>0)  {   

    $stringOutput .= '<tr>';
    $stringOutput .= "<td align='center'>$sno</td>";
    $stringOutput .= "<td align='center'>{$v['acc_code']}</td>";
    $stringOutput .= "<td align='left' class='color1'>{$v['desc1']}</td>";
    $stringOutput .= "<td align='right'>$dr</td>";
    $stringOutput .= "<td align='right'>$cr</td>";
    $stringOutput .= '</tr>';
    $sno++;
    }
}
?>

<!--=============================================================================================
// Printing
//=============================================================================================-->
<br />
   <table>
    <thead align="center">
    <tr>
    <td width="10%">S.No</td>
    <td width="10%">Code</td>
    <td width="45%">Title of Account</td>
    <td width="15%">Debit</td>
    <td width="15%">Credit</td>
    </tr>
    </thead>
    <tbody>
    <?=$stringOutput?>
</tbody>
</table>
</div>

But now there is a last request to calculate sum of all 4 digit codes like shown in image
I mean how to extract required date from

<?=$stringOutput?>

or with some other method.

acc_code is varchar.

Please

[diafol]

This is another layer of complexity not expected. I'm assuming that the totals (debit and credit) as tota of 4-digit codes are simply the sums of seven digit acc_codes (as calculated previously)? Unfortunately this may require a slightly different approach.

How about you have a go and see what you get. I believe I've "given" you a lot of help thus far, so attempt a solution yourself and if you run into an issue, post back.

[phphp]

SIR #diafol

I wrote a long query to get the sum of all 4 digit codes,
The result is ok.
Please review if you can make it SHORTER.

<?php
$query = "SELECT distinct(LEFT(data.acc_code,4)) as acc_code,
sum(isnull(data.dr_amount,0))as dr_amount1,
sum(isnull(data.cr_amount,0))as  cr_amount1,
(case when sum(isnull(data.dr_amount,0)) + sum(isnull(data.cr_amount,0))>0  then sum(isnull(data.dr_amount,0)) + sum(isnull(data.cr_amount,0)) end) as dr_amount,
(case when sum(isnull(data.dr_amount,0)) + sum(isnull(data.cr_amount,0))<0  then sum(isnull(data.cr_amount,0)) + sum(isnull(data.dr_amount,0)) end) as cr_amount
   from (
SELECT max(acc_code)as acc_code,
(case when sum(open_dr) > sum(open_cr)  then sum(open_dr) - sum(open_cr) end) as dr_amount,
(case when sum(open_dr) < sum(open_cr)  then sum(open_dr) - sum(open_cr) end) as cr_amount
 from master where  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code, 
(case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
 from vouchers  where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code, 
(case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from cashsalp   where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code,
(case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from crsalp    where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code, (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from brsalp  where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code, (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from mrsalp    where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code, (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from orsalp     where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code,  (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from frsalp  where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code,  (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from Srsalp    where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code, (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from cashprp   where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
union all
SELECT max(acc_code)as acc_code, (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
  from crprp    where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4))
 as data
where  len(acc_code)=7  group by left(acc_code,4)";

$result = sqlsrv_query($con,$query) or die ("Error". sqlsrv_errors($con)) ;  

$sumdr=0;
$sumcr=0;

while($res = sqlsrv_fetch_array($result)) {
$sumdr=$sumdr+($res['dr_amount']);
$sumcr=$sumcr+($res['cr_amount']);
}

echo "<td></td>";
echo "<td></td>";
echo "<td align='Center'><span style='color:brown;font-size:14px;font-weight:bolder;'>Total</span></td>";
echo "<td align='right'>".number_format($sumdr)."</td>";
echo "<td align='right'>".number_format(abs($sumcr))."</td>";

echo " Total of 4 digits Codes   =       "      .number_format($sumdr).  "  |   "    .number_format(abs($sumcr));

?>

 ![Untitled.png](/attachments/large/4/d2fa16a6e4c88a29a39a4e94dc2bacf6.png "align-center") 

[diafol]

This may help - minimal changes from previous version I posted:

$query = "SELECT data.acc_code, master.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)) ;

$output = [];
$sno = 1;
$stringOutput = '';

$bigDebit = 0; //add this
$bigCredit = 0; // add this

//Preparing amounts
while($res = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {

    $code = trim($res['acc_code']);
    $output[$code] = $res;

    if(strlen($code) == 7){
        $cut = substr($code,0,4);

        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$code]['my_amount'];
            if($output[$code]['my_amount'] > 0)$bigCredit += $output[$code]['my_amount']; //add this
            if($output[$code]['my_amount'] < 0)$bigDebit += -$output[$code]['my_amount']; //add this
        }
    }

}

foreach($output as $v){
    $cr = '';
    $dr = '';
    if($v['my_amount'] > 0) $cr = number_format($v['my_amount']);
    if($v['my_amount'] < 0) $dr = number_format(abs($v['my_amount']));

    $stringOutput .= '<tr>';
    $stringOutput .= "<td align='center'>$sno</td>";
    $stringOutput .= "<td align='center'>{$v['acc_code']}</td>";
    $stringOutput .= "<td align='left' class='color1'>{$v['desc1']}</td>";
    $stringOutput .= "<td align='right'>$dr</td>";
    $stringOutput .= "<td align='right'>$cr</td>";
    $stringOutput .= '</tr>';
    $sno++;
}
?>

<?=$bigDebit . '|' . $bigCredit?> <!--add this-->
<table>
    <thead>
        <!-- whatever -->
    </thead>
    <tbody>
        <?=$stringOutput?>
    </tbody>
</table>

[phphp]

Thanks Sir #diafol

I tested it in manay ways, but this command is SUMMING data of all 7 digit codes instead of 4 digits.

if($output[$acc_code]['my_amount'] > 0)$bigCredit += $output[$acc_code]['my_amount']; //add this
if($output[$acc_code]['my_amount'] < 0)$bigDebit += -$output[$acc_code]['my_amount']; //add this

I think the command needs to add the reference of all 4 digit codes.

Thanks again

[diafol]

Check my logic: four char codes are the sum of seven char codes. You state the totals are the sums of four char codes. Does it not follow that the totals therefore are the sums of seven char codes?

[phphp]

Check my logic: four char codes are the sum of seven char codes. You state the totals are the sums of four char codes. Does it not follow that the totals therefore are the sums of seven char codes?

Sir, Four char codes are NOT the sum of seven char codes.
The data in 4 char codes comes after applying (credit-debit) with all 7 digit codes like

Code        Debit   Credit
1101               100
1101001     900     100
1101002     600     1000
1101003     700     1200

1102                  150
1102001             150

1201          250   
1201001              450
1201002              500
1201003     1200    

Please see attachment also

This part of codes display the total of all 7 digit codes. (3400 for above data But I need 250)

 if(strlen($code) == 7){
        $cut = substr($code,0,4);
        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$code]['my_amount'];
            if($output[$code]['my_amount'] > 0)$bigCredit += $output[$code]['my_amount']; //add this
            if($output[$code]['my_amount'] < 0)$bigDebit += -$output[$code]['my_amount']; //add this
        }
    }

Thanks

[diafol]

Ok will have a look but will be later.

[diafol]

Maybe this:

$query = "SELECT data.acc_code, master.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)) ;

$output = [];
$sno = 1;
$stringOutput = '';

//Preparing amounts
while($res = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {

    $code = trim($res['acc_code']);
    $output[$code] = $res;

    if(strlen($code) == 7){
        $cut = substr($code,0,4);

        if(isset($output[$cut])){
            $output[$cut]['my_amount'] += $output[$code]['my_amount'];
        }
    }

}

$bigDebit = 0;
$bigCredit = 0;

foreach($output as $k=>$v){
    $cr = '';
    $dr = '';

    if($v['my_amount'] > 0) $cr = number_format($v['my_amount']);
    if($v['my_amount'] < 0) $dr = number_format(abs($v['my_amount']));

    if( strlen($k) == 4 ){
        if($v['my_amount'] > 0) $bigCredit += number_format($v['my_amount']);
        if($v['my_amount'] < 0) $bigDebit += number_format(abs($v['my_amount']));
    }

    $stringOutput .= '<tr>';
    $stringOutput .= "<td align='center'>$sno</td>";
    $stringOutput .= "<td align='center'>{$v['acc_code']}</td>";
    $stringOutput .= "<td align='left' class='color1'>{$v['desc1']}</td>";
    $stringOutput .= "<td align='right'>$dr</td>";
    $stringOutput .= "<td align='right'>$cr</td>";
    $stringOutput .= '</tr>';
    $sno++;
}
?>

<?=$bigDebit . '|' . $bigCredit?> <!--add this-->
<table>
    <thead>
        <!-- whatever -->
    </thead>
    <tbody>
        <?=$stringOutput?>
    </tbody>
</table>

Be interesting to see if your big query is quicker than the two loop method I posted. I generally try to keep the SQL as simple as possible, but that's not always the appropriate solution.

[phphp]

Thanks but many thanks my Great Professor sir #diafol,

I used this line your codes

  echo $k.'|'.$v['my_amount']."<br>";

Result

That wast enough to ensure that query is getting the sum of all four digit codes but the result was ZERO.
Then I changed these line

if( strlen($k) == 4 ){
        if($v['my_amount'] > 0) $bigCredit += number_format($v['my_amount']);
        if($v['my_amount'] < 0) $bigDebit += number_format(abs($v['my_amount']));
    }

with

if( strlen($k) == 4 ){
        if($v['my_amount'] > 0) $bigCredit +=($v['my_amount']);
        if($v['my_amount'] < 0) $bigDebit += (abs($v['my_amount']));
    }

The problem was in numbr_format function

After removing the function, the result is amazingly correct.

I learnt many new new techniques in this thread with your kind help.

Now, if you do not mind, this is last request,
I need some more help about following portion

<table>
    <thead align="center">
    <tr>
    <td width="10%">S.No</td>
    <td width="10%">Code</td>
    <td width="45%">Title of Account</td>
    <td width="15%">Debit</td>
    <td width="15%">Credit</td>
    </tr>
    </thead>
    <tbody>
    <?=$stringOutput?>

</tbody>
</table>

The data appears in table as

Is it possible to change bgcolor of all rows those have four digit codes like this

In simple case where we can detect row number, I use these codes

$i=0;

while($res = sqlsrv_fetch_array($result)) {

     if($i % 2 == 0) 
    {
      $bgcolor= "#b8d1f3";
   }
   else 
  {
     $bgcolor= "#dae5f4";
  }
   $i++;

echo "<tr bgcolor='$bgcolor'>";

But do not know how to use codes like above with

<?=$stringOutput?>

1000% Thanks

[diafol]

Ah. Yes number format would cause issues! You can use

$stringOutput .= '<tr>';

To:

$stringOutput .= ( strlen($k) == 4 ) )? '<tr class="feature-row">' : '<tr>';

Then you can use css on .feature-row{}

[phphp]

Sir, I have these codes now

// $stringOutput .= '<tr>'; // commented this line
      $stringOutput .= "(strlen($k)==4))? '<tr class='mycolor'>':'<tr>'";    // added this line
    $stringOutput .= "<td align='center'>$sno</td>";
    $stringOutput .= "<td align='center'>{$v['acc_code']}</td>";
    $stringOutput .= "<td align='left' class='color1'>{$v['desc1']}</td>";
    $stringOutput .= "<td align='right'>$dr</td>";
    $stringOutput .= "<td align='right'>$cr</td>";
    $stringOutput .= '</tr>';
    $sno++;

and code of mycolor class

.mycolor {
    color:#87ACE7;
    background-color:#9FF0BF;
}

But it shows this result instead of row format

Thanks

[diafol]

Take off the double quotes in line 2

[phphp]

Sir, Done like this

But it says:

May be I am doing something wrong.

Thanks

[pritaeas]

One ) too much.

[phphp]

Sir, 2 opened and 2 closed.
so which one is enough?

[diafol]

Opened 2 closed 3. Take off last closed.

[phphp]

Beleive me sir both lines present same result shown in image

$stringOutput .= "(strlen($k==4))? '<tr class='mycolor'>':'<tr>'";  
$stringOutput .= "(strlen($k)==4)? '<tr class='mycolor'>':'<tr>'";  

[diafol]

You didn.t take off the double quotes. Try

$stringOutput .= (strlen($k==4))? '<tr class='mycolor'>':'<tr>';