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 Naming Reports

R Brook
R Brook
17,937 Points

date filter is not actually filtering

Hi,

I've been following through this video and copying the code that Alena does into my workspace. However, at the end of the video I can't get the date filter to actually filter anything - whatever I pick from the dropdown all the results still stay visible. I can't find where I've made a mistake and I've gone over the video a couple of times. If anyone can give me some help I'd really appreciate it.

My functions.php:

<?php
//application functions

function get_project_list() {
  include 'connection.php';

  try {
    return $db->query('SELECT project_id, title, category FROM projects');
  } catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "</br>";
    return array();
  }
}

function get_task_list($filter = null) {
  include 'connection.php';

  $sql = 'SELECT tasks.*, projects.title as project FROM tasks'
  . ' JOIN projects ON tasks.project_id = projects.project_id';

  $where = '';
  if (is_array($filter)) {
    switch ($filter[0]) {
      case 'project':
        $where = ' WHERE projects.project_id = ?';
        break;
      case 'category':
        $where = ' WHERE category = ?';
        break;
      case 'date':
        $where = ' WHERE date >= ? AND date <= ?';
    }
  }

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

  try {
    $results = $db->prepare($sql . $where . $orderBy);
    if (is_array($filter)) {
      $results->bindValue(1, $filter[1]);
      if ($filter[0] == 'date') {
        $results->bindValue(2, $filter[2], PDO::PARAM_STR);
      }
    }
    $results->execute();
  } catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "</br>";
    return array();
  }
  return $results->fetchAll(PDO::FETCH_ASSOC);
}


function add_project($title, $category) {
  include 'connection.php';

  $sql = 'INSERT INTO projects(title, category) VALUES(?, ?)';

  try {
    $results = $db->prepare($sql);
    $results->bindValue(1, $title, PDO::PARAM_STR);
    $results->bindValue(2, $category, PDO::PARAM_STR);
    $results->execute();
  } catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "<br />";
    return false;
  }
  return true;
}

function add_task($project_id, $title, $date, $time) {
  include 'connection.php';

  $sql = 'INSERT INTO tasks(project_id, title, date, time) VALUES(?, ?, ?, ?)';

  try {
    $results = $db->prepare($sql);
    $results->bindValue(1, $project_id, PDO::PARAM_INT);
    $results->bindValue(2, $title, PDO::PARAM_STR);
    $results->bindValue(3, $date, PDO::PARAM_STR);
    $results->bindValue(4, $time, PDO::PARAM_INT);
    $results->execute();
  } catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "<br />";
    return false;
  }
  return true;
}

My reports.php:

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

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

if (!empty($_GET['filter'])) {
  $filter = explode(':', filter_input(INPUT_GET, 'filter', FILTER_SANITIZE_STRING));
}

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>
          <form class='form-container form-report' action='reports.php' method='get'>
            <label for='filter'>Filter:</label>
            <select id='filter' name='filter'>
              <option value=''>Select One</option>
              <optgroup label="Project">
              <?php
              foreach(get_project_list() as $item) {
                echo '<option value="project:' . $item['project_id'] . '">';
                echo $item['title'] . "</option>\n";
              }
              ?>
              </optgroup>
              <optgroup label="Category">
                <option value="category:Billable">Billable</option>
                <option value="category:Charity">Charity</option>
                <option value="category:Personal">Personal</option>
              </optgroup>
              <optgroup label="Date">
                <option value="date:<?php
                  echo date('m/d/Y',strtotime('-2 Sunday'));
                  echo ":";
                  echo date('m/d/Y',strtotime('-1 Saturday'));
                ?>">Last Week</option>
                <option value="date:
                <?php
                  echo date('m/d/Y',strtotime('-1 Sunday'));
                  echo ":";
                  echo date('m/d/Y');
                ?>">This Week</option>
                <option value="date:
                <?php
                  echo date('m/d/Y',strtotime('first day of last month'));
                  echo ":";
                  echo date('m/d/Y',strtotime('last day of last month'));
                ?>">Last Month</option>
                <option value="date:
                <?php
                  echo date('m/d/Y',strtotime('first day of this month'));
                  echo ":";
                  echo date('m/d/Y');
                ?>">This Month</option>
              </optgroup>
            </select>
            <input class="button" type="submit" value="Run" />
          </form>
        </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"; ?>
Benjamin Payne
Benjamin Payne
8,142 Points

Hey Rachel,

If you still need help with this, can you var_dump the $filter variable and make sure that the correct array / data is in there?

<?php

// ...

if (! empty($_GET['filter'])) {
    $filter = explode(':', filter_input(INPUT_GET, 'filter', FILTER_SANITIZE_STRING));
}

var_dump($filter);

exit;

Let me know what the output of that is. I'm wondering its a null / falsey value which might cause the DB queries to just return all the values instead of filtering.

Thanks,

Ben

Alexandru Palita
Alexandru Palita
14,261 Points

Hello!

You have unnecessary space in $filter[1] if you var_dump .....you get : 0 => string 'date' (length=4) 1 => string ' //space.//12/17/2016' (length=36) 2 => string '12/13/2016

To resolve it you have to do this:

<option value="date:<?php /// Put "<?php " on the same line with "date:".

6 Answers

R Brook
R Brook
17,937 Points

Hi, thanks for taking the time to help.

If I filter by: last week:

array(3) { [0]=> string(4) "date" [1]=> string(10) "12/18/2016" [2]=> string(10) "12/24/2016" }

this week:

array(3) { [0]=> string(4) "date" [1]=> string(28) " 12/25/2016" [2]=> string(10) "12/28/2016" }

