Developing A Site Search Engine With PHP And MySQL - Creating the database

(Page 3 of 7 )

Let's now start by creating the database for our search engine. We will pretend that we run a content driven site that focuses on articles relating to computer programming. The database will contain two tables: one for the articles, and another to hold the keywords for our search engine. Fire up the MySQL console application and enter the following code:

create database content;

use content;

create table articles

(

articleId int auto_increment,

title varchar(50) not null,

content text,

primary key(articleId),

unique id(articleId)

);

create table searchWords

(

wordId int auto_increment,

word varchar(50),

articleIds varchar(255),

primary key(wordId),

unique id(wordId)

);

Because we're concentrating on creating a search engine in this article, we won't worry about the details of the articles table too much: basically, it will hold the title and content of individual articles, with each article having a unique identifier field (articleId).

The searchWords table is what we're really interested in. Here are the details of each of its fields:

• wordId: A unique identifier which will be used to identify each word that can be searched on.
• word: In terms of our search engine, a word is any string of characters that can be searched for, such as "PHP", "MySQL", or "str_replace".
• articleIds: A string of comma-separated numerical id's which refer to the articles in which the word field is relevant. For example, if we had two records in the articles table with articleId's of 103 and 104, and they were both considered to be relevant to the keyword "MySQL", then we might have a record in the searchWords table whose word field value is "MySQL" and whose articleIds field is "103,104".

To better understand the relationship of the tables in our database, let's add a record to the articles table:

insert into articles values(0, 'MySQL is a great database', 'If you\'re after a top quality database, then you should consider MySQL. MySQL is packed with features, and can be installed on both Linux and Windows servers.');

By now you should get the drift of our search engine: The records in the articles table will be searchable (using PHP, which we will see later) using the records from the searchWords table. Execute the following INSERT command using the MySQL console application:

insert into searchWords values(0, 'MySQL', '1');

The record we've just added to the searchWords table is used to indicate that our article about "MySQL being great" (with articleId of 1) should be returned as a match if the users search keywords include "MySQL".

The contents of our article in the articles table looks like this:

"If you're after a top quality database, then you should consider MySQL. MySQL is packed with features, and can be installed on both Linux and Windows servers."

No doubt the article is about MySQL, but it also talks briefly about installing MySQL, Let's add a record to the searchWords table for the "install" keyword:

insert into searchWords values(0, 'install', '1');

Our database is now setup so that we can create a PHP script that will allow a user to search for "MySQL" or "install", and the article with articleId of 1 will be returned. Before we proceed to creating the PHP search script however, let's add one more record to the articles table:

insert into articles values(0, 'MySQL: Most popular DBMS!', 'A recent survey conducted by Company X indicated that 98% of Linux developers prefer MySQL over any other DBMS. Developers prefer MySQL because it is fast, free and also cross-platform.');

Which keywords do you think would be relevant to this article? I would say MySQL and survey. Because we already have a record in the searchWords table for MySQL, let's update that record to include our new article (which has an articleId of 2):

update searchWords set articleIds = '1,2' where word = 'MySQL';

Basically we've just modified our the searchWords table so that if a user searches for MySQL, then both articles with articleId's 1 and 2 will be returned. Lastly, we need to add a record for the "survey" keyword:

insert into searchWords values(0, 'survey', 2);

Now that our database contains some articles and search words, let's create the PHP script to actually conduct a search against the articles in our database.

Next: The searchdocs.php script >>
 

More MySQL Articles
More By Mitchell Harper

Comments On: Developing A Site Search Engine With PHP And MySQL

>>> Be the FIRST to comment on this article!