Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Outputting timesheet totals

Here is the table:

tracking_id, project_id, project_time, createdat

1,1,2.5,2014-03-10
2,1,3.5,2014-03-11
3,1,4.5,2014-03-12
4,1,5.5,2014-03-13
5,1,2.5,2014-03-14
6,2,3.5,2014-03-10
7,2,3.5,2014-03-11
8,2,3.5,2014-03-12
9,2,4.5,2014-03-13
10,2,4.5,2014-03-14


Title Line of Report

Totals for 8/10 - 8/14

Project ID | Mon | Tue | Wed | Thu | Fri


Where the day columns are the total from the database for that day for the project

1.  Some days might be missing.
2.  the date range is dynamic.

Here is what I have so far:

http://sqlfiddle.com/#!2/69e8f0/3


The query returns the results but not in a way I can easily generate an html table.


What I need to know: is their an easy way to rewrite the query so that there is 1 row per project_id and all the totals returned...

or how would I go about writing the PHP to output 1 row per project_id and the totals for the day columns.

Attached is a sample report, but it does not correlate with the data in the fiddle.
sample-report.PNG
0
rjohnsonjr
Asked:
rjohnsonjr
  • 5
  • 4
1 Solution
 
gplanaCommented:
Not sure if I understand your question, but I think this code is what you need (supposing you already have the connection made to mysql):

<table>
<?php
$strSQL = 'SELECT
	project_id,
	FROM_DAYS(TO_DAYS(createdat)) AS createdat,
	sum(project_time) AS project_time
FROM
	tracking
WHERE
	createdat >= \'2014-03-08\'
AND createdat <= \'2014-03-15\'
GROUP BY
	project_id,
	FROM_DAYS(TO_DAYS(createdat))';

$query = mysql_query($strSQL);
for ($i=0; $i<mysql_num_rows($query): $i++){
   $tracking_id = mysql_result($query,$i,0);
   $project_id = mysql_result($query,$i,1);
   $project_time = mysql_result($query,$i,2);
   $createdat = mysql_result($query,$i,3);
?>
<tr>
<td><?php echo $tracking_id; ?></td>
<td><?php echo $project_id; ?></td>
<td><?php echo $project_time; ?></td>
<td><?php echo $createdat; ?></td>
</tr>
<?php
   } // end of for
?>
</table>

Open in new window


Hope it helps. Regards.
0
 
rjohnsonjrAuthor Commented:
Sorry for not better explaining myself.

The issue I had and your code has is if there are 5 entries in the table for a project id, there will be 5 rows  in the output report and that is not what I want.

This might make it easier:


I want 1 row per project_id in the output report.

I want the totals for the particular date.

For project one I want the total hours for 3/10/2014 to be in the Monday column, the total hours for 3/11/2014 to be in the Tuesday column etc.
0
 
rjohnsonjrAuthor Commented:
So I found the solution to my problem I believe.  I needed to create a 2 dimensional array  where I create an entry in the array for each product id  and put set the columns to 0.00

Then I loop over the database and use the day of week to determine which column the total should go into.

This code needs cleaned up but should give you an idea.  it is a mix of php syntax and blade syntax at the moment.

<?php

$matrix = array();

?>

@foreach ($results as $result)

	@for ($i = 0; $i <= $total_days; $i++)
		<?php $matrix[$result->project_number][$i]=0.00; ?>
	@endfor

@endforeach

@foreach ($results as $result)

<?php

		$i++; 
		$line_total = 0.00;
		$grand_total = 0.00;
		$column_total = array();
		$day_of_week = date("w",strtotime($result->createdat));
		
		$matrix[$result->project_number][$day_of_week] = $result->project_time;
?>

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
gplanaCommented:
Oh, sorry, I didn't understood.

I think you should just remove last line on the group by so only the project id is on your group by clause.

So change this:
$strSQL = 'SELECT
	project_id,
	FROM_DAYS(TO_DAYS(createdat)) AS createdat,
	sum(project_time) AS project_time
FROM
	tracking
WHERE
	createdat >= \'2014-03-08\'
AND createdat <= \'2014-03-15\'
GROUP BY
	project_id,
	FROM_DAYS(TO_DAYS(createdat))';

Open in new window


to this:

$strSQL = 'SELECT
	project_id,
	FROM_DAYS(TO_DAYS(AVG(createdat))) AS createdat,
	sum(project_time) AS project_time
FROM
	tracking
WHERE
	createdat >= \'2014-03-08\'
AND createdat <= \'2014-03-15\'
GROUP BY
	project_id;

Open in new window


and let me know if this works.
0
 
rjohnsonjrAuthor Commented:
if you do that then only 1 date is returned instead of all dates
0
 
gplanaCommented:
Yes, but isn't this what you want? Don't you sant just one record by project?
0
 
rjohnsonjrAuthor Commented:
I want 1 project_id row but the totals for each date
0
 
gplanaCommented:
Not sure if I understand. If you have these rows:

Project   date
1              Today
1              Tomorrow
2              Today
2              Tomorrow

Which rows do you want to be on the resultset?
0
 
rjohnsonjrAuthor Commented:
The 2 dimensional array was the key as explained in my post.  Thanks to those that tried to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now