Summarizing Project Time8: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.
Querying Relational Databases: ORDER BY
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