Help with complex table php MySQL

Hello all,

I am trying to build a personal budget web application. Currently I have 4 tables.
one table for the categories
one table for the sub_categories (items)
one table for bugeted items by month
one table for actual credits and debits

I then have a form that allows the user to select a date range to view a report of the budget. This spits out a table that looks like the attached.

I am currently achieving this table with this:


<?php
global $mainframe;

$database =& JFactory::getDBO();
$database->setQuery( '
SELECT category_name, category_item, a.cat_id
FROM jos_chronoforms_excategories a, jos_chronoforms_exsubcategories b
WHERE a.cat_id = b.cat_id
ORDER BY a.cat_id
');

$database->query();
$records = $database->loadAssocList();

$sdate = JRequest::getVar('date_start', '', 'post');
$edate = JRequest::getVar('date_end', '', 'post');

list($sday, $smonth, $syear) = explode("/", $sdate);
list($eday, $emonth, $eyear) = 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)+1;


?>
<html><head><title>Budget Tester</title>
<style type="text/css">
table td { border:1px solid gray; padding:0 7px;}
</style>
</head><body>

<div id="budget_table">
<table border=0 cellspacing=0 cellpadding=0 style='border-collapse:collapse'>
 <tr>
  <td><span>&nbsp;</span></td>
  <!-- loop for the number of months displayed -->
  <?php 
	 for($i=$start;$i<$nmonths;$i++){
		$z = mktime(0,0,0,$i,01,2010);
		echo '<td colspan=2><span class="month_tab">'.date("M",$z).'</span></td>'."\
\	";
	} 
  ?>
  <!--END LOOP -->
 </tr>
 <!-- BEGIN OUTERLOOP FOREACH CATEGORY -->
 <?php 

 $fcat = '';
 	foreach ($records as $record) {
	
		if($record['cat_id'] != $fcat){
			echo '
			<tr height="18px">
			  <td colspan='.$numspan.'><span class="cat_name">'.$record['category_name'].'</span></td>
			</tr>'."\
\	";			
			
			$fcat = $record['cat_id'];			
		}
		echo '
		<tr height="11px">
			<td rowspan=2><span>'.$record['category_item'].'</span></td>
		';
		// amount input and the amount difference
		for($i=$start;$i<$nmonths;$i++){
			echo '
			<td><span>&nbsp;</span></td>
			<td rowspan=2><span align=center style="text-align:center">&nbsp;</span></td>'."\
\	";
		}
		echo '
		</tr>
		<tr height="11px">
		';
		//amount budgeted
		for($j=$start;$j<$nmonths;$j++){
			echo '
			<td><span></span></td>'."\
\	";
		}
		echo '
		</tr>';
	}

?> 
</table>

</div>
</body>
</html>

But I need to populate that table with info from the other two tables (bugetbymonth and bugetinput)
I am a bit confused on what my query needs to look like and what the php would be to extract the data in the correct td.

Any Ideas??

Thanks


WHERE  [table].created_at >= [start date]
AND      [table].created_at <= [end date]

Thanks for the reply AlienDev,

Unfortunately I don’t quite follow. If my query looks like:


SELECT category_name, category_item, a.cat_id, c.budget_amount
FROM jos_chronoforms_excategories a, jos_chronoforms_exsubcategories b, jos_chronoforms_bdgtbymonth c
WHERE a.cat_id = b.cat_id
AND c.recordtime >= '.$sdate.'
AND c.recordtime <= '.$edate.'
ORDER BY a.cat_id

I get 0 returned rows??

How is the ‘recordtime’ field being stored (unixtime, YYYY-MM-DD, etc)?
When you do “var_dump($sdate);” what is output?

recordtime is (unixtime, YYYY-MM-DD - H:i:s);

the var_dump of sdate outputs:
string(10) “19/02/2010”

OK,

if I do this:

SELECT category_name, category_item, a.cat_id, budget_amount
FROM jos_chronoforms_excategories a, jos_chronoforms_exsubcategories b, jos_chronoforms_bdgtbymonth c, jos_chronoforms_budgetinfo d
WHERE a.cat_id = b.cat_id
AND c.recordtime BETWEEN "' .$sdate.' - 12:00:00" AND "'.$edate.' - 12:00:00"
ORDER BY a.cat_id

the table looks like (see attached)

ok,

I am making a little progress - but am stuck on navigating to the end of the loop if my record does not have a month in the array of months for the report.

For example if my record contains months 01,02 and 03
And my array of months to display is 01,02,03,04,and 05

The first thing I need to do is check that the $dbmonth[i] is in the $arraymonths.
if so output the table data

then I need to check if the $dbmonth array has more matches.
if so I need to loop through and output table data for the matches

But the tricky part is I need to output blank table data for the non-matched until the table row is complete and then close the <tr>

