When working with large datasets, auto-populating dropdown lists is a common requirement in web development.
With the help of jQuery AJAX, it’s possible to dynamically fill dropdown lists with data from a server in response to user input.
A common use case of this feature is when selecting a country, state, and city. According to the country selection related state list populates, followed by a list of cities within the selected state.
It enables users to quickly choose their location from a list of options and can enhance the user experience by requiring less manual input.
In this tutorial, I show how you can use jQuery AJAX and PHP to autopopulate dropdown with MySQL database data.
Table of Content
- Create Tables
- Setting up the Database connection
- Create HTML Layout
- AJAX file – Return data for autopopulate
- Creating the jQuery AJAX script to load data
- Demo
- Conclusion
1. Create Tables
I am using 3 tables in the example –
countries table (Store countries) –
CREATE TABLE `countries` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` varchar(80) NOT NULL );
states table (Store states of the countries) –
CREATE TABLE `states` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` varchar(80) NOT NULL, `country_id` int(11) NOT NULL );
cities table (Store cities of the states) –
CREATE TABLE `cities` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` varchar(80) NOT NULL, `state_id` int(11) NOT NULL );
2. Setting up the Database connection
To define the database connection, create a config.php file.
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
3. Create HTML Layout
To get started, create three dropdowns using the <select> element.
The first dropdown will allow users to select their country. Fetch all records from the countries table and loop them to add each country as an <option> to the dropdown.
The second dropdown will be initially empty and will be used to select the state within the selected country. We’ll use jQuery AJAX to dynamically load the state data when a user selects a country from the first dropdown.
Similarly, the third dropdown will also be initially empty and will be used to select the city within the selected state. We’ll again use jQuery AJAX to dynamically load the city data when a user selects a state from the second dropdown.
<?php
include "config.php";
?>
<div >
<?php
// Fetch all countries
$sql = "select * from countries";
$result = mysqli_query($con,$sql);
?>
<!-- Country Dropdown -->
<div>Country </div>
<select id="sel_country">
<option value="0">- Select Country -</option>
<?php
while($row = mysqli_fetch_assoc($result)){
$country_id = $row['id'];
$country_name = $row['name'];
echo "<option value='".$country_id."' >".$country_name."</option>";
}
?>
</select>
<br>
<!-- State Dropdown -->
<div>State </div>
<select id="sel_state">
<option value="0">- Select State -</option>
</select>
<br>
<!-- City Dropdown -->
<div>City </div>
<select id="sel_city">
<option value="0">- Select City -</option>
</select>
</div>
4. AJAX file – Return data for autopopulate
To handle AJAX requests, create a separate file called ajaxfile.php. This file handles two types of requests –
- $request == ‘getCountryStates’ (Return country state list)
To obtain the user-selected country ID, read the POST data. Then, we’ll fetch the records from the states table where the country_id matches the selected country.
Loop through the fetched records and store each state’s ID in the $response['id'] variable and its name in $response['name'] variable. Finally, return the $response array in JSON format.
- $request == ‘getStateCities’ (Return state city list)
Similarly, read the POST data to get the state ID that was selected by the user. Then, fetch the records from the cities table where the state_id matches the selected state.
Loop through the fetched records and store each city’s ID in the $response['id'] variable and its name in $response['name'] variable. Finally, we’ll return the $response array in JSON format.
<?php
include "config.php";
$request = '';
if(isset($_POST['request'])){
$request = $_POST['request'];
}
// Fetch state list by country_id
if($request == 'getCountryStates'){
$country_id = 0;
if(isset($_POST['country_id']) && is_numeric($_POST['country_id'])){
$country_id = $_POST['country_id'];
}
$sql = "select * from states where country_id=".$country_id;
$stateData = mysqli_query($con,$sql);
$response = array();
while($state = mysqli_fetch_assoc($stateData)){
$response[] = array(
"id" => $state['id'],
"name" => $state['name']
);
}
echo json_encode($response);
exit;
}
// Fetch city list by state_id
if($request == 'getStateCities'){
$state_id = 0;
if(isset($_POST['state_id']) && is_numeric($_POST['state_id'])){
$state_id = $_POST['state_id'];
}
$sql = "select * from cities where state_id=".$state_id;
$cityData = mysqli_query($con,$sql);
$response = array();
while($city = mysqli_fetch_assoc($cityData)){
$response[] = array(
"id" => $city['id'],
"name" => $city['name']
);
}
echo json_encode($response);
exit;
}
5. Creating the jQuery AJAX script to load data
Define change event on <select id='sel_country'> and <select id='sel_state'> element.
- Country selection change –
When the country selection changes, read the selected option value and assign it to the country_id variable. Then empty the state and city dropdowns.
Next, send an AJAX POST request to ajaxfile.php and pass {request:'getCountryStates',country_id:country_id} as the data. Set the dataType to 'json'. On successful callback, loop through the response data and create new <option> elements for the state dropdown.
- State selection change –
Similarly, when the state selection changes, read the selected option value and assign it to the state_id variable. Then empty the city dropdown.
Next, send an AJAX POST request to ajaxfile.php and pass {request:'getStateCities',state_id:state_id} as the data. Set the dataType to 'json'. On successful callback, loop through the response data and create new <option> elements for the city dropdown.
$(document).ready(function(){
// Country change
$("#sel_country").change(function(){
// Selected country id
var country_id = $(this).val();
// Empty state and city dropdown
$('#sel_state').find('option').not(':first').remove();
$('#sel_city').find('option').not(':first').remove();
// Fetch country states
$.ajax({
url: 'ajaxfile.php',
type: 'post',
data: {request:'getCountryStates',country_id:country_id},
dataType: 'json',
success:function(response){
var len = response.length;
// Add data to state dropdown
for( var i = 0; i<len; i++){
var state_id = response[i]['id'];
var state_name = response[i]['name'];
$("#sel_state").append("<option value='"+ state_id +"' >"+ state_name +"</option>");
}
}
});
});
// State change
$('#sel_state').change(function(){
// Selected state id
var state_id = $(this).val();
// Empty city dropdown
$('#sel_city').find('option').not(':first').remove();
// Fetch state cities
$.ajax({
url: 'ajaxfile.php',
type: 'post',
data: {request:'getStateCities',state_id:state_id},
dataType: 'json',
success:function(response){
var len = response.length;
// Add data to city dropdown
for( var i = 0; i<len; i++){
var city_id = response[i]['id'];
var city_name = response[i]['name'];
$("#sel_city").append("<option value='"+ city_id +"' >"+ city_name +"</option>");
}
}
});
});
});
6. Demo
7. Conclusion
By utilizing the power of jQuery AJAX, you can make server-side calls to retrieve relevant data from the database and populate dropdowns on the client side in real time. This allows users to select the appropriate data quickly and easily without the need for manual input or page refreshes.
Dynamic-dependent dropdowns are a crucial feature in many web applications, and by mastering this technique, you’ll be able to improve your users’ experience and create more efficient and user-friendly forms. So go ahead, give it a try, and start implementing dynamic-dependent dropdowns in your web application project!
You can also view this tutorial to learn how to auto-populate dropdown with PDO and PHP.
If you found this tutorial helpful then don't forget to share.