Friday, 13 April 2007

Develop a Code Search Engine with PHP and MySQL

Jason Gilmore has offered a simplified version of a search engine through his article. He introduces to you some PHP and MySQL features along the way.

The following sections explain the article in detail:

The Database Schema

He says, a single table is required for the engine's operation. The table, code, serves as the code repository. Each example is stored along with a suitable title and the chapter number in which it appears. Since the search engine retrieves examples based on keywords found in the example title or in the code itself, a FULLTEXT index has been added for these columns. Also, the table contents will rarely change beyond the occasional bug fix, so its backed by the read-optimized MyISAM storage engine. The table code is as follows:

CREATE TABLE code (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(50) NOT NULL,
chapter TINYINT UNSIGNED NOT NULL,
code TEXT NOT NULL,
FULLTEXT (title, code)
) TYPE = MYISAM;

Loading the Table

The downloadable zip file containing all of the book's code should be easily navigable so readers can retrieve the desired example. To meet this requirement, the zip file contains a number of directories labeled according to chapter number (1, 2, 3, ... 37), and each script is aptly named with a lowercase title and series of underscores, for example retrieving_array_keys.php. Therefore a script capable of dealing with these two organizational matters is required in order to automate the process of loading the scripts into the database.

Building the Search Engine

With the code and corresponding metadata inserted into the database, he builds the search engine. Jason has used the Symfony framework to abstract the database interaction, for the purposes of this article and have also made use of POPM (Plain Old PHP and MySQL) to build the search engine.