A website search box looks simple, but a useful search feature must do more than check whether two strings are identical.

Visitors may type several words, use only part of a word, make a spelling mistake or search with a related term that does not appear in the page title. Good search results should also place the most relevant pages first instead of sorting everything by date.

This tutorial explains how to build a practical search engine using PHP 8, PDO and MySQL FULLTEXT search. It does not require Elasticsearch, OpenSearch, Solr or an artificial-intelligence API.

By the end, the project will support:

• Full-text searching across titles, excerpts and article content
• Relevance scoring
• Extra weight for title matches
• Prefix matching such as migrat*
• Category filters
• Pagination
• Synonym expansion
• Safe result highlighting
• A fallback for short or uncommon searches
• “Did you mean?” spelling suggestions
• Search analytics without storing visitors’ personal information

The completed example is suitable for a blog, documentation website, tutorial library or small product catalogue.

---

Quick Answer

The simplest reliable approach is to let MySQL find and rank possible results with a FULLTEXT index, then use PHP to improve the query and display the results.

The core SQL looks like this:

SELECT
    id,
    title,
    slug,
    excerpt,
    MATCH(title, excerpt, body)
        AGAINST(:query IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE status = 'published'
  AND MATCH(title, excerpt, body)
      AGAINST(:boolean_query IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 10;

MySQL’s natural-language mode returns a relevance value for each row. Boolean mode adds useful operators, including required words, excluded words, phrases and prefix wildcards. According to the MySQL full-text documentation, an InnoDB table needs a matching FULLTEXT index for the columns used in MATCH().

PHP can then:

1. Normalise the visitor’s query.
2. Convert its words into a safe Boolean query.
3. Add related words.
4. Apply filters and pagination.
5. Escape and highlight the displayed results.

This gives a useful search experience without adding a separate search server.

---

What Makes a Search Engine “Smart”?

For this tutorial, “smart” does not mean that the search engine understands language like a chatbot. It means the system handles normal visitor behaviour sensibly.

Visitor behaviour	Search feature
Searches php migration	Requires both important words
Types migrat	Prefix search can find migration and migrations
Searches js	A synonym can also search for JavaScript
Misspells databse	The page can suggest database
Searches within Tutorials	A category filter limits the results
Finds many results	Pagination divides them into manageable pages
Searches an exact title	Title weighting places it near the top
Searches a two-letter term	A controlled LIKE fallback can still help

A search engine becomes useful through several small improvements. It does not need to begin with machine learning.

---

Why Not Use Only SQL LIKE?

A beginner may start with this query:

SELECT *
FROM articles
WHERE title LIKE '%php%'
   OR excerpt LIKE '%php%'
   OR body LIKE '%php%';

This works on a small table, but it has important limitations:

• A leading wildcard such as %php% normally prevents efficient use of a standard B-tree index.
• Every matching row is treated equally.
• Searching several words becomes awkward.
• Common words can create noisy results.
• It does not provide full-text relevance scoring.
• Scanning a large body column becomes expensive as the table grows.

LIKE is still useful as a controlled fallback for short or unusual terms. It should not be the main search method for a growing content library.

---

How the Project Works

The request moves through four stages:

1. PHP validates the request. It limits the query length, category and page number.
2. PHP builds a search expression. It removes Boolean operators supplied by the visitor, keeps useful words and adds optional synonyms.
3. MySQL finds candidates. A FULLTEXT index filters the table and calculates relevance.
4. PHP presents the results. It escapes all database content, highlights matching words and creates pagination links.

The database remains the source of truth. There is no separate search index to synchronise.

---

Requirements

The example uses:

• PHP 8.1 or later
• The PDO MySQL extension
• MySQL 8.0 or later
• An InnoDB database
• UTF-8 using utf8mb4

The code also uses the mbstring extension for safer Unicode string handling.

Check the installed PHP version:

php -v

Check whether PDO MySQL and mbstring are enabled:

php -m | grep -E 'pdo_mysql|mbstring'

On Debian or Ubuntu, the packages are commonly installed with:

sudo apt install php-mysql php-mbstring

Package names may differ if several PHP versions are installed.

---

Project Structure

Create a folder containing these files:

smart-search/
├── config.php
├── functions.php
├── search.php
└── setup.sql

setup.sql creates the sample data. config.php opens the database connection. functions.php contains reusable search helpers. search.php processes the request and displays the interface.

---

Step 1: Create the Database

Create setup.sql:

CREATE DATABASE IF NOT EXISTS smart_search
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

USE smart_search;

CREATE TABLE categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE articles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    excerpt TEXT NOT NULL,
    body LONGTEXT NOT NULL,
    status ENUM('draft', 'published') NOT NULL DEFAULT 'draft',
    published_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_articles_category
        FOREIGN KEY (category_id) REFERENCES categories(id),

    INDEX idx_articles_listing (status, category_id, published_at),
    FULLTEXT INDEX ft_articles_title (title),
    FULLTEXT INDEX ft_articles_all (title, excerpt, body)
) ENGINE=InnoDB;

CREATE TABLE search_terms (
    term VARCHAR(100) PRIMARY KEY,
    popularity INT UNSIGNED NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE search_queries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    query VARCHAR(200) NOT NULL,
    results_count INT UNSIGNED NOT NULL,
    searched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_search_queries_date (searched_at),
    INDEX idx_search_queries_results (results_count)
) ENGINE=InnoDB;

INSERT INTO categories (name, slug) VALUES
    ('PHP', 'php'),
    ('Databases', 'databases'),
    ('Security', 'security');

INSERT INTO articles
    (category_id, title, slug, excerpt, body, status, published_at)
VALUES
    (
        1,
        'PHP Form Validation for Beginners',
        'php-form-validation-for-beginners',
        'Learn how to validate and safely display data submitted through a PHP form.',
        'This tutorial covers required fields, email validation, error messages and output escaping with PHP.',
        'published',
        '2026-01-10 09:00:00'
    ),
    (
        2,
        'Understanding MySQL Indexes',
        'understanding-mysql-indexes',
        'A practical introduction to database indexes, query plans and performance.',
        'Learn when MySQL can use an index, how composite indexes work and how to inspect a query with EXPLAIN.',
        'published',
        '2026-02-15 09:00:00'
    ),
    (
        3,
        'Preventing SQL Injection with PDO',
        'preventing-sql-injection-with-pdo',
        'Use prepared statements and parameter binding to protect PHP database queries.',
        'This security guide explains SQL injection, PDO prepared statements and safe query construction.',
        'published',
        '2026-03-20 09:00:00'
    ),
    (
        1,
        'Building a REST API with PHP',
        'building-a-rest-api-with-php',
        'Create a small JSON API using modern PHP and a MySQL database.',
        'The project includes routing, validation, prepared statements, JSON responses and error handling.',
        'published',
        '2026-04-05 09:00:00'
    );

INSERT INTO search_terms (term, popularity) VALUES
    ('php', 100),
    ('mysql', 90),
    ('database', 80),
    ('validation', 70),
    ('security', 65),
    ('injection', 60),
    ('indexes', 50),
    ('prepared', 45),
    ('statements', 40),
    ('api', 35);

Two full-text indexes are created deliberately:

• ft_articles_title lets the ranking formula give title matches extra weight.
• ft_articles_all searches the title, excerpt and body together.

The columns in a MATCH() expression must correspond to a suitable FULLTEXT index. Do not change one list without changing the related index and queries.

Import the file:

mysql -u root -p < setup.sql

Use a restricted application account in production rather than connecting the website as the MySQL root user.

---

Step 2: Create a Safe PDO Connection

Create config.php:

<?php

declare(strict_types=1);

$databaseHost = getenv('SEARCH_DB_HOST') ?: '127.0.0.1';
$databaseName = getenv('SEARCH_DB_NAME') ?: 'smart_search';
$databaseUser = getenv('SEARCH_DB_USER') ?: 'search_user';
$databasePass = getenv('SEARCH_DB_PASS') ?: '';

$dsn = sprintf(
    'mysql:host=%s;dbname=%s;charset=utf8mb4',
    $databaseHost,
    $databaseName
);

$pdo = new PDO($dsn, $databaseUser, $databasePass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_STRINGIFY_FETCHES => false,
]);

