Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Start a free Basic trial
to watch this video
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.
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.
-
0:00
Grouping tasks by project is one of the most important things that we can do.
-
0:05
In fact, I think that should be the default view for our reports.
-
0:09
We'll need to modify our get task list function
-
0:12
as well as add additional information to our reports page.
-
0:16
We're going to update the get task list function to accept an optional parameter.
-
0:22
We'll name this parameter filter since we'll be filtering this data
-
0:25
in several different ways.
-
0:27
The first thing we're going to do is sort.
-
0:30
So we need to add an order by to our SQL statement.
-
0:36
Let's set a default for the order to start with.
-
0:39
If our filter is not set, so on our task list page we want to only sort by date.
-
0:45
So the newest entry is first.
-
0:46
ORDER BY date DESCENDING.
-
0:53
If our filter parameter is not null, we want to change our ORDER BY.
-
1:11
ORDER BY projects.title ASC,
-
1:16
and then date DESCENDING.
-
1:22
Now we can add the ORDER BY to our query.
-
1:32
We're also going to start accepting user data, so
-
1:34
let's change this to a prepare statement instead.
-
1:52
And then we need to return the results.
-
1:58
Return $results > fetchAll,
-
2:03
and we use PDO FETCH ASSOC, for
-
2:08
associative array.
-
2:13
Next we need to update our report to pass the filter parameter.
-
2:18
Let's go back to the reports file and
-
2:19
add a filter variable at the top with the other variables.
-
2:23
filter = all.
-
2:27
Now use that filter variable to pass to the get_task_list function.
-
2:34
This should sort the tasks by project, but there isn't really any way for
-
2:38
us to see that since we're not showing the project.
-
2:42
Let's add headers for each project.
-
2:44
We need a new variable to track the project id.
-
2:47
Since we want this to start with zero as well,
-
2:50
let's add that to the line with the total.
-
2:52
project_id = 0.
-
2:57
Now at the top of the for each loop, we'll add a conditional
-
3:01
to check if the project_id is not equal to the current project.
-
3:07
If project_id != item ['project_id'].
-
3:19
If it's not equal, we want to set the project id and
-
3:21
display the project information header.
-
3:24
project id = item project_id.
-
3:35
And now we'll echo the headers.
-
3:54
Then we can add the project title.
-
4:09
Then add the column names to the next two columns.
-
4:15
Date and Time.
-
4:20
Let's see how this looks in the browser.
-
4:23
Oops, let's go in and fix that misspelling.
-
4:31
We have a project header now, but let's add a total for each project as well.
-
4:36
Back in work spaces, let's add another variable for project total.
-
4:45
At the top of the conditional that checks the project ID,
-
4:48
we add a new conditional to check if the project ID is greater than zero.
-
4:59
If so, we're going to display the project total.
-
5:10
I've added some nice styling here for you so
-
5:15
add class = project-total-label.
-
5:24
Colspan = 2.
-
5:28
Project Total.
-
5:33
And then for
-
5:36
this cell, class
-
5:41
= project-total-number
-
5:48
project_total.
-
5:56
We also need to reset the project total.
-
6:06
Finally, we need to add the current time to the project total, and we need to do
-
6:10
this regardless of the project id but after resetting the project total.
-
6:16
We reset our project total to zero if we're starting a new project.
-
6:20
But we need to add the task time each loop.
-
6:22
So after our conditional, let's duplicate this grand total line and
-
6:27
change this to project total.
-
6:30
Now let's check this in the browser again.
-
6:34
This time we see a header and a summary for all the projects.
-
6:38
Except the last one.
-
6:40
The last project does not have a project total.
-
6:43
Let's go back to our work spaces.
-
6:45
The project summary is only shown at the beginning of the loop,
-
6:49
when the project assigned to the current task does not match the project assigned
-
6:53
to the last task.
-
6:54
We also want to show the project summary for the last project.
-
6:58
We could copy and paste this display row after the for
-
7:01
each loop but duplicating code isn't usually the best idea.
-
7:05
Instead, we use another function with our loop.
-
7:09
First, we need to move the get_task_list out of the foreach loop.
-
7:21
Now we'll move this second conditional to the end of our loop.
-
7:32
And we'll change the conditional.
-
7:35
This time we'll use the next function.
-
7:46
If the next project id does not equal the current item,
-
7:51
project id, then we're going to show the project summary.
-
7:58
Now when we preview the script in the browser,
-
8:00
we see a summary of all the projects plus the grand total at the bottom.
You need to sign up for Treehouse in order to download course files.
Sign up