Implementing a Search Engine for a Message Board Using PHP and MySQL
- Creating a Most Basic Search
- A Better Search Solution
- Creating the Tables
- Indexing the System
- Performing a Search
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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.
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.
)
Figure 1 The forum database schema.
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);
With the index created, you can run SELECT queries using the MATCH...AGAINST syntax:
SELECT * FROM posts WHERE MATCH (message) AGAINST ('terms');
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).