Home arrow MySQL arrow Page 3 - Developing A Site Search Engine With PHP And MySQL
MYSQL

Developing A Site Search Engine With PHP And MySQL


The key to developing a successful web site is content. Sure, your site can be packed with 10,000 50-page articles, but what good are these articles if your visitors can't find what they're after? In this article Mitchell shows us how to create a site search engine with PHP and MySQL. He develops the MySQL database and PHP script that will allow us to enter several keywords to search dynamic content.

Author Info:
By: Mitchell Harper
Rating: 4 stars4 stars4 stars4 stars4 stars / 145
February 16, 2002
TABLE OF CONTENTS:
  1. · Developing A Site Search Engine With PHP And MySQL
  2. · Search Engine Theory
  3. · Creating the database
  4. · The searchdocs.php script
  5. · The doSearch function
  6. · Testing our search script
  7. · Conclusion

print this article
SEARCH DEVARTICLES

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.
blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials