PHP/SQL search form with multiple search input fields

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 />

<?

;

?>

Comments are closed.