Add Search to a PHP and MySQL App

A PHP search form sends a query to MySQL and displays matching articles. Start with LIKE for a small dataset. Use a full-text index when readers need word matching and relevance scores.

This tutorial needs PHP with pdo_mysql and mbstring, a MySQL database, and the PDO connection from the dynamic-site guide. It uses a new articles table rather than the previous tutorial’s news_posts table. Keep the database connection and saved publication times in the same timezone.

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.

Create the Articles Table

Store each article’s title, summary, body, URL slug, and publication time in one table.

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 publication time filters out future articles. The ID breaks ties when several articles share a timestamp.

The PHP dynamic-site guide explains how to connect to the database and separate queries from HTML templates.

How Do You Search MySQL from PHP?

Search MySQL from PHP with a prepared SELECT statement, binding the search text as a value. This LIKE query matches part of a title or summary and returns only published articles.

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 to 100 characters or fewer.");
    }

    $page = min(1000, 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.

Bind the search text as a value instead of inserting 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 '!'

How Do You Paginate PHP Search Results?

Paginate PHP search results with LIMIT and OFFSET in the MySQL query. Fetch only the rows needed for the current page.

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 < :before_ts
        OR (published_at = :same_ts AND id < :id)
    )
ORDER BY published_at DESC, id DESC
LIMIT :limit;

Bind the last row’s timestamp to both :before_ts and :same_ts, and its ID to :id. Separate placeholder names are needed with native PDO prepares. The ID breaks timestamp ties; changing publication times while paging can still change which rows appear. 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.

Can a MySQL Index Speed Up a LIKE Search?

A MySQL B-tree index can speed up exact matches and LIKE patterns that start with known text, such as database%. It generally cannot narrow a search that starts with a wildcard.

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.

When Should You Use MySQL Full-Text Search?

Use MySQL full-text search when readers need to find words and rank matching articles by relevance. LIKE is a better fit for simple substring matching.

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.

Full-text search can ignore stopwords and words outside configured length limits. Try representative queries before switching, especially when readers search for short abbreviations.

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 prevent search text from becoming SQL code. HTML escaping prevents titles and summaries from becoming page markup. The PHP security guide explains both.

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.

Display the Search Form and Results

Display search results with escaped titles and summaries. Encode each slug before putting it in a link.

Load your PDO helper and the search functions above before this request handler:

<?php
$query = $_GET["q"] ?? "";
$page = filter_var($_GET["page"] ?? "1", FILTER_VALIDATE_INT, [
    "options" => ["min_range" => 1, "max_range" => 1000],
]);
if (! is_string($query) || mb_strlen($query) > 100 || $page === false) {
    http_response_code(400);
    exit("Invalid search or page number.");
}
$results = searchArticles(db(), $query, $page);

<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>
    <button type="submit">Search</button>
</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; ?>

Show a clear “No matching articles” message when a nonempty search returns no rows. The form uses GET so readers can bookmark or share a search.

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 MySQL trailing-wildcard search use an index?

A MySQL LIKE search with a trailing wildcard, such as database%, can use a B-tree index to narrow the matching range. A pattern starting with %, such as %database%, usually cannot use that range lookup. Check the query plan rather than assuming MySQL will choose an index.

Does MySQL full-text search match plurals automatically?

MySQL’s built-in full-text parser does not automatically reduce words to a shared root. Searching for cat does not inherently match cats. Natural-language search is not the same as stemming or fuzzy matching.

Should the search box use type search or type text?

Use input type search for a search box. It identifies the field’s purpose, and some browsers add a clear button. PHP receives the submitted value in the same way as text input.

Should public search require sign-in?

Public article search does not need sign-in. Its query must exclude unpublished or restricted articles. Private search requires authentication and filters that limit results to records the user may view.

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

A server-rendered PHP search can update after form submission, giving readers a bookmarkable results page. Live search needs JavaScript to delay requests while someone types and ignore stale responses. Add it only if that interaction helps your readers.