The Catalogue List & Detail Pages

Personal Catalogue: PHP & MySQL

Chapter 7 · The Catalogue List & Detail Pages

Chapters 3-6 built every write and search operation the catalogue needs, but there's still no ordinary browsing page — the one a real visit to the site opens on. This chapter builds index.php, a full list of every item with its type and tags visible at a glance, and view_item.php, a single-item detail page.

The Naive Approach, and Why It's a Real Problem

The obvious first attempt at listing every item's own tags alongside it is to fetch all items, then loop over them and run get_tags_for_item() (from Chapter 5) once per row:

// Works, but don't actually do this:
$items = $pdo->query('SELECT * FROM items ORDER BY title')->fetchAll();

foreach ($items as &$item) {
    $item['tags'] = get_tags_for_item($pdo, $item['id']);
}

This Is the Classic N+1 Query Problem

Listing 40 items this way runs 1 query to fetch the items, plus 40 more queries — one per item — to fetch each one's own tags. That's 41 real round trips to MySQL for a page that should need one or two. It happens to work correctly at a small personal-catalogue scale, but it's exactly the pattern that turns slow the moment a collection grows past a trivial size, and it's worth building the habit of avoiding it from the start rather than only fixing it once it's actually slow.

The Fix: One Query, GROUP_CONCAT, and a LEFT JOIN

MySQL's GROUP_CONCAT() aggregate function collapses several rows' worth of a column into one comma-separated string per group — exactly what's needed to fetch every item's own tags in the same query as the item itself, using GROUP BY to group the joined tag rows back down to one row per item:

$sql = "
    SELECT
        items.*,
        item_types.name AS type_name,
        GROUP_CONCAT(tags.name ORDER BY tags.name SEPARATOR ', ') AS tag_names
    FROM items
    JOIN item_types ON item_types.id = items.item_type_id
    LEFT JOIN item_tags ON item_tags.item_id = items.id
    LEFT JOIN tags ON tags.id = item_tags.tag_id
    GROUP BY items.id
    ORDER BY items.title
";

$items = $pdo->query($sql)->fetchAll();
// $items[0]['tag_names'] is now either "Programming, Python", or NULL
// for an item with no tags at all — one query, no loop needed.

Why LEFT JOIN, Not JOIN, for item_tags and tags

