Building a Full-Text Search with PHP and MySQL – From Design to Bold Results

Software Development
, , , ,
1

php
php1200×630 53.8 KB

I’m not a professional web developer, but I’ve been building PHP/MySQL search systems for years. You don’t need Dreamweaver (use VS Code or any modern editor). Let’s walk through building a search that queries multiple tables and bolds keywords in results.

Database Design

Start with a normalized schema. Example: a music database with tables: artists, albums, genres, songs, links. Use foreign keys (e.g., artistID in albums). Normalization keeps your DB performant.

Secure Queries with PDO

Forget mysql_*. Use PDO with prepared statements:

$pdo = new PDO('mysql:host=localhost;dbname=musicdb', $user, $pass);
$stmt = $pdo->prepare("SELECT * FROM songs WHERE title LIKE :keyword ORDER BY title");
$stmt->execute(['keyword' => '%' . $searchTerm . '%']);
$songs = $stmt->fetchAll();

Repeat for artists and albums. Combine results.

Bolding Keywords

To bold search terms in results, use preg_replace for case-insensitive matching:

function highlightKeywords($text, $keywords) {
    $words = explode(' ', $keywords);
    foreach ($words as $word) {
        $text = preg_replace('/(' . preg_quote($word, '/') . ')/i', '<b>$1</b>', $text);
    }
    return $text;
}

User Experience

  • Use GET method for form so results are bookmarkable.
  • Auto-detect which tables contain matches and display them in separate sections.
  • Track popular searches and show them as quick links.
  • Sanitize input to prevent SQL injection (PDO does that).

This approach scales, handles any database, and gives users instant feedback. Post your specific problems – I’ll help.

2

Topic Summary: Build a PHP/MySQL search with PDO, multi-table queries, and keyword highlighting. Modernize with fulltext indexes, relevance scoring, AJAX autocomplete, and consider Elasticsearch for scale.

:movie_camera: YouTube Video:

---
title: Full-Text Search with PHP and MySQL
---
flowchart TD
    A[Requirements Analysis] --> B[Database Design]
    B --> C[Create Full-Text Index]
    C --> D[Write PHP Search Query]
    D --> E[Display Results with Highlighting]
    E --> F[Optimize Performance]
    F --> G[Deploy and Monitor]
3

This thread lays a solid foundation for building a search feature with PHP and MySQL. The PDO-based approach and keyword highlighting shown are exactly what you’d need for a small to medium site. But let’s talk about scaling and modernizing.

First, while LIKE queries work, they become slow on large datasets. MySQL offers fulltext indexes (MyISAM or InnoDB) that enable faster, relevance-ranked searches. You’d switch to MATCH…AGAINST in natural language mode, which also supports boolean operators for precision. For example, SELECT *, MATCH(title, description) AGAINST(:keyword IN BOOLEAN MODE) AS relevance FROM songs ORDER BY relevance DESC gives weighted results. Combine that with your multi-table queries by using UNIONs or a search-specific view.

For even larger scale, consider Elasticsearch or Meilisearch. They index data separately and provide instant typo-tolerant searches, faceted filters, and high availability. Plugins like Laravel Scout or Symfony bundles integrate them seamlessly. But if you’re sticking with MySQL, improve performance with query caching (e.g., Redis) and pagination via keyset rather than OFFSET.

Database Search Implementation
Database Search Implementation600×350 37.1 KB

Modern UX also demands instant feedback. Implement AJAX autocomplete using input events debounced at 300ms, fetching results from a dedicated lightweight endpoint. Combine that with client-side caching (localStorage) for repeated terms. The highlighting function from the original post works well, but be mindful of XSS: escape the text first (htmlspecialchars) before highlighting keywords to avoid injection.

Finally, consider using a fulltext search engine as a service (Algolia, Typesense) for production apps – they handle scaling, relevancy tuning, and analytics out of the box. The core logic from this thread remains the same: normalize data, secure queries, and deliver results quickly. Build on these principles, and your search will serve users well.