Building Search Features

Reviewed & published by Brayan K

By the end of this lesson you'll be able to add real search to a PHP app — starting with SQL LIKE, graduating to a MySQL FULLTEXT index with relevance ranking and pagination, and knowing exactly when to reach for a dedicated search engine.

Part of the free PHP course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

What You'll Learn in This Lesson

• Search with SQL LIKE — and know why it doesn't scale
• Create a MySQL FULLTEXT index and query it with MATCH ... AGAINST
• Rank results by relevance and paginate with LIMIT/OFFSET
• Use boolean mode operators (+must, -exclude, "phrase", prefix*)
• Build a fast, index-friendly autocomplete box
• Decide when to graduate to Meilisearch, Typesense, or Elasticsearch

1️⃣ Starting Simple: SQL LIKE

The first tool everyone reaches for is SQL's LIKE. The % is a wildcard meaning "any characters here", so '%php%' matches the word php anywhere in the text. Two rules from day one: always bind the search term with a placeholder (the % wildcards go on the bound value, not the SQL), and remember LIKE matches characters, not words — so a search for cat also matches concatenate.

<?php
// The obvious first attempt at search: SQL's LIKE with wildcards.
// The % means "any characters here", so '%php%' matches php anywhere.

$pdo = new PDO('sqlite::memory:');                 // tiny throwaway DB for the demo
$pdo->exec("CREATE TABLE articles (id INTEGER, title TEXT, body TEXT)");
$pdo->exec("INSERT INTO articles VALUES
  (1, 'Getting Started with PHP', 'Learn variables and loops'),
  (2, 'PHP Security Basics',       'Stop SQL injection cold'),
  (3, 'A Guide to Python',         'No PHP here at all')");

// ALWAYS bind the search term — never glue user input straight into SQL.
$term = 'php';
$sql  = "SELECT id, title FROM articles WHERE title LIKE :q OR body LIKE :q";
$stmt = $pdo->prepare($sql);
$stmt->execute([':q' => '%' . $term . '%']);       // the % wildcards go on the VALUE

foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    echo "  #{$row['id']}  {$row['title']}\n";
}

// This WORKS — but read the section below for why it falls apart at scale:
// a leading % means the database can't use an index, so it reads EVERY row.
?>

It works, and for a small table it's fine. The catch is performance: a leading % means the value can start with anything, so the database can't use an index and has to read every row — a "full table scan". On a few thousand rows you won't notice; on millions it crawls. There's also no notion of relevance — every match is equal, with no "best result first". That's exactly what full-text search fixes.

2️⃣ MySQL FULLTEXT: MATCH ... AGAINST

A FULLTEXT index builds an inverted index — a map of each word to the list of rows that contain it — so the database jumps straight to matching rows instead of scanning everything. You query it with MATCH(columns) AGAINST(query). The default natural language mode treats the query as plain words and returns a numeric relevance score: put AGAINST(...) in the SELECT to get the score, then ORDER BY it so the best matches come first. Add LIMIT and OFFSET for pagination.

<?php
// A FULLTEXT index builds an "inverted index": word -> list of rows that
// contain it. MATCH ... AGAINST then ranks rows by RELEVANCE, fast, even
// over millions of rows. This is the SQL you run once to set it up:
//
//   ALTER TABLE articles ADD FULLTEXT INDEX ft_search (title, body);

$q    = 'php security';                             // the user's raw query
$page = 1;                                          // which page of results
$per  = 10;                                         // results per page
$off  = ($page - 1) * $per;                         // rows to skip

// NATURAL LANGUAGE MODE: plain words, automatic relevance ranking.
// AGAINST(...) in the SELECT returns a score; reuse it to ORDER BY relevance.
// LIMIT + OFFSET give you pagination. :q is BOUND, so it's injection-proof.
$sql = "SELECT id, title,
               MATCH(title, body) AGAINST(:q) AS score
        FROM   articles
        WHERE  MATCH(title, body) AGAINST(:q)
        ORDER  BY score DESC
        LIMIT  :per OFFSET :off";

$stmt = $pdo->prepare($sql);
$stmt->bindValue(':q',   $q,   PDO::PARAM_STR);
$stmt->bindValue(':per', $per, PDO::PARAM_INT);
$stmt->bindValue(':off', $off, PDO::PARAM_INT);
$stmt->execute();

foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
    printf("  %.2f  %s\n", $r['score'], $r['title']);
}
// Higher score = better match. Rows with no matching words are excluded.
?>

