Implementing a Search Engine for a Message Board Using PHP and MySQL

Everyone knows how critical a good search engine is to the success of a website, but most developers—even well-seasoned ones—don’t know how to code a search engine themselves. In this article, Larry Ullman discusses a couple of ways to add a search engine to a site, while providing plenty of real-world code.

From the author of

In Chapter 17, “Example-Message Board,” of my book PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (4th edition, Peachpit Press), I explain how to create a message board (a.k.a., a forum). Forums are fairly straightforward in terms of both the underlying database design and the functional PHP code. The complexity derives from the various features and custom qualities that make each forum special. In this article, I explain how to add a search engine to a message board, using the example in the book as the basis.

NOTE

Basic knowledge of PHP and MySQL is required to follow this article.

How, exactly, you implement a search engine for any website depends greatly upon how the database is designed. With the forum example in Chapter 17 of the book, Figure 1 presents the scheme (a variation on that scheme is used in an earlier chapter). This particular design is made somewhat more complicated by the fact that the site is designed to be multilingual. Not only is there a forum for each language, but all of the navigation elements are also presented in the user’s chosen language. Therefore, the words table isn’t germane to this discussion; the fundamental contentthe forum postsis stored in one table: posts, as shown in Figure 1.

Creating a Most Basic Search

When the content to be searched is stored in a single table, and that table uses the MyISAM storage engine, you can perform FULLTEXT searches. To do so, create a FULLTEXT index on the column or columns to be searched:

ALTER TABLE posts ADD FULLTEXT (message);

NOTE

FULLTEXT searches are discussed in Chapter 7, “Advanced SQL and MySQL,” of the book.

With the index created, you can run SELECT queries using the MATCH...AGAINST syntax:

SELECT * FROM posts WHERE MATCH (message) AGAINST ('terms');

NOTE

You can also create similar functionality using a LIKE clause, but that should be avoided, as LIKE searches are quite inefficient, especially with large blocks of text.

An important thing about the MATCH...AGAINST syntax is that the columns named in the MATCH parenthetical must exactly match those used when the index was established. You can take this a step further and invoke the IN BOOLEAN MODE option, as explained in the book and in the MySQL manual.

Using this query in a PHP script is quite simple: Just validate that the user provided search terms (one or more words) and make the value safe to use in a query. If you’re using prepared statements, no extra work is required; with standard queries, run the user search terms through mysqli_real_escape_string() before executing the query.

And that’s all there is to implementing a basic search. You’ll probably want to paginate the results, too. If so, pagination is explained in Chapter 10, “Common Programming Techniques.” Be aware that for the pagination to work, the search terms must also be passed from page to page (you would apply urlencode() when adding the terms to the pagination links).