Ajax Drop Down Selection Data Load with PHP & MySQL

In this tutorial you will learn how to create dynamic drop down selection to load data with jQuery, PHP and MySQL. The dynamic drop down mostly used to display related data. In this tutorial we will implement drop down of employee to show employee details on employee name selection. The drop down selection data load handled on selection without page reload using Ajax.

The tutorial covers in very easy steps with live demo and complete demo script to download. So let’s start the coding.

Steps1: Create MySQL Database Table
For this tutorial, we have used MySQL database table "employee" to create drop down of employee name and display related details. So we will use below code to create table.
CREATE TABLE IF NOT EXISTS `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`employee_name` varchar(255) NOT NULL COMMENT 'employee name',
`employee_salary` double NOT NULL COMMENT 'employee salary',
`employee_age` int(11) NOT NULL COMMENT 'employee age',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=11 ;


Now we will import employee data using below queries
INSERT INTO `employee` (`id`, `employee_name`, `employee_salary`, `employee_age`) VALUES
(1, 'Tiger Nixon', 3208000, 61),
(2, 'Garrett Winters', 170750, 63),
(3, 'Ashton Cox', 86000, 66),
(4, 'Cedric Kelly', 433060, 22),
(5, 'Airi Satou', 162700, 33),
(6, 'Brielle Williamsons', 372000, 61),
(7, 'Herrod Chandler', 137500, 59),
(8, 'Rhona Davidson', 327900, 55),
(9, 'Colleen Hurst', 205500, 39),
(10, 'Sonya Frost', 103600, 23);



Steps2: Create MySQL Database Connection
We will create db_connect.php PHP file to make connection with MySQL database.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "phpzag_demos";
$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
?>




Steps3: Include Bootstrap, jQuery and JavaScript Files
In this tutorial,. We have created PHP file index.php and included all necessary library files (Bootstrap, jQuery, validation js) and CSS files in head tag. In this tutorial, we have created HTML using Bootstrap. The JavaScript file getData.js handle drop down change event and make Ajax request to load data.

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<script type="text/javascript" src="script/getData.js"></script>



Steps4: Create Drop Down Selection HTML
Now in index.php, we will create drop down HTML and load drop down list with employee names from MySQL database table.
<div class="page-header">
<h3>
<select id="employee">
<option value="" selected="selected">Select Employee Name</option>
<?php
$sql = "SELECT id, employee_name, employee_salary, employee_age FROM employee LIMIT 10";
$resultset = mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));
while( $rows = mysqli_fetch_assoc($resultset) ) {
?>
<option value="<?php echo $rows["id"]; ?>"><?php echo $rows["employee_name"]; ?></option>
<?php } ?>
</select>
</h3>
</div>




Steps5: Drop Down Selection Data Load with jQuery Ajax
Now in getData.js JavaScript file, we will handle drop down selection change event to get selected value and make Ajax request to server getEmployee.php to get selected employee details from MySQL database table employee. The Ajax request gets response employee data in JSON format from server. We will display that response JSON data with jQuery.

$(document).ready(function(){
// code to get all records from table via select box
$("#employee").change(function() {
var id = $(this).find(":selected").val();
var dataString = 'empid='+ id;
$.ajax({
url: 'getEmployee.php',
dataType: "json",
data: dataString,
cache: false,
success: function(employeeData) {
if(employeeData) {
$("#heading").show();
$("#no_records").hide();
$("#emp_name").text(employeeData.employee_name);
$("#emp_age").text(employeeData.employee_age);
$("#emp_salary").text(employeeData.employee_salary);
$("#records").show();
} else {
$("#heading").hide();
$("#records").hide();
$("#no_records").show();
}
}
});
})
});



Steps6: Get Data from MySQL Database

Now finally in getEmployee.php, we will get employee details from MySQL database table and return data as JSON using json_encode.

<?php
include_once("db_connect.php");
if($_REQUEST['empid']) {
$sql = "SELECT id, employee_name, employee_salary, employee_age FROM employee WHERE id='".$_REQUEST['empid']."'";
$resultset = mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));
$data = array();
while( $rows = mysqli_fetch_assoc($resultset) ) {
$data = $rows;
}
echo json_encode($data);
} else {
echo 0;
}
?>


You can view the live demo from the Demo link and can download the script from the Download link below.
Demo [sociallocker]Download[/sociallocker]