help with complex table

[samoht]

hello all,

I am writing a php mysql personal budget. I have created the tables and forms for storing the data - now I am getting ready to create the display pages. One of the pages I want to display a table with a selected number of months on the top row. The rows are comprised of the categories and the subcategories with the budgeted amount, the amount spent, and the amount difference (calculated) all next to each other for easy reference. 

 

here is an example of the way the HTML output might be:

<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 -->
  <td colspan=2><span align=center style='text-align:center'>Jan</span></td>
  <td colspan=2><span align=center style='text-align:center'>Feb</span></td>
  <td colspan=2><span align=center style='text-align:center'>Mar</span></td>
  <!--END LOOP -->
</tr>
<!-- BEGIN OUTERLOOP FOREACH CATEGORY -->
<tr height="18px">
<!-- colspan below should equal $i number of months *2 +1 -->
<td colspan=7><span>Home Expenses</span></td>
</tr>
<!-- loop foreach item record within the category-->
<tr height="11px">
  <td rowspan=2><span>Mortgage/Rent</span></td>
  <!-- the amount spent -->
  <td><span> </span></td>
  <!-- the calculated amount difference -->
  <td rowspan=2><span align=center style='text-align:center'> </span></td>
  <td><span> </span></td>
  <td rowspan=2><span align=center style='text-align:center'> </span></td>
  <td><span> </span></td>
  <td rowspan=2><span align=center style='text-align:center'> </span></td>
</tr>
<tr height="11px">
<!--the Budgeted amount -->
  <td><span>825</span></td>
  <td><span>825</span></td>
  <td><span>825</span></td>
</tr>
<!-- end item foreach -->
<tr height="18px">
<!-- colspan below should equal $i number of months *2 +1 -->
<td colspan=7><span>Transportation</span></td>
</tr>
<!-- END CATEGORY FOREACH -->
</table>

</div>

 

and here is the beginning of how I was thinking about the php

<?php
//mysql query for rows
?>
<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 
foreach ($records as $record) {
	echo '<td colspan=2><span class="month_tab">'.$record['select_month'].'</span></td>'."\n\t";
}
  ?>
  <!--END LOOP -->
</tr>
<!-- BEGIN OUTERLOOP FOREACH CATEGORY -->
<!-- loop foreach item record within the category-->
<?php
	foreach ($records as $record) {
	echo '
	<tr height="18px">
	  <td colspan='.$numspan.'><span class="ex_name">'.$record['expname'].'</span></td>
	</tr>'."\n\t";
	foreach ($items as $item) {
		//not sure about this one
	}
}
?>
<!-- end item foreach -->
<!-- END CATEGORY FOREACH -->
</table>

</div>

 

Any suggestions on how I might set this up?

 

 

 

[samoht]

no help so far - so maybe I will break down the tasks into smaller segments.

 

First question:

I have a query that will return data from the db something like this:

expname	amount	recordtime	budget_amount	select_year	select_month	select_category	itemList
Rent	800	2010-01-30 - 13:41:53	825	2010	01, 02, 03, 04, 05	2	12
Fuel	35	2010-01-26 - 11:02:20	250	2010	01, 02, 03, 04, 05	1	8

 

I have a form that allows the user to pick between a date range through two date/time pickers. Those will post a date like 31/01/2010. So I need to return the records for the date range and populate a table with the correct month columns even if no data is in one of the months.

 

For example: if the user picks a date range of 01/08/2010 start date and 01/11/2010 end date, I want to build a table with months 08, 09, 10 and 11 showing in the column headers and all the rows for the budget items in place even though they may be empty. Any ideas??

 

 

[samoht]

OK  - I got a little further and now have the table as I want it, but I am unable to populate it with db data?

here is what I have:

<?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> </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>'."\n\t";
    } 
  ?>
  <!--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>'."\n\t";            
            
            $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> </span></td>
            <td rowspan=2><span align=center style="text-align:center"> </span></td>'."\n\t";
        }
        echo '
        </tr>
        <tr height="11px">
        ';
        //amount budgeted
        for($j=$start;$j<$nmonths;$j++){
            echo '
            <td><span></span></td>'."\n\t";
        }
        echo '
        </tr>';
    }
?> 

</table>

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

 

Any Ideas how to add the info from jos_chronoforms_bdgtbymonth and jos_chronoforms_bdgtinput  - so that I can populate those empty table cells??