Table relationships

In a real database the data lives in several tables: users in one, their orders in another. The tables are linked by identifiers, and a query assembles the picture you need from them.

The examples in this lesson use an in-memory SQLite database. The site's browser sandbox has no database support, so they have no "Run" button — run them on your own machine.

The foreign key

A relationship is stored in a column holding the identifier of a row in another table. For a post that is user_id — the author's identifier:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
$pdo->exec('CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT)');
$pdo->exec("INSERT INTO users (name) VALUES ('Anna'), ('Oleg')");
$pdo->exec("INSERT INTO posts (user_id, title) VALUES (1, 'First post'), (1, 'Second post'), (2, 'A note')");
 
$statement = $pdo->prepare('SELECT title FROM posts WHERE user_id = ?');
$statement->execute([1]);
 
echo implode(', ', $statement->fetchAll(PDO::FETCH_COLUMN)); // First post, Second post

The author's name is not repeated in the posts table: it is stored once, in the users table.

JOIN

JOIN connects the rows of two tables by a condition and returns them as one row:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
$pdo->exec('CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT)');
$pdo->exec("INSERT INTO users (name) VALUES ('Anna'), ('Oleg')");
$pdo->exec("INSERT INTO posts (user_id, title) VALUES (1, 'First post'), (2, 'A note')");
 
$rows = $pdo->query('
    SELECT posts.title, users.name
    FROM posts
    JOIN users ON users.id = posts.user_id
    ORDER BY posts.id
')->fetchAll(PDO::FETCH_ASSOC);
 
foreach ($rows as $row) {
    echo $row['title'], ' — ', $row['name'], "\n";
}

This prints "First post — Anna" and "A note — Oleg".

LEFT JOIN

A regular JOIN drops rows without a pair. LEFT JOIN keeps every row of the left table and fills the missing values with NULL:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
$pdo->exec('CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT)');
$pdo->exec("INSERT INTO users (name) VALUES ('Anna'), ('Oleg')");
$pdo->exec("INSERT INTO posts (user_id, title) VALUES (1, 'First post')");
 
$rows = $pdo->query('
    SELECT users.name, posts.title
    FROM users
    LEFT JOIN posts ON posts.user_id = users.id
    ORDER BY users.id
')->fetchAll(PDO::FETCH_ASSOC);
 
foreach ($rows as $row) {
    echo $row['name'], ': ', $row['title'] ?? 'no posts', "\n";
}

Oleg is in the result although he has no posts.

Counting with GROUP BY

GROUP BY gathers rows into groups, and COUNT() counts the rows in each:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
$pdo->exec('CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT)');
$pdo->exec("INSERT INTO users (name) VALUES ('Anna'), ('Oleg')");
$pdo->exec("INSERT INTO posts (user_id, title) VALUES (1, 'A'), (1, 'B'), (2, 'C')");
 
$rows = $pdo->query('
    SELECT users.name, COUNT(posts.id) AS total
    FROM users
    LEFT JOIN posts ON posts.user_id = users.id
    GROUP BY users.id
    ORDER BY users.id
')->fetchAll(PDO::FETCH_KEY_PAIR);
 
echo json_encode($rows); // {"Anna":2,"Oleg":1}

Many to many

A post has many tags, and a tag has many posts. Such a relationship is stored in a third table with two columns: post_id and tag_id. Each of its rows is one "post and tag" pair.

Common mistake

A query inside a loop is the most common cause of slow pages. A hundred posts mean a hundred and one queries:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
$pdo->exec('CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT)');
$pdo->exec("INSERT INTO users (name) VALUES ('Anna')");
$pdo->exec("INSERT INTO posts (user_id, title) VALUES (1, 'A'), (1, 'B')");
 
// Bad: a separate author query for every post inside a loop over the posts.
// Good: one query with JOIN returns the posts together with their authors.
$rows = $pdo->query('SELECT posts.title, users.name FROM posts JOIN users ON users.id = posts.user_id')->fetchAll();
 
echo count($rows); // 2

Thanks for reading!