I revised the sql as suggested.
I noticed that the comma’s should not be in the join syntax (right?)
Here is my page now:
<?php
list($syear, $smonth, $sday) = explode("-", $sdate);
list($eyear, $emonth, $eday) = explode("-", $edate);
$sd = mktime(0,0,0,$smonth,01,$syear);
$ed = mktime(0,0,0,$emonth,01,$eyear);
$start = date("m", $sd);
$end = date("m", $ed);
$nmonths = $end + 1;
$numspan = ($end * 2)+3;
$database =& JFactory::getDBO();
$database->setQuery( '
SELECT a.category_name, b.category_item, a.cat_id, b.item_id
FROM jos_chronoforms_excategories a
LEFT JOIN jos_chronoforms_exsubcategories b
USING ( cat_id )
ORDER BY a.cat_id, b.item_id
');
/*
SELECT category_name, category_item, a.cat_id, item_id
FROM jos_chronoforms_excategories a, jos_chronoforms_exsubcategories b
WHERE a.cat_id = b.cat_id
ORDER BY a.cat_id, item_id
');
*/
$database->query();
$records = $database->loadAssocList();
//AND date_3 BETWEEN "'.$sdate.' - 12:00:00" AND "'.$edate.' - 12:00:00"
?>
<html><head><title>Budget Tester</title>
<style type="text/css">
table td { border:1px solid #ccc; padding:0 7px;}
td.green {background:#E4EFF3; font-type:Trebuchet MS; font-size:9pt;}
td.alert {background:#F28686; font: bold Trebuchet MS 12pt; color:yellow;}
td.safe {background:#E4EFF3; font:bold Trebuchet MS 12pt;}
</style>
</head><body>
<div id="budget_table">
<table border=0 cellspacing=0 cellpadding=0 style='border-collapse:collapse'>
<tr>
<td><span> </span></td>
<!-- loop for the number of months displayed -->
<?php
$myarr = array($start);
$fmonth = '';
for($i=$smonth;$i<$nmonths;$i++){
$z = mktime(0,0,0,$i,01,2010);
if($start != date("m",$z)){
$montharr = array_push($myarr, date("m",$z));
}
echo '<td colspan=2><span class="month_tab">'.date("M",$z).'</span></td>'."\
\ ";
}
?>
<!--END LOOP -->
<td colspan=2><span class="total_tab">Total</span></td>
</tr>
<!-- BEGIN OUTERLOOP FOREACH CATEGORY -->
<?php
$fcat = '';
foreach ($records as $record) {
$sc = $record['cat_id'];
$il = $record['item_id'];
// write the main category and give it a colspan as wide as the table
if($record['cat_id'] != $fcat){
echo '
<tr height="18px">
<td colspan='.$numspan.' style="';
if($record['cat_id']==1)
echo "background:#006500;";
else
echo "background:#346378;";
echo 'color:white;"><span class="cat_name">'.$record['category_name'].'</span></td>
</tr>'."\
\ ";
$fcat = $record['cat_id'];
}
//write the subcategories
echo '
<tr height="11px">
<td rowspan=2><span class="cat_item">'.$record['category_item'].'</span></td>
';
//toprow
for($tr=$start;$tr<$nmonths;$tr++){
if($tr<10 && $tr!=$start)
$dm = "0".$tr."/".$syear;
else
$dm = $tr."/".$syear;
if($tr<10 && $tr!=$start)
$sm = "0".$tr;
else
$sm = $tr;
//need new query here
$database =& JFactory::getDBO();
$database->setQuery( '
SELECT SUM(expendature) AS ex, budget_amount, budget_amount - SUM(expendature) AS remaining
FROM jos_chronoforms_budgetinfo a
LEFT JOIN jos_chronoforms_bdgtbymonth b
USING (select_category)
WHERE a.date_3 LIKE "%'.$dm.'%"
AND b.select_month LIKE "%'.$sm.'%"
AND a.select_category ='.$sc.'
AND a.itemList ='.$il.'
AND a.itemList = b.itemList
ORDER BY a.date_3
');
$database->query();
$toprecs = $database->loadAssoc();
//echo 'this is toprecs'.$toprecs;
if($toprecs){
echo '
<td class="green"><span>'.$toprecs['ex'].'</span></td>
<td rowspan=2 class="';
if($toprecs['remaining']<0)
echo 'alert"><span align=center style="text-align:center"> '.$toprecs['remaining'].'</span></td>'."\
\ ";
else if($toprecs['remaining']>0)
echo 'safe"><span align=center style="text-align:center">+ '.$toprecs['remaining'].'</span></td>'."\
\ ";
else
echo 'safe"><span align=center style="text-align:center">'.$toprecs['remaining'].'</span></td>'."\
\ ";
}else{
echo '
<td class="green"><span>0</span></td>
<td rowspan=2 class="green"><span align=center style="text-align:center"> </span></td>'."\
\ ";
}
}
echo '
</tr>
<tr height="11px">
';
//bottom row
for($br=$start;$br<$nmonths;$br++){
if($br<10 && $br!=$start)
$sm = "0".$br;
else
$sm = $br;
//need another query
$database =& JFactory::getDBO();
$database->setQuery( '
SELECT select_month, select_year, budget_amount
FROM jos_chronoforms_bdgtbymonth
WHERE select_month LIKE "%'.$sm.'%"
AND select_category = '.$sc.'
AND itemList = '.$il.'
ORDER BY select_category, itemList
');
$database->query();
$bottomrecs = $database->loadAssoc();
if($bottomrecs){
//amount budgeted
echo '
<td class="green"><span>'.$bottomrecs['budget_amount'].'</span></td>'."\
\ ";
}else{
//amount budgeted
echo '
<td class="green"><span>0</span></td>'."\
\ ";
}
}
echo '
</tr>';
}
?>
</table>
</div>
</body>
</html>
and here is a sql dump of my db structure
-- phpMyAdmin SQL Dump
-- version 3.1.3.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 02, 2010 at 10:54 PM
-- Server version: 5.1.33
-- PHP Version: 5.2.9
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `andy`
--
-- --------------------------------------------------------
--
-- Table structure for table `jos_chronoforms_bdgtbymonth`
--
CREATE TABLE IF NOT EXISTS `jos_chronoforms_bdgtbymonth` (
`bdgtgoal_id` int(11) NOT NULL AUTO_INCREMENT,
`uid` varchar(255) NOT NULL,
`recordtime` varchar(255) NOT NULL,
`budget_amount` varchar(255) NOT NULL,
`select_year` varchar(255) NOT NULL,
`select_month` varchar(255) NOT NULL,
`select_category` int(11) NOT NULL,
`itemList` int(11) NOT NULL,
PRIMARY KEY (`bdgtgoal_id`),
KEY `bdgtgoal_id` (`bdgtgoal_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ;
-- --------------------------------------------------------
--
-- Table structure for table `jos_chronoforms_budgetinfo`
--
CREATE TABLE IF NOT EXISTS `jos_chronoforms_budgetinfo` (
`bdgt_id` int(11) NOT NULL AUTO_INCREMENT,
`uid` varchar(255) NOT NULL,
`recordtime` varchar(255) NOT NULL,
`expname` varchar(255) NOT NULL,
`expendature` varchar(255) NOT NULL,
`date_3` varchar(255) NOT NULL,
`select_category` int(11) NOT NULL,
`itemList` int(11) NOT NULL,
PRIMARY KEY (`bdgt_id`),
KEY `bdgt_id` (`bdgt_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;
-- --------------------------------------------------------
--
-- Table structure for table `jos_chronoforms_excategories`
--
CREATE TABLE IF NOT EXISTS `jos_chronoforms_excategories` (
`cat_id` int(11) NOT NULL AUTO_INCREMENT,
`category_name` varchar(255) NOT NULL,
`uid` varchar(255) NOT NULL,
`recordtime` varchar(255) NOT NULL,
PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;
-- --------------------------------------------------------
--
-- Table structure for table `jos_chronoforms_exsubcategories`
--
CREATE TABLE IF NOT EXISTS `jos_chronoforms_exsubcategories` (
`item_id` int(11) NOT NULL AUTO_INCREMENT,
`category_item` varchar(255) NOT NULL,
`cat_id` int(11) NOT NULL,
`uid` varchar(255) NOT NULL,
`recordtime` varchar(255) NOT NULL,
PRIMARY KEY (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=76 ;
