Site Search Engine - PHP & AJAX

Database Setup with FULLTEXT Index

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT INDEX ft_search (title, body)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

PHP Search Endpoint

<?php
// search.php
header('Content-Type: application/json');

$pdo = new PDO('mysql:host=localhost;dbname=app', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

$query = trim($_GET['q'] ?? '');
$page = max(1, (int) ($_GET['page'] ?? 1));
$perPage = 10;
$offset = ($page - 1) * $perPage;

if (strlen($query) < 2) {
    echo json_encode(['results' => [], 'total' => 0]);
    exit;
}

// FULLTEXT search with relevance scoring
$stmt = $pdo->prepare("
    SELECT id, title, body, category,
           MATCH(title, body) AGAINST(? IN BOOLEAN MODE) AS relevance
    FROM articles
    WHERE MATCH(title, body) AGAINST(? IN BOOLEAN MODE)
    ORDER BY relevance DESC
    LIMIT ? OFFSET ?
");
$stmt->execute([$query, $query, $perPage, $offset]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Get total count
$countStmt = $pdo->prepare("
    SELECT COUNT(*) FROM articles
    WHERE MATCH(title, body) AGAINST(? IN BOOLEAN MODE)
");
$countStmt->execute([$query]);
$total = (int) $countStmt->fetchColumn();

// Generate snippets with highlighting
$safeQuery = htmlspecialchars($query, ENT_QUOTES, 'UTF-8');
foreach ($results as &$row) {
    $snippet = substr(strip_tags($row['body']), 0, 200) . '...';
    $row['snippet'] = preg_replace(
        '/(' . preg_quote($safeQuery, '/') . ')/i',
        '<mark>$1</mark>',
        htmlspecialchars($snippet, ENT_QUOTES, 'UTF-8')
    );
    $row['title'] = preg_replace(
        '/(' . preg_quote($safeQuery, '/') . ')/i',
        '<mark>$1</mark>',
        htmlspecialchars($row['title'], ENT_QUOTES, 'UTF-8')
    );
    unset($row['body']);  // Don't send full body
}

echo json_encode([
    'results' => $results,
    'total' => $total,
    'pages' => ceil($total / $perPage),
    'page' => $page,
]);

AJAX Frontend

const searchInput = document.getElementById('search');
const resultsDiv = document.getElementById('results');
let debounceTimer;

searchInput.addEventListener('input', function() {
  clearTimeout(debounceTimer);
  debounceTimer = setTimeout(() => doSearch(this.value), 300);
});

async function doSearch(query, page = 1) {
  if (query.length < 2) {
    resultsDiv.innerHTML = '';
    return;
  }

  const res = await fetch(
    `search.php?q=${encodeURIComponent(query)}&page=${page}`
  );
  const data = await res.json();

  resultsDiv.innerHTML = data.results.length
    ? data.results.map(r => `
        <div class="result">
          <h3><a href="/article/${r.id}">${r.title}</a></h3>
          <span class="category">${r.category}</span>
          <p>${r.snippet}</p>
        </div>`).join('')
    : '<p>No results found.</p>';

  // Pagination
  if (data.pages > 1) {
    let pager = '<div class="pagination">';
    for (let i = 1; i <= data.pages; i++) {
      pager += `<button onclick="doSearch('${query}',${i})"
        class="${i === data.page ? 'active' : ''}">${i}</button>`;
    }
    resultsDiv.innerHTML += pager + '</div>';
  }
}

Boolean Mode Operators

Performance Tips

For sites with 100K+ articles, consider dedicated search engines like Meilisearch, Typesense, or Elasticsearch instead of MySQL FULLTEXT. They offer faster fuzzy matching, typo tolerance, faceted filtering, and better relevance tuning. For smaller sites (under 50K records), MySQL FULLTEXT with InnoDB performs well.