Filter by Project

In this lesson, we’ll focus on filtering data by project. This is the workflow that we’ll follow to filter the data project-wise:

1. Create a basic form in views/reports.php.
2. Edit the controller and the model for the “Filter” feature.
3. Run the application.

Basic implementation

Let’s create a form in our view with a list of all the projects:

<p>Filter by</p>
<form method="get">
     <select name="filter" id="project">
           <option value="">Select one</option>               
            <?php foreach ($projects as $project) : ?>
            <option value="<?php echo 'project:' . $project["id"] ?>"><?php echo escape($project["title"]) ?>
             </option>

             <?php endforeach; ?>             
</select>   
<input type="submit" value="Run">
      
</form>

We’ll create the projects variable that we use in the $projects = get_all_projects(); view inside the conroller. We will also create the $filter = 'all' variable, which we’ll use as a parameter for the get_all_tasks() function.

Now when the user submits the form, the data will be stored in $_GET['filter']. Let’s add this in the reports.php controller:

if (!empty($_GET['filter'])) {
    $filter = explode(":", $_GET['filter']); // convert input into an array
}

$tasks = get_all_tasks($filter);

To finish, we’ll edit the get_all_tasks() function in our model.

First, we’ll accept an optional parameter, get_all_tasks($filter = null). Next, we’ll simplify the $sql variable like so:

$sql =  'SELECT t.*, p.title project 
       FROM tasks t
       INNER JOIN projects p 
       ON t.project_id = p.id';

We can now add two new variables with their default values

$where = '';
$orderBy = ' ORDER BY t.date_task DESC';

and prepare the query like so: $tasks = $connection->prepare($sql . $where . $orderBy);.

If we call the get_all_tasks() function, we’ll get all the tasks that were ordered by t.date_task DESC. This is what we want in our task_list.php file.

If $filter is set and is not null, we’ll know that the call is from reports.php. Let’s check if the parameter is an array:

 if (is_array($filter)) {
       if ($filter[0] == 'project') {
            $where = ' WHERE p.id = ?';
        } 
}

If $filter is an array, we’ll check whether the first element is project. From there, we’ll know that the user wants to filter the data by project. Hence, before we execute the query, we’ll need to bind the value for the placeholder in $where:

 if (is_array($filter)) {
      $tasks->bindValue(1, $filter[1], PDO::PARAM_INT);            
}

Running the application

We were able to implement everything that we needed for our feature to work. Now it’s time for us to make sure that it works as expected. In the code widget given below, click the “Run” button and enjoy the application view.