Select Dropdown Form to Search through mySQL Database Table Using PHP
This post will show you how to create a sample form
In this Sample Form there is a Drop down Option , that enable the user select different ” Company name”.
When the User click submit , the browser will output all the personnel working in this selected company from the MySQL Database table.
(1) 2 x PHP file will be created “infoSelect” “infoList ”
(2) First PHP File-> infoSelect-> This PHP file will connect the Database, -> The User will Select the Company he/ she want to search -> When User Click Submit it will be redirected to infoList.php
(3) Second PHP File->infoList -> This PHP File will connect the Database ->Display the Company Search Result.
Prior to that you need to complete the items listed Below
- Mamp or xampp with phpMyAdmin install into your Computer , check out here
- A Sample Database created Check out Here
- New User Created . Check out Here
- A New table created , check out Here
- Insert Data into the Table Here
The “infoSelect ” PHP File
<?php
/*
* File: infoSelect.php
*
*
*=====================================
*/
$hostName ="localhost";
$userName ="jane";
$userPassword ="jane";
$database ="janedb";
$comArray = array();
$dbConnectionStatus = new mysqli($hostName, $userName, $userPassword,$database);
//Connection Error
if ($dbConnectionStatus->connect_error){
die("Connection failed: " . $dbConnectionStatus->connect_error);
}
// Connected to Database JaneDB
// Object oriented -> pointing
if($dbConnectionStatus->query("SELECT DATABASE()")){
$dbSuccess =true;
//
$result = $dbConnectionStatus->query("SELECT DATABASE()");
$row = $result->fetch_row();
printf("Default database is %s.\n", $row[0]);
$result->close();
}
if ($dbSuccess) {
$selectData = " SELECT * FROM testtable";
// Send Select Query
$selectData_Query = mysqli_query($dbConnectionStatus,$selectData );
echo '<form action="infoList.php" method="post">';
echo '<select name="infoID">';
echo '<option value="0" label="" selected="selected">..Select Company..</option>';
if (mysqli_num_rows($selectData_Query)>0){
// while there is still data in row
// assign selected mySQL data into 'arrayData' array
while($rows=mysqli_fetch_assoc($selectData_Query)){
// Before insert Check whether the value inside Array
$ID = $rows['id'];
$companyName =$rows['companyname'];
$firstName =$rows['firstname'];
// Loop Through array and Check
// If the Value is not inside the Array
if(! in_array($companyName,$comArray)){
array_push($comArray,$companyName);
echo '<option value="'. $companyName.'">'.$companyName.'</option>';
}
}
//Populate the Option
}
echo '</select>';
echo '<input type="submit" />';
echo '</form>';
}
echo "<br /><hr /><br />";
echo '<a href="/">Back to LocalHost</a><br />';
print_r ($comArray);
?>
The “infoList” PHP File
<style>
table, th, td {
border: 1px solid black;
}
</style>
<?php
/* Declare User connecting Credential*/
$hostName ="localhost";
$userName ="jane";
$userPassword ="jane";
$database ="janedb";
/* return connection status through $dbConnectionStatus Boolean Variable*/
/* using mysqli Object*/
$dbConnectionStatus = new mysqli($hostName, $userName, $userPassword,$database);
/* Check whether the User Succesfully connect to localhost and mydata Database*/
/* -> Access the instance "$dbConnectionStatus" variable property "connect_error " boolean */
if ($dbConnectionStatus->connect_error){
die("Connection failed: " . $dbConnectionStatus->connect_error);
}
// If Connection Status is Success
if($dbConnectionStatus->connect_error==false){
// Table Structure
echo'<table style="width:100%">';
echo'<tr>';
echo'<th>id</th>';
echo'<th>First Name</th>';
echo'<th>Last Name</th>';
echo'<th>Email</th>';
echo'<th>Company Name</th>';
echo'</tr>';
//Setup Field Name array . value and key
{ // setup ARRAY of field names
$personField = array(
'id' => 'id',
'firstname' => 'firstname',
'lastname' => 'lastname',
'email' => 'email',
'reg_date' => 'reg_date',
);
}
// get Data
$infoID = $_POST["infoID"];
// Select Query
$selectData = "SELECT * FROM testtable WHERE companyname = '$infoID' ";
// Send Select Query
$selectData_Query = mysqli_query($dbConnectionStatus,$selectData );
// Declare Array
$arrayData = array();
while($rows=mysqli_fetch_assoc($selectData_Query)){
$arrayData[] = $rows;
}
//------------------------------------------------------
// Foreach datas assign to $data
foreach($arrayData as $data){
echo'<tr>';
// Search through the array print out value if see the Key eg: 'id', 'firstname ' etc.
echo'<td>'.$data['id'].'</td>';
echo'<td>'.$data['firstname'].'</td>';
echo'<td>'.$data['lastname'].'</td>';
echo'<td>'.$data['email'].'</td>';
echo'<td>'.$data['companyname'].'</td>';
echo'</tr>';
}
echo'</table>';
}
print_r ( $arrayData);
echo "<br /><hr /><br />";
echo '<a href="infoSelect.php">Select Another Company</a>';
echo "<br /><hr /><br />";
echo '<a href="/">Back to LocalHost</a><br />';
?>
Select Dropdown Form to Search through mySQL Database Table Using PHP (One Page )
<?php
/*
* File: SelectCompanyOutputPeople
*/
$hostName ="localhost";
$userName ="jane";
$userPassword ="jane";
$database ="janedb";
$comArray = array();
$dbConnectionStatus = new mysqli($hostName, $userName, $userPassword,$database);
//Connection Error
if ($dbConnectionStatus->connect_error){
die("Connection failed: " . $dbConnectionStatus->connect_error);
}
// Connected to Database JaneDB
// Object oriented -> pointing
if($dbConnectionStatus->query("SELECT DATABASE()")){
$dbSuccess =true;
//
$result = $dbConnectionStatus->query("SELECT DATABASE()");
$row = $result->fetch_row();
printf("Default database is %s.\n", $row[0]);
$result->close();
}
// DB Connect Successful
if ($dbSuccess) {
// Check whetehr the User Select Anything
if (isset($_POST["infoID"])){
$companyName = $_POST["infoID"];
// Get the details of the company selected
$selectData = "SELECT * FROM testtable WHERE companyname = '$companyName' ";
// Send Select Query
$selectData_Query = mysqli_query($dbConnectionStatus,$selectData );
//--------------------------------------------------------------------------
// Declare Array
// Get all the User data and save that in an array
$arrayData = array();
while($rows=mysqli_fetch_assoc($selectData_Query)){
$arrayData[] = $rows;
}
//--------------------------------------------------------------------------
// Free Queries
mysqli_free_result($selectData_Query );
echo '<div style=" font-family: arial, helvetica, sans-serif; ">';
echo '<div style="margin-left: 100; ">';
echo '<table border="1" padding="5">';
echo '<tr>
<td>id</td>
<td>firstname</td>
<td>lastname</td>
<td>email</td>
<td>company</td>
</tr> ';
// Output Data Row by row
foreach($arrayData as $data){
echo'<tr>';
// Search through the array print out value if see the Key eg: 'id', 'firstname ' etc.
echo'<td>'.$data['id'].'</td>';
echo'<td>'.$data['firstname'].'</td>';
echo'<td>'.$data['lastname'].'</td>';
echo'<td>'.$data['email'].'</td>';
echo'<td>'.$data['companyname'].'</td>';
echo'</tr>';
}
echo '</table>';
echo '</div>';
echo '</div>';
// END: Output section
}
else {
$selectDataOption = " SELECT * FROM testtable";
// Send Select Query
$selectDataOption_Query = mysqli_query($dbConnectionStatus,$selectDataOption);
echo '<form action="selectCompanyOutputPeople.php" method="post">';
echo '<select name="infoID">';
echo '<option value="" label="" selected="selected">..select company..</option>';
//-------------------------------------------------------------------------------
if (mysqli_num_rows($selectDataOption_Query)>0){
// while there is still data in row
// assign selected mySQL data into 'arrayData' array
while($rowss=mysqli_fetch_assoc($selectDataOption_Query)){
// Before insert Check whether the value inside Array
$ID = $rowss['id'];
$companyNames =$rowss['companyname'];
$firstName =$rowss['firstname'];
// Loop Through array and Check
// If the Value is not inside the Array
if(! in_array($companyNames,$comArray)){
array_push($comArray,$companyNames);
echo '<option value="'. $companyNames.'">'.$companyNames.'</option>';
}
}
//Populate the Option
mysqli_free_result($selectDataOption_Query );
}
echo '</select>';
echo '<input type="submit" />';
echo '</form>';
// END: Select company from dropdowm
}
// END: if ($dbSuccess)
}
unset($companyName);
echo "<br /><hr /><br />";
echo '<a href="SelectCompanyOutputPeople.php">Select Another</a>';
echo ' ';
echo '<a href="../index.php">Quit - to homepage</a>';
?>