General Report

Learn how to use the SQL “INNER JOIN” clause to query data from a number of tables.

We'll cover the following

We need to get all the information about the tasks with totals from the database. For this, we’ll create the reports.php file in both the controllers and the views folders.

Model

In our model.php file, we’ll need to modify the get_all_tasks() function. Don’t forget that the tasks and projects tables are in a relationship. Therefore, we also need a way to select corresponding projects along with the tasks. One way to do this is by using the SQL INNER JOIN clause.

We’ll replace the $sql = 'SELECT * FROM tasks ORDER BY date_task line with:

$sql = 'SELECT t.*, DATE_FORMAT(t.date_task, "%m/%d/%Y"), p.title project 
FROM tasks t
INNER JOIN projects p 
ON t.project_id = p.id 
ORDER BY p.title ASC, t.date_task DESC';

Controller

The controller will be similar to the task_list.php controller:

<?php
require_once "../model/model.php";

$tasks = get_all_tasks();

require "../views/reports.php";

View

As with other views, reports.php will inherit from layout.php. We can review the other views to incorporate the layout.php file in the reports view.

Let’s focus on the table we’ll create for our report:

Get hands-on with 1000+ tech skills courses.