HTML-Market

Creating A Search Application

Home :: Web Design :: PHP

Jan 31, 2007 11:57 pm
by: Shane Goodwin

Learn how to create a site search for database-driven websites. This tutorial assumes that the information you wish to search for is contained in a MySQL database. This search application will not only locate the documents containing the text in a search, but will also order the results according to how many times the words appear in the selected documents and offer boolean search commands. This tutorial assumes a basic knowledge of PHP and MySQL.

Full-Text Index Setup

The first thing that must be done when creating our new search application is to create a full-text index on our existing database. Now you're supposed to say, "What's a full-text index?" Basically it's an index that stores all important keywords in your database into one huge index. Note that I say keywords, because all common words such as "the", "if", "and", etc. are not included; nor are any words that appear in more than half of your database categories. Once we have this full-text index we can then conduct an efficient search against this index. Here is the code to create the full-text index:

CREATE FULLTEXT INDEX index_name ON table_name(
	field1_name ,
	field2_name ,
	field3_name
);

Let me begin by explaining some of the names. The "index_name" is one that you can make up - it is simply the name of the index this code will be creating. The table_name needs to be changed to the name of the table in your database that you want to create the full-text index on. And finally, the field names are the names of the fields within the table that you want to include in the index. Note that only text fields can be included here. Upload the code to your server and run it once, then delete from the server since you don't need the code again. Now we're ready to create the actual search application.

The Search Field

The first thing we will create is the search field. I like to create objects for my pages so I will place this search field within a div that can be placed anywhere on the page. Here is the code for the search input field:

<div id="search">
<form action="search.php" method="get" name="search">
<input type="text" value="<?php echo $search; ?>" name="searchtext" id="searchtext">
<input type="submit" value="Search" name="search">
</form></div>

This is all very easy. We create a form that will send the contents of our search to a page called "search.php". The form contains a text input field to enter our search into and a submit button. Note the value of the text input field. Its value is set up as a PHP string for a reason - on our search page we want the current search text to appear in the search field so that the user does not have to rewrite the search if they choose to change a part of it. Just position the div where you want it using CSS and you're done with this part.

The Search Page

This is where the fun begins. This page will connect to our database, run a query comparing our search string to our full-index, and display the results. Nothing complicated, but a fair amount of little details. Here is the full code for this page.

<html>
<head></head>
<body>
<?php 
$search = mysql_escape_string($_GET['searchtext']);
@mysql_connect (HOST, USER_NAME, USER_PASSWORD);
@mysql_select_db (DATABASE_NAME);
$query = "SELECT *,MATCH(field1, field 2, field3) AGAINST ('$search' IN BOOLEAN MODE) AS score 
      FROM table_name WHERE MATCH(field1, field2, field3) AGAINST ('$search' IN BOOLEAN MODE)";
$result = mysql_query($query);
?>
<h1>Search Results</h1>
 <p>Search returned <?php echo mysql_num_rows($result) ?> results for <b>"<?php echo $search; ?>"</b>.</p> 
<?php
if(mysql_num_rows($result) > 0) {
	for ($i=0; $i < mysql_num_rows($result); $i++) {
		$row = mysql_fetch_array($result);
		$id = $row['id'];
		$field1 = $row['field1'];
		$field2 = $row['field2'];
?>
		<div>
		<?php echo $i + 1 ?>. 
		<a href="address.php?id=<?php echo $id; ?>">
		<?php echo $field1; ?></a></div>
		<div><?php echo $field2; ?></div>
<?php
	}
?>
	</body>
	</html>
<?php
	exit;
} else { 
?>
	<p>No results found for <b>"<?php echo $search; ?>"</b>. 
	Please refine search.</p>
	</body>
	</html>
<?php
}
?>

This script should not look to complicated too you if you are at all familiar with PHP and MySQL. I'll start by breaking down the different parts. The first part deals with retrieving the search term and connecting to your database:

