Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Outputting timesheet totals

Posted on 2014-03-14
9
Medium Priority
?
222 Views
Last Modified: 2014-03-25
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
Comment
Question by:rjohnsonjr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 39929560
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
 
LVL 6

Author Comment

by:rjohnsonjr
ID: 39929699
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
 
LVL 6

Accepted Solution

by:
rjohnsonjr earned 0 total points
ID: 39929813
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 15

Expert Comment

by:gplana
ID: 39929826
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
 
LVL 6

Author Comment

by:rjohnsonjr
ID: 39929934
if you do that then only 1 date is returned instead of all dates
0
 
LVL 15

Expert Comment

by:gplana
ID: 39929940
Yes, but isn't this what you want? Don't you sant just one record by project?
0
 
LVL 6

Author Comment

by:rjohnsonjr
ID: 39929960
I want 1 project_id row but the totals for each date
0
 
LVL 15

Expert Comment

by:gplana
ID: 39929968
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
 
LVL 6

Author Closing Comment

by:rjohnsonjr
ID: 39952706
The 2 dimensional array was the key as explained in my post.  Thanks to those that tried to help
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question