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