Add Search to a PHP and MySQL App

Use prepared statements, index the columns you search, paginate the result, and move to full-text search when substring matching stops being honest.

A search application is where a small database app starts showing its age. The first version is usually a LIKE query wired to a form, which is fine for a few rows and too thin to call a search engine.

This is step 3 of the PHP web-app path. The app already has a simple dynamic site and a news/comments data model; now it needs search without turning every request into a table scan.

Think of search as a library catalog. The form submits a request, the index narrows the shelves, and the query returns only the published records the reader may open. A larger catalog needs better indexing before it needs a separate search service.

Guide Path

Follow this step after news and comments CRUD. After search works, the path pauses at the authentication and sessions reference, then finishes with the custom API capstone.

Start with the Data Shape

Use a table that has searchable text and stable ownership:

CREATE TABLE articles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    summary TEXT NOT NULL,
    body MEDIUMTEXT NOT NULL,
    published_at DATETIME NOT NULL,
    INDEX articles_published_idx (published_at, id)
);

The schema is deliberately boring because the search form needs a table, columns, and a predictable sort order before anything clever helps.

If this is a PHP app, read building the dynamic site starter before adding search. A search screen is just another route unless the database boundary is already a mess.

The Basic Search Query

For a small table, LIKE is enough to prove the route:

SELECT id, title, slug, summary, published_at
FROM articles
WHERE (
    title LIKE :title_term ESCAPE '!'
    OR summary LIKE :summary_term ESCAPE '!'
)
  AND published_at <= CURRENT_TIMESTAMP
ORDER BY published_at DESC, id DESC
LIMIT 20 OFFSET 0;

The PHP side should still use a prepared statement:

<?php

function searchArticles(PDO $pdo, string $query, int $page = 1): array
{
    $query = trim($query);

    if ($query === "") {
        return [];
    }

    if (mb_strlen($query) > 100) {
        throw new InvalidArgumentException("Keep searches under 100 characters.");
    }

    $page = max(1, $page);
    $limit = 20;
    $offset = ($page - 1) * $limit;
    $term = "%" . escapeLike($query) . "%";

    $statement = $pdo->prepare(
        "SELECT id, title, slug, summary, published_at
         FROM articles
         WHERE (
             title LIKE :title_term ESCAPE '!'
             OR summary LIKE :summary_term ESCAPE '!'
         )
           AND published_at <= CURRENT_TIMESTAMP
         ORDER BY published_at DESC, id DESC
         LIMIT :limit OFFSET :offset"
    );

    $statement->bindValue("title_term", $term);
    $statement->bindValue("summary_term", $term);
    $statement->bindValue("limit", $limit, PDO::PARAM_INT);
    $statement->bindValue("offset", $offset, PDO::PARAM_INT);
    $statement->execute();

    return $statement->fetchAll();
}

Binding LIMIT and OFFSET as integers relies on the step 1 PDO connection keeping PDO::ATTR_EMULATE_PREPARES set to false.

The PDO::prepare() documentation is clear about the important bit: bind user input as values, do not paste it into the SQL string.

LIKE has its own small trap: % and _ are wildcard characters, so a user searching for % should not accidentally ask for every row. Escape those characters before building the pattern:

<?php

function escapeLike(string $value): string
{
    return str_replace(
        ["!", "%", "_"],
        ["!!", "!%", "!_"],
        $value,
    );
}

The query declares ! as its escape character, so the replacement function and SQL use the same rule:

WHERE title LIKE :title_term ESCAPE '!'

That is the sort of dull line that saves you from an afternoon of strange search results.

Pagination Belongs in the Query

Do not fetch 10,000 rows and slice them in PHP. Ask the database for the page you need:

SELECT id, title, slug, summary, published_at
FROM articles
WHERE (
    title LIKE :title_term ESCAPE '!'
    OR summary LIKE :summary_term ESCAPE '!'
)
  AND published_at <= CURRENT_TIMESTAMP
ORDER BY published_at DESC, id DESC
LIMIT :limit OFFSET :offset;

Offset pagination is acceptable for the first version. If readers can walk thousands of pages deep, keyset pagination can use the last row's timestamp and ID as a composite cursor:

SELECT id, title, slug, summary, published_at
FROM articles
WHERE
    (
        title LIKE :title_term ESCAPE '!'
        OR summary LIKE :summary_term ESCAPE '!'
    )
    AND published_at <= CURRENT_TIMESTAMP
    AND (
        published_at < :ts
        OR (published_at = :ts AND id < :id)
    )
ORDER BY published_at DESC, id DESC
LIMIT :limit;

The ID breaks ties when several articles share the boundary timestamp, so no row is skipped between pages. Cursor pagination is unnecessary for many small search apps, but pagination still belongs in the database query instead of a PHP array operation after the complete result has loaded.

If you are building the page links in PHP, the PHP pagination guide covers validated page numbers, previous and next URLs, and when offset pagination starts getting expensive.

Indexes Change the Verdict

A normal B-tree index can help exact matches and prefix searches:

CREATE INDEX articles_title_idx ON articles (title);

It usually cannot rescue a leading-wildcard pattern like this:

WHERE title LIKE '%database%'

