Lesson Monday

Now that we've looked at how JOIN statements work in SQL queries, let's look at how we can use them in our PHP methods. Up to this point, when we've created a many-to-many relationship in our database we've used two or more database queries to access the information. Let's look at an example of that method now:

Category.php
function getTasks()
{
    $query = $GLOBALS['DB']->query("SELECT task_id FROM categories_tasks WHERE category_id = {$this->getId()};");
    $task_ids = $query->fetchAll(PDO::FETCH_ASSOC);

    $tasks = array();
    foreach($task_ids as $id) {
        $task_id = $id['task_id'];
        $result = $GLOBALS['DB']->query("SELECT * FROM tasks WHERE id = {$task_id};");
        $returned_task = $result->fetchAll(PDO::FETCH_ASSOC);

        $description = $returned_task[0]['description'];
        $id = $returned_task[0]['id'];
        $new_task = new Task($description, $id);
        array_push($tasks, $new_task);
    }
    return $tasks;
}

In this case we're processing a minimum of two SQL queries. The first query will return a series of task_id integers, which we receive as a PDO object. Because this object is not an array, we need to use the fetchAll() method to collect the keys to match up with the task_id values. This creates an associative array that we can pass into our foreach loop.

Once within the foreach loop, we query the database again, this time using the collected task_id to return individual rows from the tasks table. These rows also need to be converted into associative arrays, so just like before we collect the keys from the table, and only then are we able to move on to building objects and constructing our output array.

The problem we run into with this method is that we're not limiting the number of SQL queries we're making. With each new id returned by the first query, we run another instance of the foreach loop, and make another SQL query. If we have 6 ids, we run a total of 7 SQL queries. If we have 10,000 ids, we run our SQL query 10,001 times. As you can see, this isn't very efficient. In fact, we want to avoid running our SQL queries within a foreach loop at all.

That's where the JOIN statement comes to our rescue. Let's look at the same getTasks() method written with a JOIN statement.

Category.php
        function getTasks()
        {
            $returned_tasks = $GLOBALS['DB']->query("SELECT tasks.* FROM categories
                JOIN categories_tasks ON (categories_tasks.category_id = categories.id)
                JOIN tasks ON (tasks.id = categories_tasks.task_id)
                WHERE categories.id = {$this->getId()};");
            $tasks = array();
            foreach($returned_tasks as $task) {
                $description = $task['description'];
                $id = $task['id'];
                $new_task = new Task($description, $id);
                array_push($tasks, $new_task);
            }
            return $tasks;
        }

Notice anything different?

We're only running a single SQL query here, and it's outside of our foreach loop. In this example, the variable $returned_tasks represents a PDO object that we can use just like the PDO object in our getAll() method. We don't need to run a fetchAll() method on it, and can pass it directly into our simple foreach loop.

So what's going on inside this JOIN statement? It's happening in a few simple steps:

  • We set our destination: tasks.*. This means we want a complete tasks table.
  • We set our starting point: categories. *We collect an id from the categories table (chosen at the end of the statement, after WHERE), and join it up with any matching rows in the categories_tasks table.
  • We use the task_id from the matching rows in the categories_tasks table to select rows from the tasks table.
  • Finally, our statement returns a complete tasks table, as a PDO, composed of only those rows which match our query.

This single query takes the place of a potentially huge number of other queries, and returns information in an extremely usable PDO format.