Using Boolean Operators for Full Text and Boolean Searches with MySQL - Using the plus operator (
Page 3 of 4 )
As you know, it's possible to refine (or filter) a specific search string by using Boolean operators, including the plus (+) and minus (-) signs. Of course, there are other characters that can be included as part of a given search value, but I'm only going to cover those two here.
Now let me show you the signatures of the two source files listed in the section that you just read, including a SQL query that implements Boolean searches.
These sample files look like this:
(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>Testing boolean searches using the plus (+)
operator</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>Testing Boolean searches using the plus (+) operator</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>
(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 FROM users WHERE MATCH
(firstname,lastname,comments) AGAINST('$searchterm' IN BOOLEAN
MODE)");
if(!$result->countRows()){
echo 'No results were found.';
}
else{
echo '<h2>Users returned are the following:</h2>';
while($row=$result->fetchRow()){
echo '<p>Name: '.$row['firstname'].' Relevance: '.$row
['relevance'].'</p>';
}
}
}
catch(Exception $e){
echo $e->getMessage();
exit();
}
?>
As you can see, the last PHP file performs a real Boolean search against the familiar "USERS" database table, by using the brand new "IN BOOLEAN MODE" statement. This process is clearly demonstrated by the following line of PHP code:
$result=$db->query("SELECT firstname FROM users WHERE MATCH
(firstname,lastname,comments) AGAINST('$searchterm' IN BOOLEAN
MODE)");
Basically I utilized the same search query that you learned in the prior two articles of the series, except that in this case I added the Boolean command listed above. Do you see how easy is to implement Boolean searches with MySQL? I bet you do!
Now, let me show you how to use the plus (+) sign to concatenate two simple search terms to return a database result set that contains both of them.
Here's the corresponding example assuming that the respective search string has been constructed by concatenating the terms "Alejandro+JavaScript" via the (+) operator:
// displays the following entering 'Alejandro+JavaScript' search
term
/*
Users returned are the following:
Name: Alejandro
Name: Susan
*/
Indeed, you'll have to agree with me that using the plus (+) sign to perform Boolean searches with MySQL is actually a simple process, which can be achieved with minor hassles.
All right, at this moment you hopefully learned how to use the previous (+) operator to return a result set that contains all the concatenated search terms. However, I'd also like to teach you how to perform the reverse procedure, that is discarding certain words from a given search string via the minus (-) sign. Sounds pretty interesting, right?
To learn how to use this brand new Boolean operator, jump ahead and read the final section of this article. I'll be there, waiting for you.
