create report with a dynamic column.

[VBcielle]

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.

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

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

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 

[VBcielle]

Please anyone?

[VBcielle]

i Have this code for a start.

$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'";

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.

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

  
                                    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.

[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:

  $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);
  }

[Slyke]

....

[VBcielle]

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:

  $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.

[VBcielle]

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