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>Cleveland State University</option>

<option>Coker College</option>

<option>Community College of Baltimore County Essex</option>

<option>Connecticut College</option>

<option>Delaware State University</option>

<option>DePauw</option>

<option>Dillard University</option>

<option>Dominican University</option>

<option>East Central Community College</option>

<option>East Tennessee State University</option>

<option>El Paso Community College</option>

<option>Emory University</option>

<option>Ferris State University</option>

<option>Florida Atlantic University</option>

<option>Florida Gulf Coast University</option>

<option>Florida Memorial University</option>

<option>Franklin Pierce University</option>

<option>Fresno State</option>

<option>Gallaudet University</option>

<option>George Mason University</option>

<option>Georgian Court University</option>

<option>Grambling State University</option>

<option>Grand Valley State University</option>

<option>Grinnell College</option>

<option>Hampton University</option>

<option>Hendrix College</option>

<option>Hope College</option>

<option>Idiana Purdue Fort Wayne (IPFW)</option>

<option>Indiana Wesleyan University</option>

<option>Iowa State University</option>

<option>Ithaca College</option>

<option>IUPUI</option>

<option>Jackson State University</option>

<option>Jarvis Christian College</option>

<option>John Carroll University</option>

<option>Johns Hopkins</option>

<option>Johnson C. Smith University</option>

<option>Kennesaw State University</option>

<option>Kent State University</option>

<option>King University</option>

<option>La Salle University</option>

<option>La Verne</option>

<option>Lafayette College</option>

<option>Lane College</option>

<option>Lehigh University</option>

<option>Los Angeles Valley College</option>

<option>Louisiana State University</option>

<option>Lycoming College</option>

<option>Malone University</option>

<option>Marist College</option>

<option>McDaniel College</option>

<option>McKendree University</option>

<option>Menlo College</option>

<option>Mercyhurst University</option>

<option>Miami Dade College</option>

<option>Middle Tennessee State University</option>

<option>Mills College</option>

<option>Minnesota State University, Mankato</option>

<option>Missouri Baptist University</option>

<option>MIT</option>

<option>Montclair State University</option>

<option>Murray State University</option>

<option>New York University</option>

<option>Newbury College</option>

<option>NJIT</option>

<option>North Carolina Agricultural and Technical State University</option>

<option>North Carolina State University</option>

<option>Northwestern University</option>

<option>Oklahoma State University</option>

<option>Pace University</option>

<option>Pacific University (Ore.)</option>

<option>Palm Beach Atlantic University</option>

<option>Park University</option>

<option>Penn State University</option>

<option>Pepperdine University</option>

<option>Point Loma Nazarene University</option>

<option>Purchase College, SUNY</option>

<option>Purdue University</option>

<option>Queens University of Charlotte</option>

<option>Rice University</option>

<option>Rust College</option>

<option>Rutgers University</option>

<option>Saginaw Valley State University</option>

<option>Saint Augustine’s University</option>

<option>Saint Josephs University</option>

<option>Saint Mary’s University of Minnesota</option>

<option>Samford University</option>

<option>San Diego State University</option>

<option>San Jose State University</option>

<option>Santa Clara Universtiy</option>

<option>Sarah Lawrence College</option>

<option>Savannah State University</option>

<option>SC State University</option>

<option>Schreiner University</option>

<option>Seattle University</option>

<option>Shaw University</option>

<option>Shenandoah University</option>

<option>Siena College</option>

<option>Soka University of America</option>

<option>South Georgia State College</option>

<option>Southern Methodist University</option>

<option>Southern University and A&M College</option>

<option>St Thomas University</option>

<option>St. Edwards University</option>

<option>St. Johns University</option>

<option>Stevens Institute of Technology</option>

<option>Stockton University</option>

<option>Suffolk County Community College</option>

<option>Temple University</option>

<option>Tennessee State University</option>

<option>Texas Lutheran University</option>

<option>Texas Southern University</option>

<option>Texas Tech University</option>

<option>Texas Woman’s University</option>

<option>The George Washington University</option>

<option>The Ohio State University</option>

<option>The University of Alabama</option>

<option>The University of Memphis</option>

<option>The University of Texas at Austin</option>

<option>Troy University</option>

<option>Union College (NY)</option>

<option>Union County College</option>

<option>University of Alabama</option>

<option>University of Alabama at Birmingham</option>

<option>University of Arkansas at Pine Bluff</option>

<option>University of California Los Angeles</option>

<option>University of California, Berkeley</option>

<option>University of Cincinnati</option>

<option>University of Cincinnati-Clermont</option>

<option>University of Dayton</option>

<option>University of Dubuque</option>

<option>University of Florida</option>

<option>University of Houston</option>

<option>University of Illinois at Urbana-Champaign</option>

<option>University of Iowa</option>

<option>University of Kentucky</option>

<option>University of Louisville</option>

<option>University of Maryland</option>

<option>University of Maryland Eastern Shore</option>

<option>University of Maryland, Baltimore County</option>

<option>University of Memphis</option>

<option>University of Mississippi</option>

<option>University of Missouri</option>

<option>University of Mount Olive</option>

<option>University of Mount Union</option>

<option>University of Nebraska-Lincoln</option>

<option>University of Nevada</option>

<option>University of North Carolina</option>

<option>University of North Carolina at Chapel Hill</option>

<option>University of North Carolina at Charlotte</option>

<option>University of North Dakota</option>

<option>University of North Florida</option>

<option>University of North Georgia</option>

<option>University of North Texas</option>

<option>University of Oklahoma</option>

<option>University of Pittsburgh</option>

<option>University of San Diego</option>

<option>University of South Florida</option>

<option>University of Tennessee, Knoxville </option>

<option>University of the District of Columbia</option>

<option>University of the Southwest</option>

<option>University of Virginia</option>

<option>University of Washingon</option>

<option>University of West Alabama</option>

<option>University of Wisconsin-Whitewater</option>

<option>Valparaiso University</option>

<option>Virginia State Univeristy</option>

<option>Webster University</option>

<option>Wesley College</option>

<option>Wesleyan University</option>

<option>West Chester University</option>

<option>West Virginia University</option>

<option>Western New England University</option>

<option>Western State Colorado University</option>

<option>Westfield State University</option>

<option>Wilmington College</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 />

<?

;

?>

No Comments Yet.

Leave a comment