/////Get gl accounts class/////
$sql_class = "SELECT cid,class_name FROM 0_chart_class WHERE cid>=3 ORDER BY cid ASC";
$result_class = mysql_query($sql_class) or die("Error in query: $sql_project.".mysql_error());
echo "<TABLE class='swPrpTopMenu'>";
echo "<th>Account Code</th><th>Name</th><th>Amount</th>";
while($row_class=mysql_fetch_array($result_class ))
{
    $c_id=$row_class['cid'];
    $c_name=$row_class['class_name'];
echo "<tr><td>$c_name</td></tr>";
////////get accounts 
$sql_accounts="select ct.name,ct.class_id,cm.account_code,cm.account_name FROM 0_chart_types ct,0_chart_master cm
WHERE ct.id=cm.account_type AND ct.class_id='$c_id' GROUP BY name ORDER BY account_code ASC";
$result_accounts=mysql_query($sql_accounts);
while($row_accounts=mysql_fetch_array($result_accounts))
{

$account_code=$row_accounts['account_code'];
$account_name=$row_accounts['account_name'];
$main_account=$row_accounts['name'];
echo "<tr><td>$main_account</td></tr>";
    $sql_res=mysql_query("SELECT SUM(amount) FROM general_ledger
        WHERE account='$account_code'");
    while($row_res=mysql_fetch_array($sql_res))
    {


   echo " <tr><td>$account_code</td><td>$account_name</td><td>$row_res[total]</td></tr>";//logic error here

    }
}
}

The problem with the above code is that it returns one row only. Each $account_name above has several sub categories which I want to list plus the sum of their "amount" column.

Can somebody help. see the part commented as "logic error here"

Thanks.

is there any chance for you to use JOIN ? Those triple loops are just mind boggling to me.

Dear Veedeoo,
if you prefer joins, then the query is:

SELECT c.class_name as m_chart,t.name as sub_chart, SUM( amount ) AS total, a.account_code, a.account_name AS account_name, c.ctype
FROM general_ledger, chart_of_account AS a, chart_types AS t, master_chart AS c
WHERE account = a.account_code
AND a.account_type = t.id
AND t.class_id = c.cid
AND (
c.cid =3
OR c.cid =4
)
AND IF( c.ctype >3, tran_date >= '2013-01-01', tran_date >= '0000-00-00' )
AND tran_date <= '2014-07-26'
GROUP BY account_name
ORDER BY c.cid

And the output should look like:

m_chart

sub_chart
account_code account_name amount

Total for sub_chart=amount
.....
..
Total for m_chart=sum of all amounts for sub_chart

Regards,