To see the live example of this tutorial click HERE
The following form displays search results combining more than one search term.
HTML code used for the search form:
<table width=”57%” hspace=”100px”>
<form name=”search_form” method=”POST” action=”/ashe-2016/asheteam-2016.php”>
<tr><td>Name:</td> <td><input type=”text” name=”search_box_name” value=”” /></td></tr>
<tr><td>Institution:</td><td><select name=”search_school” value=”” />
<option></option>
<option>Adelphi University</option>
<option>Albany State University</option>
<option>Albany Technical College</option>
<option>Alcorn State University</option>
<option>Appalachian State University</option>
<option>Arizona State University</option>
<option>Arkansas Tech University</option>
<option>Augustana (Ill.)</option>
<option>Austin Peay State University</option>
<option>Bates College</option>
<option>Baylor University</option>
<option>Berkeley College</option>
<option>Brooklyn College</option>
<option>Brown University</option>
<option>Caldwell University</option>
<option>California State University San Bernardino</option>
<option>Central Connecticut State University</option>
<option>Clark Atlanta University</option>
<option>Xavier University of Louisiana</option>
</td></tr>
<tr><td>Sport:</td><td><select name=”search_sport” value=”” />
<option></option>
<option>Baseball</option>
<option>Basketball</option>
<option>Bowling</option>
<option>Cheerleading</option>
<option>Crew</option>
<option>Equestrian</option>
<option>Fencing</option>
<option>Field Hockey</option>
<option>Football</option>
<option>Golf</option>
<option>Gymnastics</option>
<option>Ice Hockey</option>
<option>Lacrosse</option>
<option>Rifle</option>
<option>Sailing</option>
<option>Soccer</option>
<option>Softball</option>
<option>Squash</option>
<option>Swimming</option>
<option>Tennis</option>
<option>Track</option>
<option>Volleyball</option>
<option>Water Polo</option>
<option>Wrestling</option>
</td></tr>
<tr><td>Class:</td><td><select name=”search_class” value=”” />
<option></option>
<option>JR</option>
<option>SO</option>
<option>SR</option>
</select>
</td></tr>
<tr><td>Fall GPA:</td><td><input type=”text” name=”searchfallgpa” value=”” size=”4″ maxlength=”4″ /></td></tr>
<tr><td>Cum GPA:</td><td><input type=”text” name=”search_gpa” value=”” size=”4″ maxlength=”4″ /></td></tr>
</td></tr>
<tr>
<td>
<input type=”submit” name=”search” value=”Search”></td></tr>
</form>
</table>
<a name=”anchor”></a>
<br/>
PHP code used to perform the search:
<?php
$page = 1;
if (is_numeric($_GET[‘page’]))
{
$page = intval($_GET[‘page’]); }
function fetch_results($page,$db)
{
This section connects to SQL database to search for content within the table:
$db = mysql_connect (‘localhost’, ‘user’, ‘password’) or die (‘I cannot connect to the database because: ‘ . mysql_error());
mysql_select_db (“database_name”) or die(‘I cannot select the database because: ‘ . mysql_error());
$sql = “SELECT * FROM table_name”;
if (isset($_POST[‘search’])) {
$search_term_name = mysql_real_escape_string($_POST[‘search_box_name’]);
$search_term_school = mysql_real_escape_string($_POST[‘search_school’]);
$search_term_sport = mysql_real_escape_string($_POST[‘search_sport’]);
$search_term_class = mysql_real_escape_string($_POST[‘search_class’]);
$search_term_gpa = mysql_real_escape_string($_POST[‘search_gpa’]);
$search_term_fallgpa = mysql_real_escape_string($_POST[‘searchfallgpa’]);
$search_term_major = mysql_real_escape_string($_POST[‘search_major’]);
}
if($_GET[‘name’] != “”)
$search_term_name = $_GET[‘name’];
if($_GET[‘school’] != “”)
$search_term_school = $_GET[‘school’];
if($_GET[‘sport’] != “”)
$search_term_sport = $_GET[‘sport’];
if ($_GET[‘class’] != “”)
$search_term_class = $_GET[‘class’];
if ($_GET[‘cumgpa’] != “”)
$search_term_gpa = $_GET[‘cumgpa’];
if ($_GET[‘fallgpa’] != “”)
$search_term_fallgpa = $_GET[‘fallgpa’];
$sql .= ” WHERE”;
$terms = 0;
if($search_term_name != “”)
{
$terms++;
$sql .=” name LIKE ‘%{$search_term_name}%'”;
I used % around search term so it displays results even when there’s a partial word match.
}
if ($search_term_school != “”)
{
if($terms > 0 )
{
$sql .= ” AND”;
}
$terms++;
$sql .=” school LIKE ‘%{$search_term_school}%'”;
}
if ($search_term_sport != “”)
{
if($terms > 0)
{
$sql .= ” AND”;
}
$terms++;
$sql .=” sport LIKE ‘%{$search_term_sport}%'”;
}
if ($search_term_class != “”)
{
if ($terms > 0)
{
$sql .= ” AND”;
}
$terms++;
$sql .=” class LIKE ‘%{$search_term_class}%'”;
}
if ($search_term_fallgpa != “”)
{
if ($terms > 0)
{
$sql .= ” AND”;
}
$terms++;
$sql .=” fallgpa LIKE ‘%{$search_term_fallgpa}%'”;
}
if ($search_term_gpa != “”)
{
if ($terms > 0)
{
$sql .= ” AND”;
}
$terms++;
$sql .=” cumgpa LIKE ‘%{$search_term_gpa}%'”;
}
if($terms == 0)
{
$displayResults = 0;
echo “<i style=’margin-left: 10px’;>All Arthur Ashe Jr. Sports Scholars are searchable by name, school, sport, classification and gpa</i>”;
}
else {
$total_records = mysql_num_rows(mysql_query($sql));
This line calculates how many pages are going to be displayed depending on
the number of total search results, I set a maximum of 15 results per page:
$pages = ceil($total_records / 15);
This section handles the pagination of the search results:
echo “<p style=’font-family:Verdana, Geneva, sans-serif;
font-style:italic;
font-size:12px; margin: 10px; color:red;’;>Page “; echo $page; echo ” of “; echo $pages; “</p>”;
}
if (!(is_numeric($page)) || $page < 1)
{
$page = 1;
} //if page = 0 or null or page 1
$sql .= ” LIMIT “.(($page – 1) * 15).”,15″;
$displayResults = 1;
if($terms == 0)
{
$displayResults = 0;
}
else
{
$query = mysql_query($sql) or die (mysql_error());
}
?>
The following code displays the search results on the screen:
<div class=”search-results”>
<table width=”650px” cellpadding=”5″ cellpadding=”5″ style=”font-size:12px; font-family:Verdana, Geneva, sans-serif;
font-size:10px; bgcolor=”#E5E5E5;”>
<tr>
<td width=”22%” bgcolor=”#BFEFA3″><b>Name</b></td>
<td width=”30%” bgcolor=”#BFEFA3″><b>School</b></td>
<td width=”15%” bgcolor=”#BFEFA3″><b>Sport</b></td>
<td width=”10%” bgcolor=”#BFEFA3″><b>Class</b></td>
<td width=”10%” bgcolor=”#BFEFA3″><b>Fall GPA</b></td>
<td width=”10%” bgcolor=”#BFEFA3″><b>Cum GPA</b></td>
</tr>
<?php
$search_array = array();
if($displayResults == 0)
{
return $search_array;
}
while ($row = mysql_fetch_assoc($query))
{ array_push($search_array, $row) ?>
<tr>
<td bgcolor=”#E5E5E5″><?php echo $row [‘name’]; ?></td>
<td bgcolor=”#F9F9F9″><?php echo $row [‘school’]; ?></td>
<td bgcolor=”#F9F9F9″><?php echo $row [‘sport’]; ?></td>
<td bgcolor=”#F9F9F9″><?php echo $row [‘class’]; ?></td>
<td bgcolor=”#F9F9F9″><?php echo $row [‘fallgpa’]; ?></td>
<td bgcolor=”#F9F9F9″><?php echo $row [‘cumgpa’]; ?></td>
</tr>
<?php } return $search_array;
} ; ?>
</table></div>
<?php
if (isset($_POST[‘search’])) {
$search_term_name = $_POST[‘search_box_name’];
$search_term_school = $_POST[‘search_school’];
$search_term_sport = $_POST[‘search_sport’];
$search_term_class = $_POST[‘search_class’];
$search_term_fallgpa = $_POST[‘searchfallgpa’];
$search_term_gpa = $_POST[‘search_gpa’];
$search_term_major = $_POST[‘search_major’];
}
if($_GET[‘name’] != “”)
$search_term_name = $_GET[‘name’];
if($_GET[‘school’] != “”)
$search_term_school = $_GET[‘school’];
if ($_GET[‘sport’] != “”)
$search_term_sport = $_GET[‘sport’];
if ($_GET[‘class’] != “”)
$search_term_class = $_GET[‘class’];
if ($_GET[‘cumgpa’] != “”)
$search_term_gpa = $_GET[‘cumgpa’];
if ($_GET[‘fallgpa’] != “”)
$search_term_fallgpa = $_GET[‘fallgpa’];
$page = 1; if (is_numeric($_GET[‘page’]))
{ $page = intval($_GET[‘page’]); }
$search_array = fetch_results($page,$db);
$array_count = count($search_array);
if ($page <= 1) { ?><br/><img src=”specrep/back.png” /></a> <? }
else {
?>
This section shows search results in URL link for the “back” and “next” buttons functionality.
<a href=”/ashe-2016/asheteam-2016.php?page=<? echo ($page – 1); ?>&name=<? echo $search_term_name; ?>&school=<? echo $search_term_school; ?>&sport=<? echo $search_term_sport; ?>&class=<? echo $search_term_class; ?>&cumgpa=<? echo $search_term_gpa; ?>&fallgpa=<? echo $search_term_fallgpa; ?><? echo ‘#anchor’ ?>”><br/><img src=”specrep/back.png” /></a><? }
if ($array_count < 15) { ?><img src=”specrep/next.png” /></a><? }
else { ?><a href=”/ashe-2016/asheteam-2016.php?page=<? echo ($page + 1); ?>&name=<? echo $search_term_name; ?>&school=<? echo $search_term_school; ?>&sport=<? echo $search_term_sport; ?>&class=<? echo $search_term_class; ?>&cumgpa=<? echo $search_term_gpa; ?>&fallgpa=<? echo $search_term_fallgpa; ?><? echo ‘#anchor’ ?>”><? echo $displayResults; ?><img src=”specrep/next.png” /></a><? } ?><br />
<?
;
?>