Tags

Personal Catalogue: PHP & MySQL

Chapter 5 · Tags

Chapter 2 built tags and item_tags, the many-to-many pairing that lets a book carry several tags at once. This chapter finally wires that schema up to the real add/edit forms — creating tags on the fly, attaching and detaching them from an item, and running the actual "find every book tagged Python" query the whole feature exists for.

Two Real Operations, One Shared Helper Function

Both adding a new item and editing an existing one need to end up with the same result: a correct, current set of rows in item_tags for that item's own id. Rather than writing that logic twice, one shared sync_item_tags() function handles both cases — insert new pairings, and (for edit specifically) remove pairings that were unchecked:

functions.php (additions)

function get_all_tags(PDO $pdo): array {
    return $pdo->query('SELECT id, name FROM tags ORDER BY name')->fetchAll();
}

function get_tags_for_item(PDO $pdo, int $itemId): array {
    $stmt = $pdo->prepare('
        SELECT tags.id, tags.name
        FROM tags
        JOIN item_tags ON item_tags.tag_id = tags.id
        WHERE item_tags.item_id = :item_id
        ORDER BY tags.name
    ');
    $stmt->execute(['item_id' => $itemId]);
    return $stmt->fetchAll();
}

// Returns an existing tag's id, or creates the tag first and returns
// the new id — a real "find or create" pattern.
function find_or_create_tag(PDO $pdo, string $name): int {
    $stmt = $pdo->prepare('SELECT id FROM tags WHERE name = :name');
    $stmt->execute(['name' => $name]);
    $existing = $stmt->fetch();

    if ($existing) {
        return (int) $existing['id'];
    }

    $stmt = $pdo->prepare('INSERT INTO tags (name) VALUES (:name)');
    $stmt->execute(['name' => $name]);

    return (int) $pdo->lastInsertId();
}

// $tagIds is the full, correct set of tag ids this item should end
// up with. Anything currently attached but not in this set gets
// removed; anything in this set but not currently attached gets added.
function sync_item_tags(PDO $pdo, int $itemId, array $tagIds): void {
    $current = get_tags_for_item($pdo, $itemId);
    $currentIds = array_column($current, 'id');

    $toAdd = array_diff($tagIds, $currentIds);
    $toRemove = array_diff($currentIds, $tagIds);

    $insertStmt = $pdo->prepare('INSERT INTO item_tags (item_id, tag_id) VALUES (:item_id, :tag_id)');
    foreach ($toAdd as $tagId) {
        $insertStmt->execute(['item_id' => $itemId, 'tag_id' => $tagId]);
    }

    $deleteStmt = $pdo->prepare('DELETE FROM item_tags WHERE item_id = :item_id AND tag_id = :tag_id');
    foreach ($toRemove as $tagId) {
        $deleteStmt->execute(['item_id' => $itemId, 'tag_id' => $tagId]);
    }
}

Why Diff Instead of Delete-Everything-Then-Reinsert

A simpler-looking alternative would be: on every save, DELETE every existing item_tags row for this item, then INSERT the whole new set fresh. That works, but it's needless churn — for an item whose tags didn't actually change, it still runs a real delete and a real insert for every tag, every single save. The diff approach (array_diff in both directions) only touches the rows that genuinely changed, which matters more as a habit than as a performance concern at this scale — it's the same principle behind not rewriting a whole file when only one line changed.

Adding the Tag Checkboxes to the Form

The catalogue's own real spec only ever asks for tags on books — so the tag section is shown only when item_type_id corresponds to book, reusing the exact same dropdown-driven show/hide technique Chapter 4 already built for the label switching:

<div id="tags-section" style="display: none;">
    <p>Tags:</p>
    <?php foreach ($allTags as $tag): ?>
        <label>
            <input type="checkbox" name="tag_ids[]" value="<?= $tag['id'] ?>">
            <?= htmlspecialchars($tag['name']) ?>
        </label>
    <?php endforeach; ?>

    <label>New tag(s), comma-separated:
        <input type="text" name="new_tags" placeholder="e.g. Fantasy, Short Stories">
    </label>
</div>

Then, added to Chapter 4's own updateLabels() function (or a second listener on the same change event):

const tagsSection = document.getElementById('tags-section');

function toggleTagsSection() {
    const selectedOption = select.options[select.selectedIndex];
    const typeName = selectedOption.dataset.name;

    tagsSection.style.display = (typeName === 'book') ? 'block' : 'none';
}

select.addEventListener('change', toggleTagsSection);
toggleTagsSection();

Hiding the Section Doesn't Stop the Data From Being Submitted

A hidden <div> (via display: none) still submits its own checked checkboxes if the form is somehow submitted while it's hidden — CSS visibility and form submission are unrelated. This is why the actual enforcement of "only books get tags" happens where it matters, in PHP, described next — the hidden section is purely a UI convenience, matching the same cosmetic-vs-validation distinction Chapter 4 already drew for the type-specific labels.

Handling Tags on Insert

Added to add_item.php's own POST branch, right after the item itself is inserted (so a real item_id exists to attach tags to), and deliberately gated on the item actually being a book:

$stmt->execute([ /* ...item fields from Chapter 3... */ ]);
$newItemId = (int) $pdo->lastInsertId();

// item_type_id 1 = book, per the item_types seed data (Chapter 1).
// Tags are only ever processed for books, regardless of what a
// tampered request might send for a different type.
if ($itemTypeId === 1) {
    $tagIds = array_map('intval', $_POST['tag_ids'] ?? []);

    $newTagNames = array_filter(array_map('trim', explode(',', $_POST['new_tags'] ?? '')));
    foreach ($newTagNames as $name) {
        $tagIds[] = find_or_create_tag($pdo, $name);
    }

    sync_item_tags($pdo, $newItemId, $tagIds);
}

header('Location: /add_item.php?added=1');
exit;

array_filter() with no callback drops any empty strings from the exploded comma-separated list — this matters for the common real case of a trailing comma ("Fantasy, Short Stories,"), which would otherwise try to create a tag with an empty name.

Handling Tags on Edit

edit_item.php needs the item's own current tags loaded for the GET branch (to pre-check the right boxes), and the same insert-or-sync logic on POST:

// In the POST branch, after the UPDATE runs:
if ((int) $_POST['item_type_id'] === 1) {
    $tagIds = array_map('intval', $_POST['tag_ids'] ?? []);

    $newTagNames = array_filter(array_map('trim', explode(',', $_POST['new_tags'] ?? '')));
    foreach ($newTagNames as $name) {
        $tagIds[] = find_or_create_tag($pdo, $name);
    }

    sync_item_tags($pdo, $id, $tagIds);
} else {
    // The item was changed away from "book" — remove any tags
    // it may have picked up while it was still a book.
    sync_item_tags($pdo, $id, []);
}

// In the GET branch, alongside loading $item and $itemTypes:
$itemTags = get_tags_for_item($pdo, $id);
$itemTagIds = array_column($itemTags, 'id');
$allTags = get_all_tags($pdo);

And in the checkbox markup itself, each box is pre-checked if its id is in $itemTagIds:

<input type="checkbox" name="tag_ids[]" value="<?= $tag['id'] ?>"
    <?= in_array($tag['id'], $itemTagIds) ? 'checked' : '' ?>>

The else Branch Matters

Without the else branch (sync_item_tags($pdo, $id, [])), an item that started as a book, picked up two tags, and was then re-edited to become a DVD would keep those two tags attached forever — invisible in the UI (since the tags section is hidden for non-books) but still sitting in item_tags. Passing an empty array explicitly clears them, matching the honest rule "only books have tags."

The Query the Whole Feature Exists For

With attachment working, filtering the catalogue by tag is a single query — this is a preview of what Chapter 6's own search page uses directly:

$stmt = $pdo->prepare('
    SELECT items.*
    FROM items
    JOIN item_tags ON item_tags.item_id = items.id
    JOIN tags ON tags.id = item_tags.tag_id
    WHERE tags.name = :tag_name
    ORDER BY items.title
');
$stmt->execute(['tag_name' => 'Python']);
$books = $stmt->fetchAll();

Every real book tagged "Python" — including ones tagged Python and something else, since item_tags is a genuine many-to-many table — comes back in one query, exactly the search Chapter 1's own real deadline framing said mattered most.

Hands-On Exercises

Exercise 1

Add the tag checkboxes, the "New tag(s)" text input, and the show/hide JavaScript to your own add_item.php. Add a new book with two brand-new tags typed into the comma-separated field (tags that don't exist yet), then confirm both were created in tags and correctly attached in item_tags.

📄 View solution

Exercise 2

Add the pre-checked checkboxes and the sync logic (including the else branch) to edit_item.php. Edit an existing book to remove one of its two tags and add a third, then confirm item_tags reflects exactly the new set — the removed tag's row gone, the third tag's row added, and the untouched tag's row unchanged.

📄 View solution

Exercise 3

Run the tag-filter query from this chapter's own closing section against your real database for a tag you've actually used, and write out the full result. Then explain, in one or two sentences, why a book tagged with two different tags only appears once in a plain SELECT DISTINCT items.* FROM ... version of this query but could appear twice without the DISTINCT.

📄 View solution

Chapter 5 Quick Reference

find_or_create_tag() — looks up a tag by name, creating it only if it doesn't already exist
sync_item_tags() — diffs the desired tag set against what's currently attached, only touching rows that actually changed
Tags section shown only for books — a UI convenience via display: none, never a substitute for the real server-side item_type_id === 1 check
The else branch on edit — clears tags explicitly if an item's type changes away from book, so stale rows can't linger unseen
The filter query — a single three-table join, no UNION, the direct payoff of Chapter 2's own shared-items-table decision
Next chapter: Search — real lookup across the whole catalogue, not just by tag