🤑 Join the Treehouse affiliate program and earn 25% commission!

🌟 Dreaming of a bright future? 🎓 Ask about the Treehouse Scholarship program! 🚀

A new Figma course has dropped!

🫡 Treehouse for Military offers discounts to Veterans, service members and their families!

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Summarizing Project Time

8:06 with Alena Holligan

Grouping tasks by project is one of the most important things we can do. In fact, I think this should be the default view for our reports. We'll show a summary of the total time spent on each project.

Teacher's Notes
Questions?
Video Transcript
Downloads
Workspaces

Course

Querying Relational Databases: ORDER BY

Documentation

We can't use the "next()" function with a PDOStatement object, instead we need an array. So it's important that you fetch results instead of return the "query()".

query() Executes an SQL statement, returning a result set as a PDOStatement object.

fetchAll() Returns an array containing all of the result set rows.

next() Advance the internal array pointer of an array.

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

Grouping tasks by project is one of the most important things that we can do. 0:00

In fact, I think that should be the default view for our reports. 0:05

We'll need to modify our get task list function 0:09

as well as add additional information to our reports page. 0:12

We're going to update the get task list function to accept an optional parameter. 0:16

We'll name this parameter filter since we'll be filtering this data 0:22

in several different ways. 0:25

The first thing we're going to do is sort. 0:27

So we need to add an order by to our SQL statement. 0:30

Let's set a default for the order to start with. 0:36

If our filter is not set, so on our task list page we want to only sort by date. 0:39

So the newest entry is first. 0:45

ORDER BY date DESCENDING. 0:46

If our filter parameter is not null, we want to change our ORDER BY. 0:53

ORDER BY projects.title ASC, 1:11

and then date DESCENDING. 1:16

Now we can add the ORDER BY to our query. 1:22

We're also going to start accepting user data, so 1:32

let's change this to a prepare statement instead. 1:34

And then we need to return the results. 1:52

Return $results > fetchAll, 1:58

and we use PDO FETCH ASSOC, for 2:03

associative array. 2:08

Next we need to update our report to pass the filter parameter. 2:13

Let's go back to the reports file and 2:18

add a filter variable at the top with the other variables. 2:19

filter = all. 2:23

Now use that filter variable to pass to the get_task_list function. 2:27

This should sort the tasks by project, but there isn't really any way for 2:34

us to see that since we're not showing the project. 2:38

Let's add headers for each project. 2:42

We need a new variable to track the project id. 2:44

Since we want this to start with zero as well, 2:47

let's add that to the line with the total. 2:50

project_id = 0. 2:52

Now at the top of the for each loop, we'll add a conditional 2:57

to check if the project_id is not equal to the current project. 3:01

If project_id != item ['project_id']. 3:07

If it's not equal, we want to set the project id and 3:19

display the project information header. 3:21

project id = item project_id. 3:24

And now we'll echo the headers. 3:35

Then we can add the project title. 3:54

Then add the column names to the next two columns. 4:09

Date and Time. 4:15

Let's see how this looks in the browser. 4:20

Oops, let's go in and fix that misspelling. 4:23

We have a project header now, but let's add a total for each project as well. 4:31

Back in work spaces, let's add another variable for project total. 4:36

At the top of the conditional that checks the project ID, 4:45

we add a new conditional to check if the project ID is greater than zero. 4:48

If so, we're going to display the project total. 4:59

I've added some nice styling here for you so 5:10

add class = project-total-label. 5:15

Colspan = 2. 5:24

Project Total. 5:28

And then for 5:33

this cell, class 5:36

= project-total-number 5:41

project_total. 5:48

We also need to reset the project total. 5:56

Finally, we need to add the current time to the project total, and we need to do 6:06

this regardless of the project id but after resetting the project total. 6:10

We reset our project total to zero if we're starting a new project. 6:16

But we need to add the task time each loop. 6:20

So after our conditional, let's duplicate this grand total line and 6:22

change this to project total. 6:27

Now let's check this in the browser again. 6:30

This time we see a header and a summary for all the projects. 6:34

Except the last one. 6:38

The last project does not have a project total. 6:40

Let's go back to our work spaces. 6:43

The project summary is only shown at the beginning of the loop, 6:45

when the project assigned to the current task does not match the project assigned 6:49

to the last task. 6:53

We also want to show the project summary for the last project. 6:54

We could copy and paste this display row after the for 6:58

each loop but duplicating code isn't usually the best idea. 7:01

Instead, we use another function with our loop. 7:05

First, we need to move the get_task_list out of the foreach loop. 7:09

Now we'll move this second conditional to the end of our loop. 7:21

And we'll change the conditional. 7:32

This time we'll use the next function. 7:35

If the next project id does not equal the current item, 7:46

project id, then we're going to show the project summary. 7:51

Now when we preview the script in the browser, 7:58

we see a summary of all the projects plus the grand total at the bottom. 8:00

You need to sign up for Treehouse in order to download course files.

Sign up

You need to sign up for Treehouse in order to set up Workspace

Sign up