Menu

Ajax Drop Down Selection Data Load with PHP & MySQL

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]

Komentar

  1. fourth line of getData.js showing "missing strict" statement.
    Line is given below.Please help.

    $("#employee").change(function() {

    BalasHapus
  2. I have just check and its working in demo. The "use strict" is not mandatory, its optional. If you're facing this error, you can use "use strict"; just after $("#employee").change(function() { in getData.js. Thanks!

    BalasHapus
  3. how can i use emp_salary as an input value instead of div id???

    $("#emp_salary").text(employeeData.employee_salary);

    (its not working...its showing only emp_salary not the row value )



    plz help

    BalasHapus
  4. You just need to pass your input id in place of div id #emp_salary to keep response salary value in input.

    BalasHapus
  5. how to display data in texbox employee name, age ,salary

    BalasHapus
  6. Its easy! You can just use use use inputbox and set response value to inputbox. Thanks!

    BalasHapus
  7. How can i get the table? So i can add stuff to the table?

    BalasHapus
  8. You have to create your own interface to add records to table.Thanks!

    BalasHapus
  9. This script works fine if i stay on the same page.
    If i want to pass , for example, to another page with a session, is that possible because i tryed it without any results.
    Is it possible to do this with your script, because ones taken a value from your output its gone.

    BalasHapus
  10. Here in this script, the drop-down result displayed with Ajax response. If you want to show same result in another page, then I think its better to use record id with another page link to load records data from database instead of storing all data in SESSION.

    BalasHapus

Posting Komentar

Ads middle content1

Ads middle content2