Building Full-Text Search with PHP and MySQL

MySQL full-text indexes let applications search tokenized text using MATCH ... AGAINST.

The examples focus on the operation itself, including the important distinction: LIKE matches simple patterns; FULLTEXT ranks tokenized documents.

What It Does

Use the examples below to observe the behavior on a small dataset before extending it to application code.

Before You Start

The examples use PHP 8.2+ features where shown. Database snippets require the corresponding PDO driver, demo tables, and credentials configured outside source code.

Example 1: Basic Use

Requires a FULLTEXT index on articles(title, body); tokenization, stopwords and engine affect matching.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$term = 'database';
$stmt = $pdo->prepare("SELECT id, title, MATCH(title, body) AGAINST(:term IN NATURAL LANGUAGE MODE) AS score FROM articles WHERE MATCH(title, body) AGAINST(:term IN NATURAL LANGUAGE MODE) ORDER BY score DESC LIMIT 10");
$stmt->execute(["term" => $term]);
echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 2: Checking Behavior

Requires a FULLTEXT index on articles(title, body); tokenization, stopwords and engine affect matching.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$term = 'queue';
$stmt = $pdo->prepare("SELECT id, title, MATCH(title, body) AGAINST(:term IN NATURAL LANGUAGE MODE) AS score FROM articles WHERE MATCH(title, body) AGAINST(:term IN NATURAL LANGUAGE MODE) ORDER BY score DESC LIMIT 10");
$stmt->execute(["term" => $term]);
echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 3: Practical Example

Requires a FULLTEXT index on articles(title, body); tokenization, stopwords and engine affect matching.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$term = 'scheduler';
$stmt = $pdo->prepare("SELECT id, title, MATCH(title, body) AGAINST(:term IN NATURAL LANGUAGE MODE) AS score FROM articles WHERE MATCH(title, body) AGAINST(:term IN NATURAL LANGUAGE MODE) ORDER BY score DESC LIMIT 10");
$stmt->execute(["term" => $term]);
echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Important Behavior and Edge Cases

Token lengths, stopwords, collation and index types affect results; do not interpolate search terms into SQL.

Comparison with Related Approaches

Common Beginner Mistakes

Key Takeaways

• MySQL full-text indexes let applications search tokenized text using MATCH ... AGAINST.
• Token lengths, stopwords, collation and index types affect results; do not interpolate search terms into SQL.
• LIKE matches simple patterns; FULLTEXT ranks tokenized documents.

Use Building Full-Text Search with PHP and MySQL when its documented behavior matches the problem. Test the relevant edge cases in your own runtime before deploying it.