Keep credentials in environment variables. Do not commit a real password to Git or place it in a public web directory.

PDO placeholders represent data values, not SQL identifiers. Category IDs, query text, limits and offsets can be bound as values. A visitor must never be allowed to supply a table name, column name or raw ORDER BY expression.

The PHP PDO manual explains that parameters should be represented by named or question-mark placeholders and that a unique marker should normally be used for each value in a prepared statement.

---

Step 3: Build the Search Helper Functions

Create functions.php:

<?php

declare(strict_types=1);

function escapeHtml(string $value): string
{
    return htmlspecialchars(
        $value,
        ENT_QUOTES | ENT_SUBSTITUTE,
        'UTF-8'
    );
}

function normaliseQuery(string $query): string
{
    $query = mb_strtolower(trim($query), 'UTF-8');

    // Remove MySQL Boolean operators supplied by the visitor.
    $query = preg_replace('/[^\p{L}\p{N}\s-]+/u', ' ', $query) ?? '';
    $query = preg_replace('/\s+/u', ' ', $query) ?? '';

    return mb_substr(trim($query), 0, 200, 'UTF-8');
}

function queryTerms(string $query): array
{
    if ($query === '') {
        return [];
    }

    $parts = preg_split('/\s+/u', $query) ?: [];
    $terms = [];

    foreach ($parts as $part) {
        $part = trim($part, '-');

        if (mb_strlen($part, 'UTF-8') < 2) {
            continue;
        }

        $terms[] = mb_substr($part, 0, 40, 'UTF-8');

        if (count($terms) === 8) {
            break;
        }
    }

    return array_values(array_unique($terms));
}

function buildBooleanQuery(array $terms): string
{
    $equivalents = [
        'js' => ['javascript'],
        'javascript' => ['js'],
        'db' => ['database'],
        'database' => ['db'],
        'secure' => ['security'],
        'security' => ['secure'],
    ];

    $relatedTerms = [
        'mysql' => ['database', 'sql'],
        'php' => ['backend'],
    ];

    $parts = [];

    foreach ($terms as $term) {
        $alternatives = [$term . '*'];

        foreach ($equivalents[$term] ?? [] as $equivalent) {
            $alternatives[] = $equivalent . '*';
        }

        // Require the original word or an approved equivalent.
        $parts[] = count($alternatives) === 1
            ? '+' . $alternatives[0]
            : '+(' . implode(' ', $alternatives) . ')';

        // Related words are optional and can improve a result's score.
        foreach ($relatedTerms[$term] ?? [] as $relatedTerm) {
            $parts[] = $relatedTerm . '*';
        }
    }

    return implode(' ', array_unique($parts));
}

function highlightTerms(string $text, array $terms): string
{
    if ($terms === []) {
        return escapeHtml($text);
    }

    usort(
        $terms,
        static fn (string $a, string $b): int =>
            mb_strlen($b, 'UTF-8') <=> mb_strlen($a, 'UTF-8')
    );

    $escapedTerms = array_map(
        static fn (string $term): string => preg_quote($term, '/'),
        $terms
    );

    $pattern = '/(' . implode('|', $escapedTerms) . ')/iu';
    $parts = preg_split(
        $pattern,
        $text,
        -1,
        PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_NO_EMPTY
    );

    if ($parts === false) {
        return escapeHtml($text);
    }

    $output = '';

    foreach ($parts as $part) {
        if (preg_match($pattern, $part) === 1) {
            $output .= '<mark>' . escapeHtml($part) . '</mark>';
        } else {
            $output .= escapeHtml($part);
        }
    }

    return $output;
}

function pageUrl(string $query, int $categoryId, int $page): string
{
    return '?' . http_build_query([
        'q' => $query,
        'category' => $categoryId ?: null,
        'page' => $page,
    ]);
}

Why remove Boolean operators?

MySQL Boolean search assigns meaning to characters such as +, -, *, ", parentheses and @. Passing raw visitor input into Boolean mode would let visitors change the structure of the search expression or produce invalid queries.

The application therefore keeps only letters, numbers, whitespace and ordinary hyphens. It creates the operators itself after validation.

Why limit the query?

Search endpoints are public and easy to automate. Limiting the input to 200 characters and eight useful terms prevents needlessly expensive expressions. A production site should also apply a reasonable request-rate limit at the application, reverse-proxy or CDN layer.

Why append an asterisk?

In MySQL Boolean full-text search, the trailing * is a prefix operator. A search for migrat* can match migrate, migration and migrations.

The MySQL manual also notes that a wildcarded term is not removed merely because it is shorter than the configured minimum token length or is a stopword. This makes prefix mode helpful, but it should still be tested with the vocabulary used by the real website.

---

Step 4: Build the Complete Search Page

Create search.php:

<?php

declare(strict_types=1);

require __DIR__ . '/config.php';
require __DIR__ . '/functions.php';

$query = normaliseQuery((string) ($_GET['q'] ?? ''));
$categoryId = filter_input(
    INPUT_GET,
    'category',
    FILTER_VALIDATE_INT,
    ['options' => ['min_range' => 1]]
) ?: 0;
$page = filter_input(
    INPUT_GET,
    'page',
    FILTER_VALIDATE_INT,
    ['options' => ['min_range' => 1]]
) ?: 1;

$perPage = 10;
$results = [];
$total = 0;
$usedFallback = false;
$terms = queryTerms($query);
$canSearch = mb_strlen($query, 'UTF-8') >= 2;

$categoryStatement = $pdo->query(
    'SELECT id, name FROM categories ORDER BY name'
);
$categories = $categoryStatement->fetchAll();