Notice the score column drives the ordering, and rows with no matching words are dropped entirely by the WHERE. Everything is bound — :q as a string, :per and :off as integers — so there's no way for input to break out into the SQL.

3️⃣ Boolean Mode for Power Users

Add IN BOOLEAN MODE and the query string gains operators, so users (or your advanced-search form) can be precise: +word requires a word, -word excludes it, "exact phrase" matches in order, and word* matches a prefix. One trade-off: boolean mode doesn't sort by relevance on its own — if you want ranking too, add the AGAINST(...) AS score column as well.

<?php
// BOOLEAN MODE turns on operators so users can be precise:
//   +word  this word MUST appear        -word  this word must NOT appear
//   "a b"  exact phrase                 word*  prefix (php* -> php, phpdoc)
//   >word  rank higher   <word  rank lower
//
// Here: rows that contain "php" but NOT "python".

$q   = '+php -python';
$sql = "SELECT id, title
        FROM   articles
        WHERE  MATCH(title, body) AGAINST(:q IN BOOLEAN MODE)";

$stmt = $pdo->prepare($sql);
$stmt->execute([':q' => $q]);

foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
    echo "  {$r['title']}\n";
}
// Note: boolean mode does NOT sort by relevance unless you add a score column.
// It's about filtering precisely; add AGAINST(...) AS score to also rank.
?>

4️⃣ Autocomplete (Search-as-You-Type)

Autocomplete suggests results while the user is still typing. The trick to keeping it fast is to use a trailing-only wildcard — LIKE 'ph%' — because, unlike a leading %, a prefix match can use a normal index. Always trim the input, skip empty queries, LIMIT the suggestions, and bind the value.

<?php
// Autocomplete = "search as you type". As the user types "ph", you suggest
// titles starting with it. A LIKE 'ph%' (no LEADING %) CAN use a normal index,
// so prefix search stays fast. Trim, limit, and bind the input every time.

function suggest(PDO $pdo, string $typed): array
{
    $typed = trim($typed);
    if ($typed === '') return [];                  // don't query on empty input

    $sql  = "SELECT title FROM articles
             WHERE title LIKE :p
             ORDER BY title
             LIMIT 5";                             // cap suggestions
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':p' => $typed . '%']);        // trailing % only -> index-friendly
    return $stmt->fetchAll(PDO::FETCH_COLUMN);
}

foreach (suggest($pdo, 'P') as $title) {
    echo "  {$title}\n";
}
// Frontend tip: debounce keystrokes (~300ms) so you fire one request per pause,
// not one per letter.
?>

On the frontend, debounce the keystrokes (wait ~300ms after the user stops typing) so you send one request per pause instead of one per letter — that single change is the difference between a snappy box and a hammered server.

5️⃣ Your Turn

Now you drive. Each script below is almost complete — fill in every ___ using the 👉 hint, run it, and check it against the Output panel. First, make a LIKE search safe and correct.

<?php
// 🎯 YOUR TURN — make this LIKE search safe and correct, then run it.
// The query is written; you supply the bound value and the wildcards.

$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE notes (id INTEGER, text TEXT)");
$pdo->exec("INSERT INTO notes VALUES
  (1, 'PHP is fun'), (2, 'I love coffee'), (3, 'PHP and MySQL')");

$term = 'PHP';
$stmt = $pdo->prepare("SELECT id, text FROM notes WHERE text LIKE :q");

// 1) Bind the term wrapped in % wildcards so it matches PHP ANYWHERE
$stmt->execute([':q' => ___]);     // 👉 e.g.  '%' . $term . '%'

foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
    echo "  #{$r['id']}  {$r['text']}\n";
}

// ✅ Expected output:
//    #1  PHP is fun
//    #3  PHP and MySQL
?>

Next, finish a MySQL full-text query so it ranks by relevance. (Run this one against a real MySQL/MariaDB table that has a FULLTEXT index.)

<?php
// 🎯 YOUR TURN — finish this MySQL full-text query so it ranks by relevance.
// Assume a FULLTEXT index already exists on (title, body).

$q   = 'php security';
$sql = "SELECT id, title,
               MATCH(title, body) AGAINST(:q) AS ___   -- 👉 name the score column 'score'
        FROM   articles
        WHERE  MATCH(title, body) AGAINST(:q)
        ORDER  BY ___ DESC";                            // 👉 order by that same column