last month:

array(3) { [0]=> string(4) "date" [1]=> string(28) " 11/01/2016" [2]=> string(10) "11/30/2016" }

this month:

array(3) { [0]=> string(4) "date" [1]=> string(28) " 12/01/2016" [2]=> string(10) "12/28/2016" }
Benjamin Payne
Benjamin Payne
8,142 Points

Hey Rachel,

Can you try adjusting the date format in the $filter var to be Y-m-d?

Let me know what that gets you.

Thanks,

Ben

R Brook
R Brook
17,937 Points

I've changed it and here are the results. I've noticed that 'last week' returns no results, while all the others return all results.

Last week:

array(3) { [0]=> string(4) "date" [1]=> string(10) "2016/12/18" [2]=> string(10) "2016/12/24" }

This week:

array(3) { [0]=> string(4) "date" [1]=> string(28) " 2016/12/25" [2]=> string(10) "2016/12/28" }

Last month:

array(3) { [0]=> string(4) "date" [1]=> string(28) " 2016/11/01" [2]=> string(10) "2016/11/30" }

This month:

array(3) { [0]=> string(4) "date" [1]=> string(28) " 2016/12/01" [2]=> string(10) "2016/12/28" }
Benjamin Payne
Benjamin Payne
8,142 Points

Looking more closely I noticed there is an extra space in front of the first date in the last three arrays but not in the first array. Can you remove that space and also replace the forward slashes with dashes.

R Brook
R Brook
17,937 Points

I've changed the slashes to dashes:

Last week:

array(3) { [0]=> string(4) "date" [1]=> string(10) "2016-12-18" [2]=> string(10) "2016-12-24" }

This week:

array(3) { [0]=> string(4) "date" [1]=> string(28) " 2016-12-25" [2]=> string(10) "2016-12-28" }

Last month:

array(3) { [0]=> string(4) "date" [1]=> string(28) " 2016-11-01" [2]=> string(10) "2016-11-30" }

This month:

array(3) { [0]=> string(4) "date" [1]=> string(28) " 2016-12-01" [2]=> string(10) "2016-12-28" }

I've had a look and haven't been able to find out where that extra whitespace has come from. It definitely shouldn't be there, so it might be that that's causing the problem. Here's the part of my reports .php that's responsible for the date part of the dropdown:

<optgroup label="Date">
                <option value="date:<?php
                  echo date('Y-m-d',strtotime('-2 Sunday'));
                  echo ":";
                  echo date('Y-m-d',strtotime('-1 Saturday'));
                ?>">Last Week</option>
                <option value="date:
                <?php
                  echo date('Y-m-d',strtotime('-1 Sunday'));
                  echo ":";
                  echo date('Y-m-d');
                ?>">This Week</option>
                <option value="date:
                <?php
                  echo date('Y-m-d',strtotime('first day of last month'));
                  echo ":";
                  echo date('Y-m-d',strtotime('last day of last month'));
                ?>">Last Month</option>
                <option value="date:
                <?php
                  echo date('Y-m-d',strtotime('first day of this month'));
                  echo ":";
                  echo date('Y-m-d');
                ?>">This Month</option>
              </optgroup>

Have you done this course before and had this part working? If you've got it in workspaces, would I be able to have your version of the code snippet that's above, so I can see if there are any differences?

Thanks for taking the time to help.

Benjamin Payne
Benjamin Payne
8,142 Points

Hey Rachel,

I haven't completed this one but I may have to go through it just to see what's happening here. For the white space try wrapping the $filter[1] and $filter[2] vars in a trim() function in your functions.php file. That should clean up any extra whitespace.

<?php
// ...


$results->bindValue(1, trim($filter[1]));

if (trim($filter[0]) == 'date') {
    $results->bindValue(2, trim($filter[2]), PDO::PARAM_STR);
}

Let me know what that gets you. If it's still not working i'll run through the steps quick so I can get the file built out.

Thanks,

Ben

R Brook
R Brook
17,937 Points

Hi,

I haven't been able to get it working with that. If you do work through the course, let me know if you need any of the code from earlier. I think that you can download the code from the video that I'm stuck on, but let me know if there's a problem. Thanks for the help :)

Justin Radcliffe
Justin Radcliffe
18,987 Points

I had a similar problem with this too. I resolved it by :

  1. Changing the date format from date(m/d/Y) to date(Y/m/d)
  2. Changing the date field format in Mysql database from 'text' to 'date'

I'm sure this will come up again in someone's course, so I went ahead and diagnosed the error...It's a syntax error that will get past PHP's run-time. I reviewed this old code at around 2:30am, so I couldn't pinpoint the exact part. I would recommend just copying and pasting this code into your project, then taking a look at the difference between what you wrote and what I have here. I checked it, it works as long as you have been following along with Alena. Hope this helps!

<optgroup label="Date">
              <option value="date:<?php
echo date('m/d/Y',strtotime('-2 Sunday'));
echo ":";
echo date('m/d/Y',strtotime('-1 Saturday'));
?>">Last Week </option>
                <option value="date:<?php
echo date('m/d/Y',strtotime('-1 Sunday'));
echo ":";
echo date('m/d/Y');
?>">This Week </option>
                <option value="date:<?php
echo date('m/d/Y',strtotime('first day of last month'));
echo ":";
echo date('m/d/Y',strtotime('last day of last month'));
?>">Last Month</option>
                <option value="date:<?php
echo date('m/d/Y',strtotime('first day of this month'));
echo ":";
echo date('m/d/Y');
?>">This Month</option>
              </optgroup>

I work with a split browser, so my indenting looks kind of funny :P.