-
Aug 8th, 2012, 09:10 PM
#1
Thread Starter
Hyperactive Member
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
Last edited by VBcielle; Aug 8th, 2012 at 09:30 PM.
I want to learn more
grace
-
Aug 13th, 2012, 02:27 AM
#2
Thread Starter
Hyperactive Member
Re: create report with a dynamic column.
I want to learn more
grace
-
Aug 15th, 2012, 03:12 AM
#3
Thread Starter
Hyperactive Member
Re: create report with a dynamic column.
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> </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.
I want to learn more
grace
-
Aug 16th, 2012, 11:21 AM
#4
Fanatic Member
Re: create report with a dynamic column.
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);
}
-
Aug 16th, 2012, 11:21 AM
#5
Fanatic Member
Re: create report with a dynamic column.
Last edited by Slyke; Aug 16th, 2012 at 11:22 AM.
Reason: Double post, please delete.
-
Aug 23rd, 2012, 07:11 PM
#6
Thread Starter
Hyperactive Member
Re: create report with a dynamic column.
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.
I want to learn more
grace
-
Aug 23rd, 2012, 07:12 PM
#7
Thread Starter
Hyperactive Member
Re: create report with a dynamic column.
in my sql queries returned date are displayed by rows. i want it display by column.
I want to learn more
grace
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|