SQL Help

Categories and sub-categories with PHP and MySQL: one query, no N+1

Updated August 2026 · First published 2006 by David J. Lake

The task: display nested data — artists, their albums, and each album's tracks — as grouped output, with each artist and album heading printed once. The music library is the classic teaching example, but it's the same problem as product categories, forum sections, or any category → sub-category → item display.

The trap: a query per category

The instinctive version queries the artists, then loops: one query for each artist's albums, then one for each album's tracks. That's the N+1 query problem — a library of 200 artists fires off hundreds of round-trips to render one page. It works in testing and crawls in production.

The database can hand you the whole tree, sorted for display, in one query. The only "trick" is regrouping flat rows back into headings as you loop.

The schema and the query

CREATE TABLE artists (
  artist_id   INT AUTO_INCREMENT PRIMARY KEY,
  artist_name VARCHAR(60) NOT NULL
);

CREATE TABLE albums (
  album_id   INT AUTO_INCREMENT PRIMARY KEY,
  artist_id  INT NOT NULL,
  album_name VARCHAR(80) NOT NULL
);

CREATE TABLE tracks (
  track_id   INT AUTO_INCREMENT PRIMARY KEY,
  album_id   INT NOT NULL,
  track_name VARCHAR(100) NOT NULL
);

SELECT a.artist_name, al.album_name, t.track_name
FROM artists a
LEFT JOIN albums al ON al.artist_id = a.artist_id
LEFT JOIN tracks t  ON t.album_id   = al.album_id
ORDER BY a.artist_name, al.album_name, t.track_name;

Two deliberate choices: LEFT JOINs so an artist with no albums yet still appears (switch to INNER JOIN to hide them), and the ORDER BY matching the display nesting — the regrouping loop below depends on rows arriving grouped.

The PHP: track what changed

Walk the flat rows remembering the previous artist and album; print a heading only when it changes:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=music;charset=utf8mb4',
               'user', 'pass',
               [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

$rows = $pdo->query("
    SELECT a.artist_name, al.album_name, t.track_name
    FROM artists a
    LEFT JOIN albums al ON al.artist_id = a.artist_id
    LEFT JOIN tracks t  ON t.album_id   = al.album_id
    ORDER BY a.artist_name, al.album_name, t.track_name
");

$currentArtist = null;
$currentAlbum  = null;

foreach ($rows as $row) {
    if ($row['artist_name'] !== $currentArtist) {
        $currentArtist = $row['artist_name'];
        $currentAlbum  = null;                    // new artist resets album tracking
        echo '<h2>' . htmlspecialchars($currentArtist) . '</h2>';
    }
    if ($row['album_name'] !== null && $row['album_name'] !== $currentAlbum) {
        $currentAlbum = $row['album_name'];
        echo '<h3>' . htmlspecialchars($currentAlbum) . '</h3>';
    }
    if ($row['track_name'] !== null) {
        echo '<p>' . htmlspecialchars($row['track_name']) . '</p>';
    }
}

Details that matter:

• Reset the inner tracker when the outer changes. Forgetting $currentAlbum = null on a new artist means two artists with an identically-named album ("Greatest Hits"…) merge wrongly.
• The NULL checks handle album-less artists and track-less albums that the LEFT JOINs kept in.
• htmlspecialchars() on everything you echo — band names absolutely will contain & and <.

Prefer building an array first? Same idea, different shape — nest as you read, render from the structure:

$tree = [];
foreach ($rows as $r) {
    $tree[$r['artist_name']][$r['album_name'] ?? ''][] = $r['track_name'];
}

Either way, it's one database round-trip, however big the library grows — and that's the lesson that outlives any particular language or framework.