Home arrow PHP arrow Page 3 - Performing Full-text and Boolean Searches with MySQL

Using full-text searches with MySQL - PHP

When a database-driven web site grows past a certain size, it requires an internal search engine. If it is a very big site, it may be desirable for visitors to be able to use full-text searches and Boolean operators to find the information they need. This article, the first of a three-part series, explains why and shows you how to work with full-text and Boolean searches using MySQL and PHP 5.

TABLE OF CONTENTS:
  1. Performing Full-text and Boolean Searches with MySQL
  2. Running SELECT queries using a common approach
  3. Using full-text searches with MySQL
  4. Building the Web Form
By: Alejandro Gervasio
Rating: starstarstarstarstar / 6
June 06, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

As I stated in the section that you just read, MySQL supports the use of full-text searches. This can really help speed up the execution of complex queries. But let me give you a brief description of the main features of full-text searches, so you can understand more easily how they work.

In crude terms, to take advantage of full-text searches with MySQL, the database tables used by a specific application must define one or more indexes. These indexes are tied to certain tables' fields, which means the tables in question are structured slightly differently from the conventional way.

Also, full-text searches are considerably faster than traditional searches. This makes them ideal for use with complex and large queries, and allows them to return an additional search relevance value, which will be discussed in detail in further examples.

And finally, full-text searches present a useful feature known popularly as "noisy word removal." This means that any words included in a given search string that have three characters or less will be automatically discarded, in this way accelerating the execution speed of a specific query.

So, this is a brief summary of the most relevant characteristics provided by full-text searches. There are a few more you need to know about, including Boolean operators, that will be covered in the next article of the series.

But now, let me show you an example of how to build a basic MySQL-based search engine, this time using its full-text search capabilities. The first step of this development process is based upon defining the structure of the sample database table that I plan to use here.

In this case, the pertinent sample database table will be called "USERS," and will be created as indicated below:

CREATE TABLE users
(
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
  firstname VARCHAR(64),
  lastname VARCHAR(64),
  email VARCHAR(64)
  comments TEXT  
  FULLTEXT(firstname,lastname,comments)
);

As you can see, the above "USERS" database table has been created by defining some basic fields on it, but undeniably its most important characteristic resides on the specification of the respective "firstname," "lastname" and "comments" fields as full-text indexes via the corresponding "FULL TEXT" command.

Now, having at our disposal this useful table, it's possible to built a simple search engine that uses MySQL's full-text capabilities, but first let me populate the prior table with some primitive records, like the ones below:

("users" database table)

Id firstname  lastname           email                              comments

1  Alejandro  Gervasio  alejandro@domain.com  MySQL is great for building a
search engine
2  John          Williams   john@domain.com          PHP is a server side scripting
language
3  Susan       Norton      susan@domain.com       JavaScript is good to manipulate
documents
4  Julie          Wilson      julie@domain.com          MySQL is the best open source
database server



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: