Assembling the modules of the MySQL-driven search application - MySQL

As I stated in the section that you just read, implementing this MySQL-driven search engine is a matter of fetching the search terms entered on the online web form, and then embedding them into a SELECT query, and finally performing the query in question against one or more selected databases.

Logically, the last step involved in this process is to display the corresponding results (assuming that the query was successful) on the browser. Quite simple, right?

All right, based upon this description of how the search application must work, below I listed the definition of a new PHP file. It is called "processform.php" and is tasked first with performing a search process against a sample "USERS" database table, and then showing the result to the end user.

Given that, the signature of this brand new PHP file is as follows:

(definition for "processform.php" file)

<?php

// include MySQL-processing classes

require_once 'mysql.php';

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,comments FROM
users WHERE MATCH(firstname,lastname,comments) AGAINST
('$searchterm')");

if(!$result->countRows()){

echo '<div class="maincontainer"><h2>No results were found. Go
back and try a new search.</h2></div>'."n";

}

else{

// display search results

echo '<div class="maincontainer"><h2>Your search criteria
returned '.$result->countRows().' results.</h2>'."n";

while($row=$result->fetchRow()){

echo '<div class="rowcontainer"><p><strong>First Name:
</strong>'.$row['firstname'].'<p><p><strong>Last Name:
</strong>'.$row['lastname'].'</p><p><strong>Comments:
</strong>'.$row['comments'].'</p></div>'."n";

}

}

echo '</div>';

}

catch(Exception $e){

echo $e->getMessage();

exit();

}

?>

As you can see, the above PHP file uses the two MySQL processing classes to perform the pertinent search process against the "USERS" database table. In this case, I built the search query using the "MATCH" and "AGAINST" clauses to take advantage of full-text capabilities offered by newer versions of MySQL, but you may want to use a more conventional "LIKE" statement, particularly if your search queries are rather primitive.

And finally, assuming that the aforementioned "USERS" database table has been previously populated with the following data:

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 sue@domain.com JavaScript is good to manipulate documents
4 Julie Wilson julie@domain.com MySQL is the best open source database server

Please take a look at the following screen shots. They show the database results returned by this MySQL-driven search application when the search strings "Alejandro," "Alejandro+Susan" and finally "Alejandro+Susan+John" are entered into the corresponding online web form:

As demonstrated clearly by the above images, building an expandable search application using the powerful MySQL/PHP 5 combination is indeed a no-brainer process that can be performed with minor efforts from us. Feel free to use all the source files shown here to implement this search engine on your own web site, so you can provide users with the possibility of searching the site contents.

Final thoughts

In this first part of the series, you hopefully learned how to create an expandable search application using the capabilities offered by MySQL and PHP 5. Nonetheless, this instructive journey isn't finished yet, since the application lacks important features, such as the implementation of true Boolean searches and results paging.

All of these improvements will be introduced in the next part of the series, so you don't have any excuses to miss it!