The leading wildcard means the database has to look inside the value rather than starting from the left edge of the index. That is where the first version stops being enough.

Before adding indexes out of habit, read SQL indexes and query optimization, inspect the plan, and measure latency with representative data. Change the query or index when the measured scan cost no longer meets the application's response-time target.

Full-text Search

When search is about words rather than substrings, use the database's full-text feature or a dedicated search engine.

MySQL supports FULLTEXT indexes on character columns and searches with MATCH ... AGAINST:

ALTER TABLE articles
ADD FULLTEXT articles_search_idx (title, summary, body);

SELECT id, title, slug, summary,
       MATCH(title, summary, body) AGAINST (:rank_query) AS relevance
FROM articles
WHERE MATCH(title, summary, body) AGAINST (:where_query)
  AND published_at <= CURRENT_TIMESTAMP
ORDER BY relevance DESC
LIMIT 20;

Bind the same validated search text to both unique placeholders when PDO uses native prepared statements. MySQL calculates relevance from the indexed text collection and returns the highest-scoring published matches first.

Ship the plain indexed search first, then move to full-text with measured evidence from the catalog. Search code can look sophisticated while returning results that readers cannot use.

Keep the Result Safe

Search input is hostile input because it comes from everyone. Treat it like any other form value:

• Trim it and set a reasonable maximum length.
• Bind it as a value in a prepared statement.
• Escape wildcard characters when using LIKE.
• Escape output when rendering titles and snippets.
• Rate-limit the route if it becomes expensive.

Prepared statements protect the SQL boundary, while output escaping protects the HTML boundary. They solve different problems, which is why the PHP security fundamentals page keeps coming back to boundaries instead of slogans.

A public search route should select only published rows. An editor-only search can use a separate authorized query, but a request flag such as ?include_drafts=1 must never widen the public result by itself.

Render Search Results as Data

The catalog returns records, not trusted markup. Escape each visible value and encode the slug as a URL path segment when the result crosses into HTML.

<form method="get" action="/search">
    <label>
        Search articles
        <input
            type="search"
            name="q"
            value="<?= htmlspecialchars($query, ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8") ?>"
            maxlength="100"
        >
    </label>
</form>

<?php foreach ($results as $article): ?>
    <article>
        <h2>
            <a href="/articles/<?= rawurlencode($article["slug"]) ?>">
                <?= htmlspecialchars($article["title"], ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8") ?>
            </a>
        </h2>
        <p><?= htmlspecialchars($article["summary"], ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8") ?></p>
    </article>
<?php endforeach; ?>

The library catalog can point readers to a shelf without letting a stored title rewrite the catalog page. Prepared statements protect the query, and output escaping protects the result list.

Common Pitfalls & Debugging

A Percent Search Returns Every Row

Symptom: searching for % matches the complete article table. Cause: the application wraps raw input in wildcards without escaping LIKE's special characters. Fix: escape !, %, and _ in the value, then declare the matching ESCAPE '!' clause.

Draft Articles Appear in Public Search

Symptom: a public result links to an unpublished or restricted row. Cause: the search query filters text but omits the publication or authorization predicate. Fix: keep the visibility condition inside every public LIKE and FULLTEXT query.

A Result Title Changes the Page Markup

Symptom: a stored title inserts elements or scripts into the result page. Cause: the template treats database text as trusted HTML. Fix: apply htmlspecialchars() to titles and summaries, and use rawurlencode() for the slug path segment.

Frequently Asked Questions

Can a trailing-wildcard search still use an index?

Yes. A pattern anchored at the start of the value lets a B-tree index seek straight to the matching range, which is why a trailing wildcard behaves completely differently from one wrapped around the term.

Does full-text search match plurals automatically?

Not in natural-language mode by default. Searching for run will not match running or ran, because MySQL does not stem words the way a dedicated search engine does. Plan for that before promising fuzzy matching.

Should the search box use type search or type text?

type search states the intent and gives most browsers a built-in clear control, with no difference in how the value reaches the server. The example here already uses it without saying why.

Should public search require sign-in?

Not when the searched articles are already public. The query must still enforce the publication boundary so drafts and restricted rows cannot appear. Private or account-specific search should require authentication and apply authorization filters inside the database query.

Should search results update as the user types, or only after the form is submitted?

Update on submit for a plain server-rendered search so each result set stays a stable, bookmarkable page. Live-updating as someone types requires debounced requests, cancellation of stale responses, and a script layer this guide's PHP-only approach does not build. Add live search deliberately, not as a default.

Cluster Context

This page sits between PHP and SQL. PHP owns the request, validation, and rendered result. SQL owns the matching, sorting, pagination, and plan.

If the search joins categories, authors, or permissions, review SQL joins before adding filters. If every filter feels awkward, step back to schema design basics. A search form often exposes table design that looked fine when every page was a simple list.

The first version can be small, but the search should not be casual once people depend on its results.

Next, turn the app into an integration surface with the custom API capstone.

This example has been updated from the original CodeWalkers search tutorial, which built its LIKE pattern by concatenating the submitted term into the query. Binding the pattern as a parameter is the reason this version can keep the same working-app shape without the injection hole underneath it.