Obviously I need a condition - but I cant figure out where.
here is what I have:

 	foreach ($records as $record) {
		// 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.'><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>'.$record['category_item'].'</span></td>
		';	

		$months = explode(",",$record['select_month']);
		$monthcount = count($months);

		foreach($months as $month){			
			//check if the records month is in the array of months displayed
			if(in_array($month, $myarr)){
				//build the row info for this month
			
			}
			
		}

maybe my problem is that I keep trying to populate this table with data from only one query??

I don’t know the details of your tables so I can’t write a prefect SQL.

But first of all, if you want to select data from different tables, it is best to use a join.
I assumed that all tables have the same key_name (cat_id)

Next thing is that when selecting from multiple tables, always use the table alias as prefix; I dodn’t know which data from which table so as example I choose a.

But this should give you a better idea on how to select from multiple tables.


SELECT 
    a.category_name AS 'category_name', 
    a.category_item AS 'category_item', 
    a.cat_id        AS 'cat_id', 
    a.budget_amount
FROM 
    jos_chronoforms_excategories a,
    LEFT JOIN
        jos_chronoforms_exsubcategories b,
    USING 
        (cat_id)
    LEFT JOIN
        jos_chronoforms_bdgtbymonth c,
    USING
        (cat_id)
    LEFT JOIN
        jos_chronoforms_budgetinfo d
    USING
        (cat_id)
WHERE 
    c.recordtime BETWEEN "' .$sdate.' - 12:00:00" AND "'.$edate.' - 12:00:00"
ORDER BY 
    a.cat_id

to populate an empty row, you need to size of the record by using the count function on the array. Then when the desired month is nit in the array of months just loop from 0 to the size of the recored, echo-ing each time a cell html

well, breaking up the query into parts certainly helped. Now I have the table populating with all info from the db. Now I just need to sum the info from each category - off the the far right, and sum the sub-categories at the bottom of each category.

this is what I have:

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

$database =& JFactory::getDBO();
$database->setQuery( '
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>&nbsp;</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 -->
 </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)
				$sm = "0".$tr."/".$syear;
			else
				$sm = $tr."/".$syear;
			//need new query here 
			$database =& JFactory::getDBO();
			$database->setQuery( '
			SELECT expendature, budget_amount, budget_amount - expendature AS remaining
			FROM jos_chronoforms_budgetinfo a, jos_chronoforms_bdgtbymonth b
			WHERE date_3 LIKE "%'.$sm.'%"
			AND a.select_category = '.$sc.'
			AND a.itemList = '.$il.'
			AND a.select_category = b.select_category
			AND a.itemList = b.itemList
			');

			$database->query();
			$toprecs = $database->loadAssoc();			
			
			if($toprecs){
			echo '
			<td class="green"><span>'.$toprecs['expendature'].'</span></td>
			<td rowspan=2 class="';
			
			if($toprecs['remaining']<0)
				echo 'alert"><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">&nbsp;</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>

Attached is a thumb of what I have currently and what I am attempting.

Thanks for your reply fristi,

I kinda understood it all.
Do you think one query is the better way to go??

I saw this which excited me about the possibility of doing the totals.

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>&nbsp;</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">&nbsp;</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 ;

Hmmmm

I would do things a different way, so it’s not that easy for me to uderstand everything you are trying to achieve here.

Let me start with a question.
From what I understand categories are just a grouping of items, and an item can not be part of 2 categories.
So why do you have the expenses linked to both items and categories?
That makes it unnessasary complicated.

Also, not everything needs to be fetched in one query, but try to keep the amount of different queries to a minimum. The more queries, the more interaction with the DB server, the more time and resources it takes.
So I personally would try to keep the queries out of a loop.
Select things into a couple of arrays and then loop through the arrays.

Example could be that at the beginning you determine the months you will display, then fetch all the items where the dates belong to those months.
And with the looping, just loop through the array, instead of every time fecthing items for 1 particular month inside the loop itself.

Also, using varchar for date fields is not the bets way to go. That way other programmers, or even you in the future, won’t know anymore what the syntax was you used. I for example couldn’t figure out what is inside date_3… it is something like the month/year but since to fecth it you used the LIKE operator, it could be that more info is stored in one field… Always try to keep things as clear as possible!
Like names, you mix up subcategories and items… keep things consequent and try to call everything items or subcategories, but don’t switch between the two for the same thing…

One last advice:


SELECT 
    a.category_name, 
    b.category_item, 
    a.cat_id, 
     b.item_id
FROM 
     jos_chronoforms_exsubcategories b
     LEFT JOIN 
           jos_chronoforms_excategories a
     USING ( cat_id )
ORDER BY 
     a.cat_id, b.item_id

one category has more sub categories, so you want to select all subcategories first and then link all the categories. Not the other way around :wink:

I know this isn’t a ready made solution, but I think advice to send you in the right direction is more valuable than me just giving you a rewritten code.

But please ask if you get stuck somewhere particular :slight_smile:

Thanks fristi!

I agree. I am looking for advice - not written code already working. My goal is to understand as well as develop.

Anyway, I will try implementing your advice. Couple of questions:

  1. I’m not sure what you meant here,

“one category has more sub categories, so you want to select all subcategories first and then link all the categories. Not the other way around”

  1. The table structure was created by the forms (I know that is not ideal) so that is why there is both. I assume that you were saying I can get rid of the cat_id field on at least two of my tables since the subcategory can only belong to one category.

opps