Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

CRUD Operations with PHP

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.

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