Having a search feature on your site is very handy for helping users find exactly what they are looking for. This tutorial shows how to configure your database for searching and instigate the system into your site.

Simple Site Search

Creating the Database

Our search engine tutorial assumes that all the data you want to be searchable is stored in your MySQL database. It will not have any fancy algorithms - just a simple LIKE query, but it will work for basic searching and give you a jumping off point to make a more complex searching system.

Before we can begin we need a database. The code below will create a testing database to use as you work through the tutorial.

CREATE TABLE users (fname VARCHAR(30), lname VARCHAR(30), info BLOB);

INSERT INTO users VALUES ( "Jim", "Jones", "In his spare time Jim enjoys biking, eating pizza, and classical music" ), ( "Peggy", "Smith", "Peggy is a water sports enthusiast who also enjoys making soap and selling cheese" ),( "Maggie", "Martin", "Maggie loves to cook itallian food including spagetti and pizza" ),( "Tex", "Moncom", "Tex is the owner and operator of The Pizza Palace, a local hang out joint" )

The Search Form

<h2>Search</h2>
<form name="search" method="post" action="<?=$PHP_SELF?>">
Seach for: <input type="text" name="find" /> in
<Select NAME="field">
<Option VALUE="fname">First Name</option>
<Option VALUE="lname">Last Name</option>
<Option VALUE="info">Profile</option>
</Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>

The above HTML code creates the form your users will use to search. It provides a space to enter what they are looking for, and a drop down menu where they can choose what field they are searching (first name, last name or profile.) The form sends the data back to itself using the PHP_SELF () function. This code does not go inside the <?php and ?> tags, but rather above or below them.

The Search Code

<?
//This is only displayed if they have submitted the form
if ($searching =="yes")
{
echo "<h2>Results</h2><p>";

//If they did not enter a search term we give them an error
if ($find == "")
{
echo "<p>You forgot to enter a search term";
exit;
}

// Otherwise we connect to our Database
mysql_connect("mysql.yourhost.com", "user_name", "password") or die(mysql_error());
mysql_select_db("database_name") or die(mysql_error());

// We preform a bit of filtering
$find = strtoupper($find);
$find = strip_tags($find);
$find = trim ($find);

//Now we search for our search term, in the field the user specified
$data = mysql_query("SELECT * FROM users WHERE upper($field) LIKE'%$find%'");

//And we display the results
while($result = mysql_fetch_array( $data ))
{
echo $result['fname'];
echo " ";
echo $result['lname'];
echo "<br>";
echo $result['info'];
echo "<br>";
echo "<br>";
}

//This counts the number or results - and if there wasn't any it gives them a little message explaining that
$anymatches=mysql_num_rows($data);
if ($anymatches == 0)
{
echo "Sorry, but we can not find an entry to match your query<br><br>";
}

//And we remind them what they searched for
echo "<b>Searched For:</b> " .$find;
}
?>

This is the code that actually does the searching, and can be placed either above or below the HTML form in the file depending on your preference. Although it is explained in the comments, we will explain more about how this works on the next pages.

Breaking the PHP Code Down - Part 1

if ($searching =="yes")

if ($find == "")

After this check we connect to our database, but before we can search we need to filter.

$find = strtoupper($find)

$find = strip_tags($find)

$find = trim ($find)

And this takes out all the whitespace - for example if they accidently put a few spaces at the end of their query.

Breaking the PHP Code Down - Part 2

$data = mysql_query("SELECT * FROM users WHERE upper($field) LIKE'%$find%'")

while($result = mysql_fetch_array( $data ))

$anymatches=mysql_num_rows($data);
if ($anymatches == 0)

$anymatches=mysql_num_rows($data)