Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses 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.
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