Search Features
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.
Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.
Part of the free Php course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.
What You'll Learn in This Lesson
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 .
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 .
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.
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.
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.
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.)
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.
📋 Quick Reference — Search in PHP
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.
Practice quiz
Why does LIKE '%term%' fail to scale on a large table?
- It always returns the wrong rows
- LIKE is not valid SQL in MySQL
- A leading % means no index can be used, so it scans every row
- It only works on numeric columns
Answer: A leading % means no index can be used, so it scans every row. A leading wildcard means the value can start with anything, so the database can't use a B-tree index and must do a full table scan.
When binding a LIKE search, where do the % wildcards belong?
- :q
- %
- %
Answer: :q. The placeholder stays clean in the SQL; the % characters are wrapped around the value you bind, keeping it injection-safe.
Which MySQL construct runs a relevance-ranked full-text search?
- SEARCH(title) WITHIN(:q)
- FULLTEXT(:q) ON title
- LIKE FULLTEXT :q
- MATCH(title, body) AGAINST(:q)
Answer: MATCH(title, body) AGAINST(:q). MATCH(columns) AGAINST(query) queries a FULLTEXT index and, in natural language mode, returns a relevance score.
How do you get a relevance score column to sort by?
- Add ORDER BY RANDOM()
- Put MATCH(...) AGAINST(:q) AS score in the SELECT and ORDER BY score DESC
- Use COUNT(*) AS score
- Scores are returned automatically without a SELECT expression
Answer: Put MATCH(...) AGAINST(:q) AS score in the SELECT and ORDER BY score DESC. Placing AGAINST(...) in the SELECT yields a numeric score per row; ORDER BY that column DESC puts the best matches first.
What is the difference between NATURAL LANGUAGE MODE and BOOLEAN MODE?
- Natural language mode auto-ranks by relevance; boolean mode adds operators like +, -, "", *
- Boolean mode is faster but never ranks
- They are identical aliases
- Natural language mode only works on numbers
Answer: Natural language mode auto-ranks by relevance; boolean mode adds operators like +, -, "", *. Natural language mode (the default) ranks plain words automatically; boolean mode enables +must, -exclude, "phrase", and word* operators but doesn't rank unless you add a score column.
In BOOLEAN MODE, what does the query '+php -python' match?
- Rows containing either php or python
- Rows containing the literal text '+php -python'
- Rows that contain php but NOT python
- Rows containing python but not php
Answer: Rows that contain php but NOT python. +word requires the word and -word excludes it, so '+php -python' returns rows with php and without python.
Why is a trailing-only wildcard like LIKE 'ph%' good for autocomplete?
- It matches more rows than '%ph%'
- A prefix match can still use a normal index, so it stays fast
- It is the only syntax MySQL allows
- It automatically sorts by relevance
Answer: A prefix match can still use a normal index, so it stays fast. Unlike a leading %, a prefix search can use a B-tree index, keeping search-as-you-type queries fast.
How should LIMIT :per OFFSET :off values be bound in PDO?
- As strings with PDO::PARAM_STR
- Concatenated directly into the SQL
- They cannot be bound at all
- As integers with PDO::PARAM_INT
Answer: As integers with PDO::PARAM_INT. Bind :per and :off as integers with PDO::PARAM_INT; never concatenate pagination numbers into the SQL string.
Why might a search for a word like 'the' or 'is' return nothing?
- Short words crash the query
- MySQL ignores stop words and has a minimum word length
- LIKE can't match three-letter words
- Stop words are stored in a separate table
Answer: MySQL ignores stop words and has a minimum word length. Full-text search drops common stop words and enforces a minimum token length (4 by default in InnoDB).
When is it time to graduate to Meilisearch, Typesense, or Elasticsearch?
- As soon as you have more than 10 rows
- Whenever you use prepared statements
- When you need typo tolerance, faceting, or instant search at large scale
- Only if MySQL is unavailable
Answer: When you need typo tolerance, faceting, or instant search at large scale. Stay on MySQL FULLTEXT while it's good enough; move to a dedicated engine for typo tolerance, facets, stemming, or sub-50ms latency at scale.