Performing Full-text and Boolean Searches with MySQL - Building the Web Form
(Page 4 of 4 )
Now, having filled in the sample "USERS" table with the previous records, it's time to build the corresponding web form for entering search terms:
(definition of form.htm file)
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-
8859-1" />
<title>Example of simple MySQL search engine</title>
<style type="text/css">
body{
padding: 0;
margin: 0;
background: #fff;
}
h1{
font: bold 16px Arial, Helvetica, sans-serif;
color: #000;
text-align: center;
}
p{
font: bold 11px Tahoma, Arial, Helvetica, sans-serif;
color: #000;
}
#formcontainer{
width: 40%;
padding: 10px;
margin-left: auto;
margin-right: auto;
background: #6cf;
}
</style>
</head>
<body>
<h1>Example of simple MySQL search engine</h1>
<div id="formcontainer">
<form action="search.php" method="get">
<p>Enter search term here : <input type="text"
name="searchterm" title="Enter search term here" /><input
type="submit" name="search" value="Search Now!" /></p>
</form>
</div>
</body>
</html>
And finally, here is the signature of the PHP file that performs full-text searches against the "USERS" database table that you saw before:
(definition of search.php file)
<?php
// define 'MySQL' class
class MySQL{
private $conId;
private $host;
private $user;
private $password;
private $database;
private $result;
const OPTIONS=4;
public function __construct($options=array()){
if(count($options)!=self::OPTIONS){
throw new Exception('Invalid number of connection
parameters');
}
foreach($options as $parameter=>$value){
if(!$value){
throw new Exception('Invalid parameter '.$parameter);
}
$this->{$parameter}=$value;
}
$this->connectDB();
}
// connect to MySQL
private function connectDB(){
if(!$this->conId=mysql_connect($this->host,$this-
>user,$this->password)){
throw new Exception('Error connecting to the server');
}
if(!mysql_select_db($this->database,$this->conId)){
throw new Exception('Error selecting database');
}
}
// run query
public function query($query){
if(!$this->result=mysql_query($query,$this->conId)){
throw new Exception('Error performing query '.$query);
}
return new Result($this,$this->result);
}
public function escapeString($value){
return mysql_escape_string($value);
}
}
// define 'Result' class
class Result {
private $mysql;
private $result;
public function __construct(&$mysql,$result){
$this->mysql=&$mysql;
$this->result=$result;
}
// fetch row
public function fetchRow(){
return mysql_fetch_assoc($this->result);
}
// count rows
public function countRows(){
if(!$rows=mysql_num_rows($this->result)){
return false;
}
return $rows;
}
// count affected rows
public function countAffectedRows(){
if(!$rows=mysql_affected_rows($this->mysql->conId)){
throw new Exception('Error counting affected rows');
}
return $rows;
}
// get ID form last-inserted row
public function getInsertID(){
if(!$id=mysql_insert_id($this->mysql->conId)){
throw new Exception('Error getting ID');
}
return $id;
}
// seek row
public function seekRow($row=0){
if(!is_int($row)||$row<0){
throw new Exception('Invalid result set offset');
}
if(!mysql_data_seek($this->result,$row)){
throw new Exception('Error seeking data');
}
}
}
try{
// connect to MySQL
$db=new MySQL(array('host'=>'host','user'=>'user','password'=>'password',
'database'=>'database'));
$searchterm=$db->escapeString($_GET['searchterm']);
$result=$db->query("SELECT firstname, lastname from users WHERE MATCH(firstname,lastname,comments) AGAINST('$searchterm')");
if(!$result->countRows()){
echo 'No results were found.';
}
else{
echo '<h2>Articles returned are as following:</h2>';
while($row=$result->fetchRow()){
echo '<p>Title: '.$row['title'].' Author: '.$row['author'].' Description: '.$row['content'].'</p>';
}
}
}
catch(Exception $e){
echo $e->getMessage();
exit();
}
?>
As shown above, the previous PHP file includes two new SQL statements in the SELECT query, called "MATCH" and "AGAINST" to perform full-text searches against the previously defined "USERS" table. In this case, the "MATCH" command is utilized to return a relevance value, which is determined by combining the search string in question, the words contained in the indexed table fields and finally the number of rows included in the search.
All right, now that you know how to code a SELECT statement that performs a full-text search against the prior sample "USERS" database table, let me show you what happens if the search string "JavaScript" is entered in the respective web form.
The result returned by the query is shown below:
Users returned are the following:
First Name: Susan Last Name: Norton
Apparently, the database table row retrieved after performing a full-text search is very similar to using a conventional "LIKE" statement. However, this is only a first impression, since actually the query has been performed faster, due to the specification of the respective full-text indexes.
Besides, it's important to note here that the use of a "MATCH" command returns a relevance ranking, but this crucial feature will be covered in great detail in the next part of the series.
In the meantime, feel free to test all the hands-on examples shown in this article, so you can acquire a better grounding in how to implement full-text searches with MySQL. It's going to instructive, believe me!
Final thoughts
In this first tutorial of the series, I walked you through the basics of using full-text searches with MySQL. As I said previously, the subject has many other features that need to be covered in detail, such as working with relevance rankings and Boolean operators, but all these topics will be discussed in the next tutorial.
Now that you've been warned, you won't want to miss it!
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 6 