$stmt = $pdo->prepare($sql);
$stmt->execute([':q' => $q]);                           // 👉 :q is bound = injection-safe

foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
    printf("  %.2f  %s\n", $r['score'], $r['title']);
}

// ✅ Expected: rows printed best-match first, e.g.
//    1.41  PHP Security Basics
//    0.62  Getting Started with PHP
?>

6️⃣ When to Graduate to a Dedicated Engine

MySQL FULLTEXT is free and already in your stack — keep using it while it's good enough (roughly up to a million rows of straightforward search). You've outgrown it when you need typo tolerance ("keybord" still finds "keyboard"), instant search-as-you-type, faceted filtering, multi-language stemming, or sub-50ms latency at scale. That's the moment for a dedicated engine: Meilisearch and Typesense are the easiest to adopt, while Elasticsearch is the most powerful but the most operational work. The integration pattern is always the same — index your rows, keep them in sync on every change, and query the engine instead of the database.

<?php
// When MySQL FULLTEXT isn't enough — you want TYPO TOLERANCE ("keybord" ->
// "keyboard"), faceted filters, or instant sub-50ms search over millions of
// docs — reach for a dedicated engine: Meilisearch, Typesense, or Elasticsearch.
//
// Meilisearch is the gentlest to adopt: one binary, a REST API, typo tolerance on
// by default.
//   1. Run it:     docker run -p 7700:7700 getmeili/meilisearch:latest
//   2. Install:    composer require meilisearch/meilisearch-php

require __DIR__ . '/vendor/autoload.php';

$client = new Meilisearch\Client('http://127.0.0.1:7700', 'masterKey');
$index  = $client->index('articles');

// Tell it which fields are searchable and which can be filtered on.
$index->updateSearchableAttributes(['title', 'body']);
$index->updateFilterableAttributes(['category']);

// Push your rows once; keep them in sync on every insert/update/delete.
$index->addDocuments([
    ['id' => 1, 'title' => 'Getting Started with PHP', 'category' => 'tutorial'],
    ['id' => 2, 'title' => 'PHP Security Basics',       'category' => 'security'],
]);

// Typo tolerance is automatic — "phpp" still finds the PHP articles.
$res = $index->search('phpp', ['filter' => 'category = "security"', 'limit' => 5]);

foreach ($res->getHits() as $hit) {
    echo "  {$hit['title']}\n";
}
echo "Total: {$res->getEstimatedTotalHits()} hit(s)\n";

/*
Same idea, other engines:
  Typesense:     composer require typesense/typesense-php
  Elasticsearch: composer require elasticsearch/elasticsearch  (most powerful,
                 most operational work — usually overkill until you outgrow the rest)
The pattern never changes: index your rows, keep them in sync, query the engine
instead of the database, render the hits.
*/

Common Errors (and the fix)

• Search is slow on a large table — you're using LIKE '%term%'. A leading % means the index can't be used, so MySQL scans every row. Add a FULLTEXT index and switch to MATCH ... AGAINST, or for prefix/autocomplete use a trailing-only 'term%'.
• SQL injection / broken queries when the search box has a quote — you concatenated the user's text into the SQL string. Never build SQL by gluing input together. Use a placeholder and bind the value: AGAINST(:q) with execute([':q' => $q]). Bind LIMIT/OFFSET as PDO::PARAM_INT.
• Results come back in a random or useless order — you forgot relevance ranking. Put MATCH(...) AGAINST(...) AS score in the SELECT and ORDER BY score DESC. Without it, "best match first" is just luck — and add LIMIT/OFFSET so you're not dumping thousands of rows on one page.
• "Can't find FULLTEXT index matching the column list" — the columns in MATCH(title, body) must exactly match the columns the FULLTEXT index was created on, in the same set. Recreate the index over the columns you actually search: ALTER TABLE articles ADD FULLTEXT(title, body);
• A search for a short or very common word returns nothing — MySQL ignores stop words (the, is, and…) and has a minimum word length (4 chars by default in InnoDB). Lower innodb_ft_min_token_size and rebuild the index, or use IN BOOLEAN MODE, which is less aggressive about stop words.

Pro Tips

• 💡 Bind everything. The search term, the page size, and the offset all come from the user — placeholders make every one of them injection-proof.
• 💡 Always paginate. Add LIMIT/OFFSET from day one; a search that returns 50,000 rows will fall over the first time it's popular.
• 💡 Stay on MySQL FULLTEXT until it hurts. It's free and already there — only add the cost and complexity of a separate engine once you genuinely need typos, facets, or instant search.

