help display of query results

[samoht]

hello all,

 

I have this query:

SELECT select_month
, SUM( IF( cat_id = "1", budget_amount, 0 ) ) AS "TOTAL INCOME"
, SUM( IF( cat_id != "1", budget_amount, 0 ) ) AS "TOTAL EXPENSE"
, SUM( IF( cat_id = "1", budget_amount, 0 ) ) - SUM( IF( cat_id != "1", budget_amount, 0 ) ) AS "NET INCOME"
, SUM( IF( cat_id = "1", budget_amount, 0 ) ) - SUM( IF( cat_id != "1", budget_amount, 0 ) ) + start_balance AS "END BALANCE"
FROM bdgt_startbalance, bdgtbymonth INNER JOIN bdgtcategoryitems
USING ( item_id )INNER JOIN bdgt_month_year_combo
USING ( monthly_budget_id )
GROUP BY select_month

 

which gives me this result:

select_month 	TOTAL INCOME 	TOTAL EXPENSE 	NET INCOME 	END BALANCE
2010-01-01 	3605 	1901 	1704 	3440
2010-02-01 	3050 	1637 	1413 	3149
2010-03-01 	1850 	1812 	38 	1774
2010-04-01 	250 	1812 	-1562 	174
2010-05-01 	250 	1812 	-1562 	174

 

NB: THESE are not real numbers but just used for testing!

 

however, what I would like to display would be something like:

select_month	2010-01-01 	2010-02-01 	2010-03-01 	2010-04-01 	2010-05-01 
TOTAL INCOME 	3605 	3050 	1850 	250 	250 
TOTAL EXPENSE 	1901 	1637 	1812 	1812 	1812 
NET INCOME 	1704 	1413 	38 	-1562 	-1562 
END BALANCE	3440	3149	1774	174	174

 

I am stumped on how I could write this to a table this way. So they I thought maybe I could use float left Div's ??

 

Please Help

 

Thanks,

[samoht]

Would something like this work:

$database->query();
$totals = $database->loadAssocList();
foreach($totals as $total){
if($total['select_month']=="2010-01-01"){
	$jan = array( // put all the january totals in this array);
}
else if($total['select_month']=="2010-02-01"){
	$feb = array( // put all the febuary totals in this array);
}
}

 

And then when building my table just use the $jan['TOTAL INCOME'] etc??

 

[samoht]

OK,

 

I was thinking that maybe this would work better:

foreach($totals as $total){
if($total['TOTAL INCOME']){
	$ti = array($total['TOTAL INCOME']);
}
else if($total['TOTAL EXPENSE']){
	$te = array($total['TOTAL EXPENSE']);
}
else if($total['NET INCOME']){
	$ni = array($total['NET INCOME']);
}
else if($total['END BALANCE']){
	$eb = array($total['END BALANCE']);
}

}

But that obviously only gets my the last value in the array. ??

 

Any ideas??

[samoht]

a little progress here:

$frec = '';
foreach($totals as $total){
if($total['select_month'] != $frec){
	$tia = array($total['TOTAL INCOME']);
	$tea = array($total['TOTAL EXPENSE']);
	$nia = array($total['NET INCOME']);
	$eba = array($total['END BALANCE']);

	$frec = $total['select_month'];
}
array_push($tia, $total['TOTAL INCOME']);
array_push($tea, $total['TOTAL EXPENSE']);
array_push($nia, $total['NET INCOME']);
array_push($eba, $total['END BALANCE']);
}
print_r($tia);

But this still is only giving me the last 2 entries??

 

I checked the $totals array to make sure it was loaded with all the values I want - and it was.

So why do I only get the last 2??