how to fetch data from database in dropdownlist using ajax

how to fetch data from database in dropdownlist using ajax

How to fetch data from database in dropdownlist using ajax

It is very often now a days that you need to populate dropdown using ajax it can also do it with server side languages like PHP, Java, ASP.net etc but you will have to submit every time on selection.

By this way you can populate the drop down but this is very frustrating to refreshing for page every time. Here AJAX comes in the role and with it you can retrieve data from database using JQuery in PHP without refreshing the page.

AJAX with jQuery that loads new data and removes the old data on every selection.

In the demonstration, I am creating a Department drop-down list, and based on the option selection show all existing users of that department on another Dropdown.

Contents

  1. Table structure
  2. Configuration
  3. HTML
  4. PHP
  5. AJAX
  6. Conclusion

1. Table structure

Here i am using 2 tables in the example –

department Table –

CREATE TABLE `department` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`depart_name` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

user Table –
CREATE TABLE `users` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`username` varchar(80) NOT NULL,
`name` varchar(80) NOT NULL,
`email` varchar(80) NOT NULL,
`department` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

To make a connection with database you will need a config file and save it as config.php

Complete Code

<?php
$host = “localhost”; /* Host name */
$user = “root”; /* User */
$password = “”; /* Password */
$dbname = “tutorial”; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);

if (!$con) {
die(“Connection failed: ” . mysqli_connect_error());
}

3. HTML

Creating Dropdown elements for users and department each–

  • Fetch records from department table and use to add <option> in <select id=’depart’>  and
  • Another dropdown will show the users names which are populate dropdown using ajax based on the department name selection from the first dropdown element.

Complete Code

<?php
include “config.php”;
?>

<div>Departments </div>
<select id=”depart”>
<option value=”0″>- Select -</option>
<?php

$sql_department = “SELECT * FROM department”;
$department_data = mysqli_query($con,$sql_department);
while($row = mysqli_fetch_assoc($department_data) ){
$departid = $row[‘id’];
$depart_name = $row[‘depart_name’];
echo “<option value='”.$departid.”‘ >”.$depart_name.”</option>”;
}
?>
</select>
<div>Users </div>
<select id=”user”>
<option value=”0″>- Select -</option>
</select>

4. PHP(Server Side)

Now create a new users.php file where users will be fetched on the base of department selected.

Initialize $arr_users Array with userid and name.

Return $arr_users Array in JSON format.

Complete Code

include “config.php”;
$departid = $_POST[‘depart’];   // department id
$sql = “SELECT id,name FROM users WHERE department=”.$departid;
$result = mysqli_query($con,$sql);
$users_arr = array();
while( $row = mysqli_fetch_array($result) ){
$userid = $row[‘id’];
$name = $row[‘name’];
$arr_users[] = array(“id” => $userid, “name” => $name);
}
echo json_encode($arr_users);

5. jQuery

Now the final part, sending AJAX request when an option selected from the department. Pass the selected option value as data and on successfully callback fill <select id=’user’> with response.

Complete Code

$(document).ready(function(){
$(“#depart”).change(function(){
var deptid = $(this).val();
$.ajax({
url: ‘users.php’,
type: ‘post’,
data: {depart:deptid},
dataType: ‘json’,
success:function(response){
var len = response.length;
$(“#user”).empty();
for( var i = 0; i<len; i++){
var id = response[i][‘id’];
var name = response[i][‘name’];
$(“#user”).append(“<option value='”+id+”‘>”+name+”</option>”);
}
}
});
});
});

6. Conclusion

In this example, I used drop down to fetch data from database and populate dropdown using AJAX you can do the same for other elements as well and with different event. For example in this tutorial fetch data from database in PHP and display in HTML dropdown with CHANGE event you can use same way for blur or other events.

If you found this tutorial helpful then don’t forget to share.

Leave a Reply