create report with a dynamic column.

I am at loss on how to do this kind of report. Please help me..

can you help me create a report that will look like below?
Dates are dynamic, it can display date ranges from jan. to december or it can display only january or february.

PHP Code:

---

Project Name        Amount Allocated                        No. of Jobs
                  Total    Jan.       Feb.      Mar.          Total       Jan.    Feb.      Mar.
Project 1       900.00                                         2,250
Location 1       500.00    100.00    200.00      200.00        1,000       500     250      250
Location 2       250.00    200.00    25.00       25.00         750         100     400      200
Location 3       150.00    50.00     50.00       50.00         500         50       500     400 


---

my tables look like below

for the amount allocated

tblamountalloc

PHP Code:

---

Project Name    Location 1       Location 2    Location 3          Date
Project 1            100.00        200.00        50.00        01-14-2012
Project 1            200.00         25.00        50.00        02-14-2012
Project 1            200.00         25.00        50.00        03-14-2012 


---

for the no. of jobs

tblnojobs

PHP Code:

---

Project Name    Location 1    Location 2    Location 3    Date
Project 1           500        100         50        01-14-2012
Project 1           250        400        200        02-14-2012
Project 1           250        200        400        03-14-2012 


---

Please anyone?

i Have this code for a start.

Code:

---

$query="Select tblamountalloc.agencycode,tblamountalloc.nproject,tblamountalloc.projectname,
tblamountalloc.Location1,tblamountalloc.Location2,tblamountalloc.Location3,tblnojobs.Location1,
tblnojobs.Location2,tblnojobs.Location3 from tblamountalloc Inner join tblnojobs on
tblamountalloc.projectname=tblnojobs.projectname
where tblheader.starperiod between '2012-06-01' and '2012-08-01'";

---

Code:

---

while ($record = mysql_fetch_object($query)) {
  $set[$record->agencycode][$record->nproject[0]][$record->projectname][] = $record;
}
ksort($set);

print "<table>\n";
foreach ($set as $agency => $agencycodes) {
 print "<h2>{$agency}</h2>\n";
foreach ($agencycodes as $agencycode => $procodes) {
  foreach ($procodes as $procode => $qrtcodes) {
  print "<tr>";
  print "<th>{$procode}</th><th>&nbsp;</th>";

  foreach ($qrtcodes as $records) {
         
          $mono=$records->qrtcode;
        $sno= remove_non_numeric($mono); 
        $monumber=substr($sno,0,-4);
     
        $ncr[$monumber]=$records->ncra;               
       
  }

 unset($qrtcodes);
         
print "<th>".array_sum($ncr)."</th>";       
  foreach ($ncr as $ncrrecords) {         
        print "<th>{$ncrrecords}</th>";
  }
  unset($ncr);
   
 
  print "<tr>";
  }
}
}
print "</table>\n";

---

the code above have the result of like this.

Code:

---

Project III (ARISP III)                900        100.00        300.00        500.00
Support Project III (ARISP III)        200        200.00
Project II (ARCP II) (Infra)        600        600.00

---

my problem is my display should be like this

Code:

---

 
                                    Total  June        July          August
Project III (ARISP III)                  900            100.00          300.00        500.00
Support Project III (ARISP III)  200            200.00        0.00        0.00
Project II (ARCP II) (Infra)          600          0.00          0.00        600.00

---

I want to insert 0.00 in an array where the project doesnt have a record within a date of a date range.

So, you want to add a column, depending on how many columns the database returns? Basically your code write as many columns as the database returns.

This code may point you to the right track:

Code:

---

  $userQueryResult = mysql_query("YOUR QUERY HERE", $dbConn);
  $numberOfFields = mysql_num_fields($userQueryResult);
  for ($fieldCouter=0; $fieldCouter<$numberOfFields ; $fieldCouter++ )
  {
    $fieldName = mysql_field_name($userQueryResult, $fieldCouter);
    printf('<td>%s</td>', $fieldName);
  //You could also use:
  $fieldNames[] = mysql_field_name($userQueryResult, $fieldCouter);
  }

---

....

Quote:

---

Originally Posted by Slyke

So, you want to add a column, depending on how many columns the database returns? Basically your code write as many columns as the database returns.

This code may point you to the right track:

Code:

---

  $userQueryResult = mysql_query("YOUR QUERY HERE", $dbConn);
  $numberOfFields = mysql_num_fields($userQueryResult);
  for ($fieldCouter=0; $fieldCouter<$numberOfFields ; $fieldCouter++ )
  {
    $fieldName = mysql_field_name($userQueryResult, $fieldCouter);
    printf('<td>%s</td>', $fieldName);
  //You could also use:
  $fieldNames[] = mysql_field_name($userQueryResult, $fieldCouter);
  }

---

---

That is not what i meant, I want to add a column depending on the returned date.

in my sql queries returned date are displayed by rows. i want it display by column.