Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP CRUD Operations with PHP Reading and Writing Reports Summarizing Project Time

Jonathan Grieve
MOD
Jonathan Grieve
Treehouse Moderator 91,253 Points

[SOLVED] Report still returning project totals for each task

So here's my code for generating the report. I've had another go since the weekend but the report is still showing incorrect despite going over the code many times and trying different things.

Report

Is there a reason for this you can see in the code. Or something to do with the data I've already put into the database?

My only working theory is that I'm somehow ordering results by date but not project, however I can't see anything wrong with the get_task_list() method. Thanks.

reports.php
<?php
require 'inc/functions.php';

$page = "reports";
$pageTitle = "Reports | Time Tracker";
$filter = 'all';

include 'inc/header.php';
?>
<div class="col-container page-container">
    <div class="col col-70-md col-60-lg col-center">
        <div class="col-container">
            <h1 class='actions-header'>Reports</h1>
        </div>
        <div class="section page">
            <div class="wrapper">
                <table>

                <?php 

                    $total = $project_id = $project_total = 0;
                    $tasks = get_task_list($filter);

                    foreach($tasks as $item) {

                        if ($project_id != $item['project_id']) {           
                            $project_id = $item['project_id'];
                            echo "<thead>\n";
                            echo "<tr>\n";
                            echo "<th>" . $item['project']  . "</th>\n";
                            echo "<th>Date</th>\n";
                            echo "<th>Time</th>\n";
                            echo "</tr>\n";
                            echo "</thead>\n";


                    }                       

                    $project_total += $item['time'];
                    $total += $item['time'];



                    echo "<tr>\n";
                    echo "<td>" . $item['title'] . "</td>\n";
                    echo "<td>" . $item['date'] . "</td>\n";
                    echo "<td>" . $item['time'] . "</td>\n";
                    echo "</tr>\n";

                    if(next($tasks)['project_id'] != $item['project_id']) {
                            echo "<tr>\n";
                            echo "<th class='project-total-label' colspan='2'>Project Total</th>\n";
                            echo "<th class='project-total-number'>$project_total</th>\n";
                            echo "</tr>\n";
                            $project_total=0;

                        }

                    }

                    ?>
                    <tr>
                        <th class='grand-total-label' colspan='2'>Grand Total</th>
                        <th class='grand-total-number'><?php echo $total; ?></th>
                    </tr>
                </table>
            </div>
        </div>
    </div>
</div>

<?php include "inc/footer.php"; ?>
functions.php
<?php
function get_task_list($filter = null) {

    //include database connection in function
    include 'connection.php';

    //run query on database and return result set   
    $sql = 'SELECT tasks.*, projects.title as project FROM tasks'
        . ' JOIN projects ON tasks.project_id = projects.project_id';

    $order_by = ' ORDER BY date DESC';
    if($filter) {
        $order_by = ' ORDER BY projects.title ASC, date DESC';

    }

    try {
        $results = $db->prepare($sql . $order_by);
        $results->execute();
    } catch (Exception $e) {
        echo "Error!: " . $e->getMessage() . "</br>";
        return array();

    }

    return $results->fetchAll(PDO::FETCH_ASSOC);

}
function get_task_list($filter = null) {

    //include database connection in function
    include 'connection.php';

    //run query on database and return result set   
    $sql = 'SELECT tasks.*, projects.title as project FROM tasks'
        . ' JOIN projects ON tasks.project_id = projects.project_id';

    $order_by = ' ORDER BY date DESC';
    if($filter) {
        $order_by = ' ORDER BY projects.title ASC, date DESC';

    }

    try {
        $results = $db->prepare($sql . $order_by);
        $results->execute();
    } catch (Exception $e) {
        echo "Error!: " . $e->getMessage() . "</br>";
        return array();

    }

    return $results->fetchAll(PDO::FETCH_ASSOC);

}
?>

2 Answers

Jonathan Grieve
MOD
Jonathan Grieve
Treehouse Moderator 91,253 Points

Okay so we have an answer to this problem. Thanks for Jason Anello for helping out with this. and it was simply a case of working with different versions of PHP. I like to follow courses on my own system and in this case it was XAMPP localhost which was installed with php5. Workspaces uses php7.

If you're using php7 you can produce the same report output as the video... (i.e. totals per project) with the following code

<?php 

//place code inside foreach loop

        echo "<tr>\n";
        echo "<td>" . $item['title'] . "</td>\n";
        echo "<td>" . $item['date'] . "</td>\n";
        echo "<td>" . $item['time'] . "</td>\n";
        echo "</tr>\n";

        //use current to loop project totals
        if(current($tasks)['project_id'] != $item['project_id']) {
                echo "<tr>\n";
                echo "<th class='project-total-label' colspan='2'>Project Total</th>\n";
                echo "<th class='project-total-number'>$project_total</th>";
                        echo "</tr>\n";
                $project_total=0;

                }
                    next($tasks);  //advance array pointer
            }

?>

Use the current instead of next method in the condition. It's all to do with a difference in how foreach works in php5 and php7. Use the code as directed in the video for php7. Hope this makes sense. :p

For some reason, the next() method still wasn't working for me. Adding a simple counter to the foreach works:

<?php

// Initialize counter with other variables
$total = $project_id = $project_total = $i = 0;
$tasks = get_task_list($filter);

foreach ($tasks as $item) {
  if ($project_id != $item['project_id']) {
    $project_id = $item['project_id'];
    echo "<thead>\n";
    echo "<tr>\n";
    echo "<th>" . $item['project'] . "</th>\n";
    echo "<th>Date</th>\n";
    echo "<th>Time</th>\n";
    echo "</tr>\n";
    echo "</thead>\n";
  }
  $project_total += $item['time'];
  $total += $item['time'];
  echo "<tr>\n";
  echo "<td>". $item['title'] . "</td>\n";
  echo "<td>". $item['date'] . "</td>\n";
  echo "<td>". $item['time'] . "</td>\n";
  echo "</tr>\n";

  // Retrieve next value
  if ($tasks[$i + 1]['project_id'] != $item['project_id']) {
    echo "<tr>\n";
    echo "<th class='project-total-label' colspan='2'>Project Total</th>\n";
    echo "<th class='project-total-number'>$project_total</th>\n";
    echo "</tr>\n";
    $project_total = 0;
  }

  // Advance counter
  $i++;
}

?>