Mysql cross tab report

[mwenyenia07]

/////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.

[veedeoo]

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

[mwenyenia07]

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,