if ($canSearch && $terms !== []) {
    $booleanQuery = buildBooleanQuery($terms);
    $offset = ($page - 1) * $perPage;

    $categoryWhere = $categoryId > 0
        ? ' AND a.category_id = :category_id'
        : '';

    $countSql = <<<SQL
        SELECT COUNT(*)
        FROM articles AS a
        WHERE a.status = 'published'
          AND a.published_at <= NOW()
          AND MATCH(a.title, a.excerpt, a.body)
              AGAINST(:count_boolean IN BOOLEAN MODE)
        {$categoryWhere}
    SQL;

    $countStatement = $pdo->prepare($countSql);
    $countStatement->bindValue(':count_boolean', $booleanQuery);

    if ($categoryId > 0) {
        $countStatement->bindValue(
            ':category_id',
            $categoryId,
            PDO::PARAM_INT
        );
    }

    $countStatement->execute();
    $total = (int) $countStatement->fetchColumn();

    if ($total > 0) {
        $searchSql = <<<SQL
            SELECT
                a.id,
                a.title,
                a.slug,
                a.excerpt,
                a.published_at,
                c.name AS category_name,
                (
                    MATCH(a.title)
                        AGAINST(:rank_title IN NATURAL LANGUAGE MODE) * 4
                    + MATCH(a.title, a.excerpt, a.body)
                        AGAINST(:rank_all IN NATURAL LANGUAGE MODE)
                    + MATCH(a.title, a.excerpt, a.body)
                        AGAINST(:rank_boolean IN BOOLEAN MODE) * 0.5
                    + CASE WHEN a.title = :exact_title THEN 8 ELSE 0 END
                    + CASE WHEN a.title LIKE :title_prefix THEN 4 ELSE 0 END
                ) AS score
            FROM articles AS a
            INNER JOIN categories AS c ON c.id = a.category_id
            WHERE a.status = 'published'
              AND a.published_at <= NOW()
              AND MATCH(a.title, a.excerpt, a.body)
                  AGAINST(:filter_boolean IN BOOLEAN MODE)
              {$categoryWhere}
            ORDER BY score DESC, a.published_at DESC
            LIMIT :limit OFFSET :offset
        SQL;

        $statement = $pdo->prepare($searchSql);
        $statement->bindValue(':rank_title', $query);
        $statement->bindValue(':rank_all', $query);
        $statement->bindValue(':rank_boolean', $booleanQuery);
        $statement->bindValue(':exact_title', $query);
        $statement->bindValue(':title_prefix', $query . '%');
        $statement->bindValue(':filter_boolean', $booleanQuery);

        if ($categoryId > 0) {
            $statement->bindValue(
                ':category_id',
                $categoryId,
                PDO::PARAM_INT
            );
        }

        $statement->bindValue(':limit', $perPage, PDO::PARAM_INT);
        $statement->bindValue(':offset', $offset, PDO::PARAM_INT);
        $statement->execute();
        $results = $statement->fetchAll();
    }
}

// Controlled fallback for short terms or words absent from the FULLTEXT index.
if ($canSearch && $results === [] && $page === 1) {
    $usedFallback = true;
    $fallbackCategory = $categoryId > 0
        ? ' AND a.category_id = :fallback_category'
        : '';

    $fallbackSql = <<<SQL
        SELECT
            a.id,
            a.title,
            a.slug,
            a.excerpt,
            a.published_at,
            c.name AS category_name,
            CASE
                WHEN a.title = :fallback_exact THEN 10
                WHEN a.title LIKE :fallback_prefix THEN 5
                ELSE 1
            END AS score
        FROM articles AS a
        INNER JOIN categories AS c ON c.id = a.category_id
        WHERE a.status = 'published'
          AND a.published_at <= NOW()
          AND (
              a.title LIKE :fallback_title
              OR a.excerpt LIKE :fallback_excerpt
          )
          {$fallbackCategory}
        ORDER BY score DESC, a.published_at DESC
        LIMIT 10
    SQL;

    $fallback = $pdo->prepare($fallbackSql);
    $fallback->bindValue(':fallback_exact', $query);
    $fallback->bindValue(':fallback_prefix', $query . '%');
    $fallback->bindValue(':fallback_title', '%' . $query . '%');
    $fallback->bindValue(':fallback_excerpt', '%' . $query . '%');

    if ($categoryId > 0) {
        $fallback->bindValue(
            ':fallback_category',
            $categoryId,
            PDO::PARAM_INT
        );
    }

    $fallback->execute();
    $results = $fallback->fetchAll();
    $total = count($results);
}

$totalPages = $usedFallback
    ? 1
    : max(1, (int) ceil($total / $perPage));

