1st part
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" )
2nd part
<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>
3rd part
<?
//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;
}
?>
4th part
Breaking the PHP Code Down - Part 1
if ($searching =="yes")
In our original HTML form, we had a hidden field that sets this variable to "yes" when submitted. This line checks for that. If the form has been submitted then it runs the PHP code, if not it just ignores the rest of the coding.
if ($find == "")
The next thing we check before we run the query is that they actually entered a search string. If they haven't, we prompt them to, and don't process any more of the code. If we didn't have this code, and they entered a blank result, it would simply return the entire database's contents.
After this check we connect to our database, but before we can search we need to filter.
$find = strtoupper($find)
This changes all of the characters of the search string to UPPER case. We will explain how this is useful later.
$find = strip_tags($find)
This takes out any code they may have tried to enter in to the search box.
$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.
5th part
Breaking the PHP Code Down - Part 2
$data = mysql_query("SELECT * FROM users WHERE upper($field) LIKE'%$find%'")
This code actually does the searching. We are choosing all the data from our table WHERE the the field they choose is LIKE their search string. We use upper () here to search the uppercase version of the fields. Earlier we converted our search term to uppercase as well. These two things together basically ignore case. Without this a search for "pizza" would not return a profile that had the word "Pizza" with a capitol P. We also use the '%' percentage on either side of our $find variable to indicate that we are not looking solely for that term but rather that term possibly contained in a body of text.
while($result = mysql_fetch_array( $data ))
This line and the lines below it start a loop that will cycle through and return all the data. We then choose what information to ECHO back to our user, and in what format.
$anymatches=mysql_num_rows($data);
if ($anymatches == 0)
This code counts the number of rows of results. If the number is 0, it means that no results were found. If this is the case, we let the user know that.
$anymatches=mysql_num_rows($data)
Finally, incase they forgot, we remind them of what they searched for.