A plain JOIN (an inner join) only keeps rows that have a match on both sides — an item with zero tags (every CD and DVD, and any book that hasn't been tagged yet) has zero matching rows in item_tags, so a plain JOIN would silently drop it from the results entirely. LEFT JOIN keeps every row from items regardless of whether a match exists on the right-hand side, filling in NULL for the tag columns when there's no match — exactly the same NULL-for-"no value" pattern Chapter 2's own schema already relies on. item_types still uses a plain JOIN here, deliberately, since every item is guaranteed (by the NOT NULL foreign key from Chapter 2) to have exactly one real type.

Rendering the List

With $items already carrying type_name and tag_names directly, the list page itself is a simple loop — no further queries needed per row:

index.php

<table>
    <tr>
        <th>Title</th><th>Type</th><th>Creator</th><th>Tags</th><th></th>
    </tr>
    <?php foreach ($items as $item): ?>
        <tr>
            <td><a href="view_item.php?id=<?= $item['id'] ?>"><?= htmlspecialchars($item['title']) ?></a></td>
            <td><?= htmlspecialchars($item['type_name']) ?></td>
            <td><?= htmlspecialchars($item['creator'] ?? '—') ?></td>
            <td><?= htmlspecialchars($item['tag_names'] ?? '—') ?></td>
            <td>
                <a href="edit_item.php?id=<?= $item['id'] ?>">Edit</a>
                <form method="post" action="delete_item.php" style="display: inline;">
                    <input type="hidden" name="id" value="<?= $item['id'] ?>">
                    <button type="submit">Delete</button>
                </form>
            </td>
        </tr>
    <?php endforeach; ?>
</table>

The delete form matches Chapter 3's own POST-only requirement exactly, wrapped in an inline-styled <form> so it sits naturally inside a table cell next to the edit link.

A "Recently Added" Section, Using Chapter 2's Own created_at Column

Chapter 2 called out created_at's own automatic default as "free" — something to use later. A small recently-added widget on the same page is exactly that payoff:

$recent = $pdo->query('
    SELECT title, created_at FROM items
    ORDER BY created_at DESC
    LIMIT 5
')->fetchAll();

No WHERE clause, no parameters — created_at was populated automatically by MySQL's own DEFAULT CURRENT_TIMESTAMP for every row added since Chapter 3, so ordering by it and limiting to 5 costs nothing extra in application code.

The Detail Page

view_item.php reuses the same GROUP_CONCAT query shape, just narrowed to one row with a WHERE items.id = :id clause:

view_item.php

<?php
require_once __DIR__ . '/config.php';

$id = (int) ($_GET['id'] ?? 0);

$stmt = $pdo->prepare("
    SELECT
        items.*,
        item_types.name AS type_name,
        GROUP_CONCAT(tags.name ORDER BY tags.name SEPARATOR ', ') AS tag_names
    FROM items
    JOIN item_types ON item_types.id = items.item_type_id
    LEFT JOIN item_tags ON item_tags.item_id = items.id
    LEFT JOIN tags ON tags.id = item_tags.tag_id
    WHERE items.id = :id
    GROUP BY items.id
");
$stmt->execute(['id' => $id]);
$item = $stmt->fetch();

if (!$item) {
    http_response_code(404);
    exit('Item not found.');
}
?>
<h1><?= htmlspecialchars($item['title']) ?></h1>
<p>Type: <?= htmlspecialchars($item['type_name']) ?></p>
<p>Creator: <?= htmlspecialchars($item['creator'] ?? 'Not recorded') ?></p>
<p>Format detail: <?= htmlspecialchars($item['format_detail'] ?? 'Not recorded') ?></p>
<p>Release year: <?= htmlspecialchars((string) ($item['release_year'] ?? 'Not recorded')) ?></p>
<p>Tags: <?= htmlspecialchars($item['tag_names'] ?? 'None') ?></p>
<?php if ($item['notes']): ?>
    <p>Notes: <?= nl2br(htmlspecialchars($item['notes'])) ?></p>
<?php endif; ?>

<a href="edit_item.php?id=<?= $item['id'] ?>">Edit</a>
<a href="index.php">Back to Catalogue</a>

Why GROUP BY items.id Alone Is Enough

Standard SQL normally requires every non-aggregated column in the SELECT list to also appear in GROUP BY. MySQL relaxes this specifically when the GROUP BY column is a table's own primary key (items.id here) — since a primary key uniquely determines every other column in that same row, grouping by it alone is functionally identical to grouping by every column, and MySQL is smart enough to allow the shorter form. This is real, documented MySQL-specific behavior (called "functional dependency") — it isn't guaranteed to work the same way on every SQL database, worth remembering if this project's own database is ever swapped later, echoing the same portability caveat Chapter 6 already raised for reused named placeholders.

Hands-On Exercises

Exercise 1

Build index.php using the GROUP_CONCAT query from this chapter against your real database (with at least one tagged book, one untagged book, and one CD or DVD already in it from earlier chapters). Confirm the untagged items show a sensible fallback (e.g. "—") in the Tags column rather than a blank cell or a PHP warning.

📄 View solution

Exercise 2

Temporarily change the list query's own LEFT JOIN item_tags to a plain JOIN item_tags, reload index.php, and describe exactly what changes in the result set. Then change it back and confirm the original behavior returns.

📄 View solution

Exercise 3

Build view_item.php, then visit it with a real item's own id, and separately with an id you know doesn't exist (e.g. 999999). Confirm the second case shows "Item not found." rather than a blank page or a PHP error, and explain why $stmt->fetch() returning false is what makes the if (!$item) check work.

📄 View solution

Chapter 7 Quick Reference

N+1 query problem — fetching items, then looping to fetch each one's own tags separately, means 1+N real queries for N items
GROUP_CONCAT() — collapses several joined tag rows into one comma-separated string per item, fixing N+1 in a single query
LEFT JOIN item_tags/tags — keeps items with zero tags in the results; a plain JOIN would silently drop them
GROUP BY items.id alone — valid in MySQL specifically because id is the primary key (functional dependency)
Recently added widget — the real payoff of Chapter 2's own "free" created_at DEFAULT CURRENT_TIMESTAMP column
Next chapter: Fast Manual Entry — reducing friction given the project's own real deadline