Search

Personal Catalogue: PHP & MySQL

Chapter 6 · Search

Chapter 1 named the real question this whole project exists to answer fast: "do I already own this?" Every design decision since then — the shared items table in particular — was made specifically to keep that question a single, simple query. This chapter builds the actual search page.

The Simplest Version First

A basic title search, matching anywhere inside the title, not just at the start:

$stmt = $pdo->prepare('
    SELECT * FROM items
    WHERE title LIKE :query
    ORDER BY title
');
$stmt->execute(['query' => '%' . $searchTerm . '%']);
$results = $stmt->fetchAll();

The % wildcards are built into the PHP string, not the SQL — the placeholder is still bound as one plain parameter value, exactly the same PDO::ATTR_EMULATE_PREPARES => false protection from Chapter 1 applies to it. This is a genuinely important distinction: it would be a real SQL injection risk to build the wildcards by concatenating raw SQL text instead — "title LIKE '%" . $searchTerm . "%'" — since that puts user input directly into the query string.

Searching Creator Too, Not Just Title

A search for "Tolkien" should find "The Hobbit" even though the word "Tolkien" never appears in its title — it's in creator. A real search has to check both columns:

$stmt = $pdo->prepare('
    SELECT * FROM items
    WHERE title LIKE :query OR creator LIKE :query
    ORDER BY title
');
$stmt->execute(['query' => '%' . $searchTerm . '%']);
$results = $stmt->fetchAll();

Named Placeholders Can't Be Reused With execute()'s Array Form

Reusing :query twice in the SQL above and passing one 'query' key in the execute() array actually works correctly with PDO's MySQL driver — it substitutes the same bound value into both positions. This is real, documented PDO/MySQL behavior, but it's not universal across every database driver PDO supports, so it's worth knowing it's a MySQL-specific convenience rather than a general PDO guarantee, in case this project's own database is ever swapped later.

Optional Filter: Item Type

Adding a "Type" dropdown to the search form (All / Book / CD / DVD / Blu-ray) means the query's own WHERE clause has to change shape depending on whether a type was actually chosen. Building this correctly means never string-concatenating raw $_GET values into the SQL — the clause structure changes, but every value still flows through a bound parameter:

$searchTerm = trim($_GET['q'] ?? '');
$typeFilter = (int) ($_GET['type'] ?? 0);

$sql = 'SELECT * FROM items WHERE (title LIKE :query OR creator LIKE :query)';
$params = ['query' => '%' . $searchTerm . '%'];

if ($typeFilter > 0) {
    $sql .= ' AND item_type_id = :type_id';
    $params['type_id'] = $typeFilter;
}

$sql .= ' ORDER BY title';

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$results = $stmt->fetchAll();

The SQL string is assembled conditionally, but every actual piece of user-supplied data — the search term, the type id — still only ever reaches the query as a bound parameter. Building the WHERE clause's own shape in PHP is safe; building a value's own text directly into the SQL string is not, and the difference between those two things is the whole point of this pattern.

Optional Filter: Tag (Reusing Chapter 5's Own Query)

Adding a tag filter means joining item_tags and tags only when a tag was actually selected — otherwise every non-book item would be silently excluded by an unconditional join, since a DVD has no rows in item_tags at all:

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

$sql = 'SELECT items.* FROM items';
$params = [];

if ($tagFilter > 0) {
    $sql .= ' JOIN item_tags ON item_tags.item_id = items.id';
    $sql .= ' AND item_tags.tag_id = :tag_id';
    $params['tag_id'] = $tagFilter;
}

$sql .= ' WHERE (items.title LIKE :query OR items.creator LIKE :query)';
$params['query'] = '%' . $searchTerm . '%';

if ($typeFilter > 0) {
    $sql .= ' AND items.item_type_id = :type_id';
    $params['type_id'] = $typeFilter;
}

$sql .= ' ORDER BY items.title';

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$results = $stmt->fetchAll();

Why the Tag Condition Sits in the JOIN, Not the WHERE

Putting item_tags.tag_id = :tag_id in the JOIN ... ON clause (rather than a separate WHERE item_tags.tag_id = :tag_id) means the join itself only matches the one specific tag's own pairing row — a plain inner join would still work either way for this particular query, since either placement excludes non-matching rows entirely, but keeping the tag condition attached to the join it actually belongs to keeps the query's own logic easy to read as it grows: "join in this tag's own pairings, then filter the resulting rows by title/creator/type."

Escaping Literal % and _ in a Search Term

LIKE's own % (any characters) and _ (any single character) are special wildcard characters — if a real title happens to contain one literally (a book called "50% Off", or a format note with an underscore in it), searching for that exact text needs those characters escaped, or MySQL will interpret them as wildcards instead of literal characters:

function escape_like(string $value): string {
    return str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $value);
}

$searchTerm = escape_like(trim($_GET['q'] ?? ''));
$params['query'] = '%' . $searchTerm . '%';

The backslash itself has to be escaped first, before % and _ — otherwise a search term that already contains a literal backslash would end up double-escaped incorrectly. The order of the str_replace() array matters here for exactly that reason.

A Real, Honest Scope Note

For a small personal catalogue, this edge case is genuinely unlikely to ever come up in practice — few book or CD titles contain a literal % or _. It's included here because it's a real, documented LIKE behavior worth knowing about, not because this project's own real data is expected to trigger it often.

Assembling the Search Page

The full search.php combines every piece above: a form with a text input, a type dropdown (reusing get_item_types() from Chapter 1), and a tag dropdown (reusing get_all_tags() from Chapter 5), all submitting via GET — deliberately GET, not POST, since a search is read-only and a GET request means the results page has its own shareable, bookmarkable URL:

<form method="get">
    <input type="text" name="q" value="<?= htmlspecialchars($searchTerm) ?>" placeholder="Search title or creator...">

    <select name="type">
        <option value="0">All types</option>
        <?php foreach ($itemTypes as $type): ?>
            <option value="<?= $type['id'] ?>" <?= $typeFilter === (int) $type['id'] ? 'selected' : '' ?>>
                <?= htmlspecialchars($type['name']) ?>
            </option>
        <?php endforeach; ?>
    </select>

    <select name="tag">
        <option value="0">Any tag</option>
        <?php foreach ($allTags as $tag): ?>
            <option value="<?= $tag['id'] ?>" <?= $tagFilter === (int) $tag['id'] ? 'selected' : '' ?>>
                <?= htmlspecialchars($tag['name']) ?>
            </option>
        <?php endforeach; ?>
    </select>

    <button type="submit">Search</button>
</form>

Hands-On Exercises

Exercise 1

Build the full search.php from this chapter against your real database. Search for "Tolkien" with the type dropdown left on "All types" and confirm "The Hobbit" appears in the results even though "Tolkien" never appears in its own title.

📄 View solution

Exercise 2

Search with an empty search term but the type dropdown set to "cd." Explain, using the SQL your own PHP would build for this case, why every CD in the catalogue is returned even though the search box was left blank.

📄 View solution

Exercise 3

Add the escape_like() function from this chapter, add a book to your database with a title containing a literal underscore (e.g. "Learn_Python: A Guide"), and confirm that searching for the exact substring "Learn_Python" matches only that book — not accidentally matching every title where any single character happens to sit where the underscore is.

📄 View solution

Chapter 6 Quick Reference

Wildcards built in PHP, bound as one value — '%' . $searchTerm . '%' passed as a single parameter, never concatenated into the raw SQL string
Title OR creator — a real search checks both, since a creator's own name never appears in the title column
Conditional WHERE/JOIN clauses — built up in PHP based on which filters are active, with every actual value still flowing through a bound parameter
escape_like() — escapes literal %/_/\ characters in a search term so LIKE doesn't misinterpret them as wildcards
Search form uses GET — deliberately, so results have their own shareable, bookmarkable URL, unlike the POST-only delete action from Chapter 3
Next chapter: The Catalogue List & Detail Pages