if ($canSearch && $page === 1) {
    $log = $pdo->prepare(
        'INSERT INTO search_queries (query, results_count)
         VALUES (:query, :results_count)'
    );
    $log->bindValue(':query', $query);
    $log->bindValue(':results_count', $total, PDO::PARAM_INT);
    $log->execute();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Smart PHP Search</title>
    <style>
        * { box-sizing: border-box; }
        body {
            margin: 0;
            color: #1f2937;
            background: #f8fafc;
            font-family: Arial, sans-serif;
            line-height: 1.6;
        }
        main {
            width: min(900px, calc(100% - 32px));
            margin: 48px auto;
        }
        form, article {
            padding: 24px;
            margin-bottom: 18px;
            background: #fff;
            border: 1px solid #e5e7eb;
            border-radius: 12px;
        }
        .search-row {
            display: grid;
            grid-template-columns: 1fr 180px auto;
            gap: 10px;
        }
        input, select, button {
            min-height: 44px;
            padding: 10px 12px;
            font: inherit;
        }
        button {
            color: #fff;
            background: #2563eb;
            border: 0;
            border-radius: 6px;
            cursor: pointer;
        }
        h1 { line-height: 1.2; }
        h2 { margin: 0 0 8px; line-height: 1.3; }
        h2 a { color: #1d4ed8; text-decoration: none; }
        .meta { color: #64748b; font-size: 0.9rem; }
        mark { background: #fef08a; padding: 0 2px; }
        .pagination { display: flex; flex-wrap: wrap; gap: 8px; }
        .pagination a, .pagination strong {
            padding: 7px 11px;
            background: #fff;
            border: 1px solid #cbd5e1;
            border-radius: 6px;
            text-decoration: none;
        }
        @media (max-width: 650px) {
            .search-row { grid-template-columns: 1fr; }
        }
    </style>
</head>
<body>
<main>
    <h1>Search the Article Library</h1>

    <form method="get" action="">
        <label for="q">What are you looking for?</label>
        <div class="search-row">
            <input
                id="q"
                name="q"
                type="search"
                value="<?= escapeHtml($query) ?>"
                minlength="2"
                maxlength="200"
                required
            >

            <select name="category" aria-label="Category">
                <option value="0">All categories</option>
                <?php foreach ($categories as $category): ?>
                    <option
                        value="<?= (int) $category['id'] ?>"
                        <?= $categoryId === (int) $category['id']
                            ? 'selected'
                            : '' ?>
                    >
                        <?= escapeHtml($category['name']) ?>
                    </option>
                <?php endforeach; ?>
            </select>

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

    <?php if ($query !== ''): ?>
        <p>
            <?= number_format($total) ?> result<?= $total === 1 ? '' : 's' ?>
            for <strong><?= escapeHtml($query) ?></strong>
        </p>

        <?php if ($usedFallback && $results !== []): ?>
            <p>Showing broader title and excerpt matches.</p>
        <?php endif; ?>

        <?php if ($results === []): ?>
            <article>
                <h2>No results found</h2>
                <p>Try fewer words, remove the category filter or check the spelling.</p>
            </article>
        <?php endif; ?>

        <?php foreach ($results as $result): ?>
            <article>
                <h2>
                    <a href="/articles/<?= escapeHtml($result['slug']) ?>">
                        <?= highlightTerms($result['title'], $terms) ?>
                    </a>
                </h2>

                <p class="meta">
                    <?= escapeHtml($result['category_name']) ?> ·
                    <?= escapeHtml(date(
                        'j M Y',
                        strtotime($result['published_at'])
                    )) ?>
                </p>

                <p><?= highlightTerms($result['excerpt'], $terms) ?></p>
            </article>
        <?php endforeach; ?>

        <?php if ($totalPages > 1): ?>
            <nav class="pagination" aria-label="Search-result pages">
                <?php for ($number = 1; $number <= $totalPages; $number++): ?>
                    <?php if ($number === $page): ?>
                        <strong aria-current="page"><?= $number ?></strong>
                    <?php else: ?>
                        <a href="<?= escapeHtml(pageUrl(
                            $query,
                            $categoryId,
                            $number
                        )) ?>">
                            <?= $number ?>
                        </a>
                    <?php endif; ?>
                <?php endfor; ?>
            </nav>
        <?php endif; ?>
    <?php endif; ?>
</main>
</body>
</html>

The same named placeholder is not reused for different positions. This keeps the query compatible with native PDO prepared statements.

The LIMIT and OFFSET values are bound explicitly as integers. They are calculated by the application rather than accepted as raw SQL text.

---

Step 5: Run the Project

Set the database credentials in the terminal:

export SEARCH_DB_HOST='127.0.0.1'
export SEARCH_DB_NAME='smart_search'
export SEARCH_DB_USER='search_user'
export SEARCH_DB_PASS='replace-with-a-strong-password'

Start PHP’s development server from the project folder:

php -S 127.0.0.1:8000

Open:

http://127.0.0.1:8000/search.php

Try these searches:

Search	Expected behaviour
php	Finds the PHP tutorials
php form	Requires both prefixes and ranks the form tutorial highly
mysql index	Finds the database indexing article
inject	Prefix matching finds injection
db	Treats database as an approved equivalent
pdo	Finds the security tutorial
api with PHP filter	Limits the results to the PHP category

The built-in server is for local development. Use Apache, Nginx or another properly configured production server for a public site.

---

Understanding the Ranking Formula

The result score combines five signals:

MATCH(title) AGAINST(:query) * 4
+ MATCH(title, excerpt, body) AGAINST(:query)
+ MATCH(title, excerpt, body) AGAINST(:boolean_query) * 0.5
+ exact_title_bonus
+ title_prefix_bonus

Signal	Purpose
Title relevance × 4	A title match is normally more intentional than a body mention
Overall relevance	Measures the query against all searchable content
Boolean relevance × 0.5	Gives a smaller boost to approved equivalent and related terms
Exact title + 8	Rewards a query that equals the complete title
Title prefix + 4	Rewards titles beginning with the complete query

MySQL describes its InnoDB full-text relevance as a variation of TF-IDF: words contribute more when they occur in a document but are less common across the complete collection. The score is useful for ordering results, but its raw numeric value should not be shown as a percentage. A score of 3.2 does not mean “32% relevant.”

Ranking weights are business rules. Test them with real queries and adjust them gradually. Do not change several weights simultaneously, because that makes improvements difficult to measure.

---

Adding “Did You Mean?” Suggestions

Full-text prefix matching helps with incomplete words, but it does not automatically correct a misspelling such as databse.

PHP’s levenshtein() calculates the minimum insertions, replacements and deletions required to transform one string into another. It is useful for comparing a short query term with a small, controlled vocabulary.

Add this function to functions.php:

function spellingSuggestion(PDO $pdo, string $term): ?string
{
    if (mb_strlen($term, 'UTF-8') < 4 || strlen($term) > 40) {
        return null;
    }

    $statement = $pdo->query(
        'SELECT term
         FROM search_terms
         ORDER BY popularity DESC
         LIMIT 500'
    );

    $bestTerm = null;
    $bestDistance = PHP_INT_MAX;

    foreach ($statement->fetchAll(PDO::FETCH_COLUMN) as $candidate) {
        $distance = levenshtein($term, (string) $candidate);

        if ($distance < $bestDistance) {
            $bestDistance = $distance;
            $bestTerm = (string) $candidate;
        }
    }

    $maximumDistance = mb_strlen($term, 'UTF-8') <= 6 ? 1 : 2;

    if ($bestTerm === $term || $bestDistance > $maximumDistance) {
        return null;
    }

    return $bestTerm;
}

When there are no results and the query contains one word:

$suggestion = null;

if ($results === [] && count($terms) === 1) {
    $suggestion = spellingSuggestion($pdo, $terms[0]);
}

Display it as a link:

<?php if ($suggestion !== null): ?>
    <p>
        Did you mean
        <a href="<?= escapeHtml(pageUrl($suggestion, $categoryId, 1)) ?>">
            <?= escapeHtml($suggestion) ?>
        </a>?
    </p>
<?php endif; ?>

Do not compare every query with every word in a large database. Levenshtein comparison has computational cost. Use a limited vocabulary based on popular tags, approved keywords and successful searches.

For multilingual text, PHP’s standard levenshtein() compares bytes rather than providing language-aware linguistic correction. A dedicated search engine becomes more appropriate when high-quality fuzzy matching across several languages is essential.

---

Improving Synonyms

The tutorial keeps synonyms in a PHP array so the behaviour is easy to understand. A real website may move them to a table:

CREATE TABLE search_synonyms (
    term VARCHAR(100) NOT NULL,
    synonym VARCHAR(100) NOT NULL,
    PRIMARY KEY (term, synonym)
) ENGINE=InnoDB;

Possible pairs include:

Visitor term	Optional related term
js	javascript
db	database
air con	air conditioner
vacuum mop	wet dry vacuum
yen converter	currency converter

True equivalents can form a required alternatives group. For example, +(js* javascript*) means that a result must contain either form. Broader related terms should remain optional. If a visitor searches mysql, requiring every result to contain database as well could incorrectly remove a relevant page.

Review synonym pairs manually. Two words that are related are not always interchangeable.

---

Safe Highlighting

Search highlighting creates an easy place to introduce cross-site scripting.

This is unsafe:

echo str_ireplace($query, '<mark>' . $query . '</mark>', $result['title']);

It mixes HTML markup with unescaped visitor input and database content.

The highlightTerms() helper splits the original text, escapes every piece with htmlspecialchars() and introduces only the application’s own <mark> tags. PHP documents that htmlspecialchars() converts characters such as &, quotes, < and > into HTML entities.

Output escaping is still required even if the database content was entered through an administrator-only form. Stored data should not be assumed safe for every output context.

---

Common Full-Text Search Problems

1. A Word Produces No Results

The word may:

• Be shorter than the configured minimum token size
• Appear in the stopword list
• Be absent when the index was built
• Use a language that needs different tokenisation
• Be searched against columns that do not match the index

Check the InnoDB token size:

SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

MySQL documents that changing the minimum or maximum word length requires rebuilding the affected full-text indexes. Do not change the server setting only to solve one unusual query; test the effect on index size and relevance first.

2. MATCH Columns Do Not Match the Index

This query:

MATCH(title, body) AGAINST('php')

cannot simply use an index created for a different column combination:

FULLTEXT(title, excerpt, body)

Create the index that matches the MATCH() list or make the query use the indexed list.

3. Boolean Results Are Not Properly Sorted

Boolean mode does not promise automatic descending relevance order. Calculate a score and use an explicit ORDER BY score DESC.

4. Every Word Is Required

The sample uses +word* for each original term. This gives precise results but may be too strict for a large query.

One alternative is:

• Require the first two meaningful terms.
• Make later terms optional.
• Remove common filler words in PHP.
• Retry using optional terms only when the strict search returns nothing.

Make fallback behaviour visible to the visitor instead of silently returning unrelated content.

5. Pagination Becomes Slow on Deep Pages

LIMIT 10 OFFSET 100000 makes MySQL find and skip many earlier rows. Standard offset pagination is acceptable for ordinary website search, where users rarely browse hundreds of pages.

For very deep result sets, use cursor-based pagination with a stable combination such as score, publication date and ID. Because calculated floating-point scores can tie or change as content changes, cursor search needs more careful design than a normal article list.

6. Search Finds Draft Content

Always apply visibility rules inside the SQL query:

WHERE status = 'published'
  AND published_at <= NOW()

Do not fetch hidden rows and remove them later in PHP. The database query should never return content that the visitor is not authorised to see.

---

Measuring Search Quality

The search_queries table records the normalised query, result count and time. It deliberately does not store an IP address, user-agent string or visitor identity.

Useful weekly reports include:

Searches Returning No Results

SELECT query, COUNT(*) AS searches
FROM search_queries
WHERE results_count = 0
  AND searched_at >= NOW() - INTERVAL 30 DAY
GROUP BY query
ORDER BY searches DESC
LIMIT 50;

These queries can reveal:

• Missing articles
• Missing product names
• Common misspellings
• Needed synonyms
• Terms excluded by full-text settings

Most Popular Searches

SELECT query, COUNT(*) AS searches
FROM search_queries
WHERE searched_at >= NOW() - INTERVAL 30 DAY
GROUP BY query
ORDER BY searches DESC
LIMIT 50;

Popular searches can inform navigation, internal links and future content.

Better Success Metrics

A non-zero result count does not prove that the results were useful. If privacy requirements allow, measure aggregate events such as:

• Search-result click-through rate
• Percentage of searches followed by another query
• Queries that lead to a conversion or completed task
• Position of the result that visitors select

Avoid collecting more personal data than the site genuinely needs. Define a retention period for raw search logs and aggregate older records.

---

Performance Checklist

Use this checklist before publishing:

• Confirm that the table uses InnoDB.
• Create a FULLTEXT index matching every MATCH() column list.
• Keep the normal status, category and publication-date index.
• Limit query length and term count.
• Limit results per page.
• Bind values through PDO prepared statements.
• Avoid searching a large body column with %term% except as a limited fallback.
• Use EXPLAIN to inspect the main query.
• Apply server-side rate limiting.
• Cache repeated anonymous searches if the content changes infrequently.
• Re-test results after changing token sizes, stopwords or ranking weights.

Inspect the query plan with:

EXPLAIN
SELECT id, title
FROM articles
WHERE MATCH(title, excerpt, body)
      AGAINST('+php* +security*' IN BOOLEAN MODE);

Measure with realistic data. A table containing four sample articles cannot reveal how the query will behave with 100,000 articles.

---

Security Checklist

A search page accepts public input, so treat it like any other exposed endpoint.

• Use PDO placeholders for every visitor-supplied value.
• Construct SQL identifiers and sorting rules from a fixed application allowlist.
• Remove Boolean operators from raw query text.
• Escape query text, titles, excerpts, categories, slugs and URLs for their output context.
• Set a maximum query length and maximum term count.
• Bind LIMIT and OFFSET as integers.
• Do not display database exception messages in production.
• Apply rate limits and request-size limits.
• Search only rows the visitor is allowed to access.
• Use a restricted database user.
• Keep database credentials outside the repository and web root.
• Add sensible HTTP security headers.

Prepared statements protect bound values from SQL injection. They do not make dynamically concatenated column names, directions or SQL fragments safe.

---

When MySQL Search Is Enough

MySQL full-text search is a good starting choice when:

• The content already lives in MySQL.
• The site has a modest or medium-sized collection.
• Search traffic is not extreme.
• Basic relevance, filters and prefix matching are sufficient.
• The team wants minimal infrastructure.
• Immediate consistency is valuable.

It is especially suitable for a blog or internal knowledge base that has outgrown LIKE but does not yet need a separate search cluster.

---

When to Use a Dedicated Search Engine

Consider a dedicated engine when the project requires several of these features:

• High-quality typo tolerance on every query
• Fast autocomplete and search-as-you-type
• Complex faceted navigation
• Language-specific stemming and tokenisation
• Geographical or vector search
• Millions of records with heavy search traffic
• Advanced relevance tuning and experimentation
• Search across several databases and services
• Independent scaling of indexing and querying

The trade-off is operational complexity. A separate engine creates another copy of searchable data, so the application must handle indexing, updates, deletions, retries, monitoring and recovery from synchronisation failures.

Start with the smallest system that meets the actual requirements. Build search analytics early so a future migration is based on evidence rather than guesswork.

---

Testing the Search Engine

Test more than happy-path queries.

Test	Expected outcome
Empty query	No database search is executed
One-character query	Rejected by the form or ignored by term parsing
Query longer than 200 characters	Truncated server-side and limited client-side
php' OR 1=1 --	Treated as search text, not SQL
<script>alert(1)</script>	Displayed only as escaped text
++++php****	Normalised before the Boolean expression is built
Invalid category ID	Treated as no category or returns no matching category
Page -5	Replaced with page 1
Page beyond the final page	Returns no rows without exposing an error
Draft article containing the query	Never shown
Future scheduled article	Never shown before published_at
Two identical relevance scores	Newer publication date breaks the tie
Misspelled popular term	Offers a cautious suggestion

Also create a small relevance test set:

Query: php validation
Expected top result: PHP Form Validation for Beginners

Query: sql injection
Expected top result: Preventing SQL Injection with PDO

Query: mysql index
Expected top result: Understanding MySQL Indexes

Run these checks whenever ranking logic, content structure, stopwords or indexes change.

---

Frequently Asked Questions

Can PHP build a search engine without Elasticsearch?

Yes. PHP and MySQL full-text search can provide useful relevance ranking, Boolean operators, prefix matching, filters and pagination for many websites. A dedicated search platform becomes useful when the requirements exceed what the database can deliver comfortably.

Is FULLTEXT faster than LIKE?

For word-based searches across a growing text collection, a suitable FULLTEXT index is normally much more appropriate than scanning large columns with LIKE '%term%'. Actual performance depends on the data, query and server, so verify it with EXPLAIN and realistic load tests.

Does MySQL FULLTEXT support partial words?

Boolean mode supports a trailing * prefix operator. For example, migrat* can match words beginning with migrat. It is prefix matching, not arbitrary substring matching.

Can visitors use quotes and minus signs?

MySQL supports advanced Boolean operators, but a public application should not pass them through automatically. The tutorial strips them and creates a controlled expression. If advanced search syntax is a product requirement, parse and validate each supported operator explicitly.

Why use both natural-language and Boolean modes?

Boolean mode filters the candidates and enables required prefixes. Natural-language mode supplies an intuitive relevance contribution for ranking those candidates. The application then adds its own title bonuses.

Can I search HTML stored in the body column?

It is better to index searchable plain text. HTML tags, menus, shortcodes and repeated template text add noise. Store or generate a clean text version when the source content contains heavy markup.

How can I search WordPress posts?

The same principles apply, but avoid altering WordPress core tables casually. A WordPress plugin can use the query APIs, maintain its own search index table, or integrate with an established search plugin. Measure the existing search first before replacing it.

Will Levenshtein correct every spelling mistake?

No. It measures edit distance, not meaning or pronunciation. Restrict suggestions to a curated vocabulary and a small maximum distance. Do not automatically redirect every misspelling.

Should the search query be stored?

Aggregate query logs are valuable for discovering missing content and poor results. Store only what is necessary, avoid unnecessary personal identifiers, limit access and define a retention period.

Can this work for Malay-language content?

It can search Malay words stored with utf8mb4, but relevance, stopwords, prefixes and synonyms should be tested using real Malay queries. A dedicated engine may be better if the site needs language-aware stemming, typo tolerance or mixed-language tokenisation.

Does a higher relevance score mean the result is correct?

No. It means the row scored higher under the current formula. Search quality must be checked with expected-result tests and real visitor behaviour.

---

Final Summary

A smart website search does not need to begin with a large search cluster or an AI model.

The simplified PHP approach is:

1. Store clean searchable content in MySQL.
2. Add full-text indexes for the title and complete document.
3. Normalise and limit every visitor query.
4. Build a controlled Boolean prefix expression.
5. Use MySQL to filter and score the candidates.
6. Give title and exact-query matches extra weight.
7. Add filters, pagination, synonyms and a controlled fallback.
8. Escape every displayed value and highlight matches safely.
9. Use a limited vocabulary for spelling suggestions.
10. Measure zero-result queries and improve the system using evidence.

This design is simple enough for a beginner to understand, but it includes the security and relevance rules needed for a real project. Begin with the working version, add realistic content, record expected top results and tune one ranking rule at a time.