Building Search Features 🔍

<?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.
?>

  #1  Getting Started with PHP
  #2  PHP Security Basics

<?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.
?>

  1.41  PHP Security Basics
  0.62  Getting Started with PHP

<?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.
?>

  Getting Started with PHP
  PHP Security Basics

<?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.
?>

  PHP Security Basics
  Getting Started with PHP

<?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
?>

  #1  PHP is fun
  #3  PHP and MySQL

<?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
?>

  1.41  PHP Security Basics
  0.62  Getting Started with PHP

<?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.
*/