<html>
<head></head>
<body>
<?php 
$search = mysql_escape_string($_GET['searchtext']);
@mysql_connect (HOST, USER_NAME, USER_PASSWORD);
@mysql_select_db (DATABASE_NAME);
$query = "SELECT *,MATCH(field1, field 2, field3) AGAINST ('$search' IN BOOLEAN MODE) AS score 
      FROM table_name WHERE MATCH(field1, field2, field3) AGAINST ('$search' IN BOOLEAN MODE)";
$result = mysql_query($query);

We first retrieve the text being searched using the PHP command "$_GET['searchtext']". We also need to convert quotes and other special characters into a MySQL friendly form using the "mysql_escape_string" command. The next step is to connect to your MySQL database. The next step contains the magic that makes this search application work. Basically, the search function searches through the full-index twice; the first time to count the number of times your search term is included in one of your database records, and the second time to order the results according to how many times the search term was found in each record.

We are selecting every field, then matching the selected fields in each record against the search term. So, for example, if your field1 was "title", field2 was "caption", and field3 was "story" then code compares each of these fields against the search term. The first time we go through this code it compares them as a "score"; this means that a temporary field is being created on each record to record how many times the search term was found in that record. Then we use the "WHERE" command to arrange the temporary score field into descending order and set the matching records equal to the variable "query". Note that comparing the "$search" result in Boolean mode simply allows the user to make use of common search engine operators. The final line here simply runs this whole query and stores it in the variable "result".

The next bit of code is:

?>
<h1>Search Results</h1>
 <p>Search returned <?php echo mysql_num_rows($result) ?> results for <b>"<?php echo $search; ?>"</b>.</p>

We first setup a title for the page and place it within "h1" tags to denote it as a title. The next part of the code returns the number of results found that match the search term. Using the MySQL command "mysql_num_rows" we can figure out how many results were returned and display it for the user.

The next several lines set up the HTML if results are returned:

<?php
if(mysql_num_rows($result) > 0) {
	for ($i=0; $i < mysql_num_rows($result); $i++) {
		$row = mysql_fetch_array($result);
		$id = $row['id'];
		$field1 = $row['field1'];
		$field2 = $row['field2'];
?>
		<div>
		<?php echo $i + 1 ?>. 
		<a href="address.php?id=<?php echo $id; ?>">
		<?php echo $field1; ?></a></div>
		<div><?php echo $field2; ?></div>
<?php
	}
?>
	</body>
	</html>

In our search application, we want to have one of two possible pages to appear; one if results are returned matching the search term and one if no results are returned. We can easily do this by utilizing the "mysql_num_rows" command again. So, if there is at least one result returned, we'll go ahead and loop through them to write them into an HTML file section. Using the "for" command we can write a bit of HTML for each of the results returned. We use the "mysql_fetch_array" command to store each result into a different variable called "row". We also setup variables for the different fields we want displayed in the search results page.

Then we indicate to server that we are beginning HTML code using the "?>" command. This HTML code will be written containing the text of each of the results. I have placed each search result into a separate "div" tag for simplicity. I also wanted the results to be numbered so I did that using the PHP code "echo $i + 1". Note that "$i" was previously created in our "for" command to keep track of each result in the array. The rest of this code simply writes the different fields I want displayed on the page to the browser. I then end the "for" command using "}" and include the closing "body" and "html" tags.

The rest of the code sets up the page to be displayed when no results are returned from a search:

<?php
	exit;
} else { 
?>
	<p>No results found for <b>"<?php echo $search; ?>"</b>. Please refine search.</p>
	</body>
	</html>
<?php
}
?>

The "else" command here continues the first command from earlier that determined whether the results returned were none or at least one. All that is done here is a simple HTML line that lets the user no that no results were found for their search term. You can obviously change this message to something more elaborate if you like, but that is the gist of this section of code. Then be sure to close the "body" and "html" tags again since this will be a separate file from the page with results. And that is pretty much it.

The pages that result from this page will be extremely basic in their design. It would be a good idea to integrate this code with the design of your page using some "include" commands or some CSS and HTML formatting. However, the structure of this search application is fairly powerful considering the small amount of code used to create it. A user can type in multiple search terms at once and get fairly intelligent results since they are ordered by the number of times the results appear. Users can also use search engine operators to enhance the results displayed to them if they choose.