📋 Quick Reference — Search in PHP

Mini-Challenge: A Paginated Search Function

No code is filled in this time — just a brief and an outline. Write the function yourself, run it against a MySQL table with a FULLTEXT index, then check your result against the expected output in the comments. This is the write-run-check loop you'll use on every real search feature.

<?php
// 🎯 MINI-CHALLENGE: a paginated search function.
// No code is filled in — work from the steps, then run it.
//
// Write  search(PDO $pdo, string $q, int $page = 1, int $per = 5): array
// 1. Work out the OFFSET:  $off = ($page - 1) * $per
// 2. Build a query that uses MATCH(title, body) AGAINST(:q) for BOTH the
//    WHERE filter AND a 'score' column.
// 3. ORDER BY score DESC, then LIMIT :per OFFSET :off.
// 4. bindValue :q as a string, :per and :off as PDO::PARAM_INT.
// 5. Return the matching rows.
//
// Then call it:  foreach (search($pdo, 'php', 1, 5) as $r) { echo $r['title'], "\n"; }
//
// ✅ Expected: page 1 prints up to 5 titles, best match first, no SQL injection,
//    and page 2 continues where page 1 left off.

// your code here
?>

🎉 Lesson Complete!

• ✅ LIKE is the easy start, but a leading % can't use an index and has no relevance
• ✅ A FULLTEXT index + MATCH ... AGAINST gives fast, ranked search
• ✅ Natural language mode ranks automatically; boolean mode adds + - "" * operators
• ✅ AGAINST(...) AS score + ORDER BY score DESC = relevance; LIMIT/OFFSET = pagination
• ✅ Prefix LIKE 'x%' + debounce = fast autocomplete
• ✅ Bind every value to stop SQL injection; mind stop words and minimum word length
• ✅ Graduate to Meilisearch, Typesense, or Elasticsearch for typo tolerance and scale

Practice quiz

Continue this course

• Previous: Localization, Internationalisation & Multilingual Apps
• Next: Cron Jobs, Background Tasks & Scheduling Workers — Automate recurring tasks with cron, worker processes, and job schedulers
• Quick reference: PHP cheat sheet

Frequently asked questions

Why is LIKE '%term%' slow on a big table?

A leading % wildcard means the value can start with anything, so the database cannot use a normal B-tree index — it has to read and test every single row (a full table scan). On a few thousand rows you won't notice; on millions it becomes painfully slow. A trailing-only wildcard like 'term%' CAN use an index, which is why prefix/autocomplete searches stay fast. For real word-based search, switch to a FULLTEXT index with MATCH ... AGAINST.

What is the difference between NATURAL LANGUAGE MODE and BOOLEAN MODE?

NATURAL LANGUAGE MODE (the default) treats the query as plain words and automatically ranks rows by a relevance score — best for a normal search box. BOOLEAN MODE turns on operators so users can be precise: +word requires it, -word excludes it, "phrase" matches exactly, and word* matches a prefix. Boolean mode does not rank by relevance unless you also add MATCH(...) AGAINST(...) as a score column to sort on.

How do I add relevance ranking and pagination?

Put MATCH(title, body) AGAINST(:q) in the SELECT to get a numeric score per row, then ORDER BY that score DESC so the best matches come first. For pagination add LIMIT :per OFFSET :off, where :off = (page - 1) * per. Bind :per and :off as integers (PDO::PARAM_INT) and bind :q as a string — never concatenate any of them into the SQL string.

Why don't searches for words like 'the' or 'is' return anything?

MySQL full-text search ignores 'stop words' — extremely common words such as the, is, and, of — because they appear nearly everywhere and add no signal. It also has a minimum word length (4 characters by default in InnoDB via innodb_ft_min_token_size). If you must search short or common terms, lower that setting and rebuild the index, or use BOOLEAN MODE which is less aggressive about stop words.

When should I move from MySQL full-text search to a dedicated engine?

Stay on MySQL FULLTEXT while it's good enough — it's free, already in your stack, and fine up to roughly a million rows for straightforward search. Graduate to Meilisearch, Typesense, or Elasticsearch when you need typo tolerance ('keybord' finding 'keyboard'), instant search-as-you-type, faceted filtering, multi-language stemming, or sub-50ms latency at large scale. The integration pattern is always the same: index your rows, keep them in sync on every change, and query the engine instead of the database.