With Autopopulating dropdowns user is restricted to select an option from the dropdown step by step. Based on the dropdown selection next dropdown is autofill with options.
A common example, of this, is country, state, and city dropdowns on a form.
To load data without page refresh need to use jQuery AJAX.
In this tutorial, I show how you can auto-populate the dropdown with PDO and PHP.
Contents
1. Table structure
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 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
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` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
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` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2. Configuration
Create a config.php for a database connection.
Completed Code
<?php
$server = "localhost";
$username = "root";
$password = "";
$dbname = "tutorial";
// Create connection
try{
$conn = new PDO("mysql:host=$server;dbname=$dbname","$username","$password");
$conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
die('Unable to connect with the database');
}
3. HTML & PHP
Create 3 <select> elements.
Populate 1st <select > element with countries list and set empty the other 2 <select > elements.
The 2nd <select > is populated with state names based on a country selection from the 1st <select > element using jQuery AJAX.
Similarly, the 3rd <select > is populated with city names based on the state selection from the 2nd <select> element using jQuery AJAX.
Completed Code
<?php include "config.php"; ?>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<table>
<tr>
<td>Country</td>
<td>
<!-- Country dropdown -->
<select id='sel_country' >
<option value='0' >Select Country</option>
<?php
## Fetch countries
$stmt = $conn->prepare("SELECT * FROM countries ORDER BY name");
$stmt->execute();
$countriesList = $stmt->fetchAll();
foreach($countriesList as $country){
echo "<option value='".$country['id']."'>".$country['name']."</option>";
}
?>
</select>
</td>
</tr>
<tr>
<td>State</td>
<td>
<select id='sel_state' >
<option value='0' >Select State</option>
</select>
</td>
</tr>
<tr>
<td>City</td>
<td>
<select id='sel_city' >
<option value='0' >Select City</option>
</select>
</td>
</tr>
</table>
4. jQuery
Define change event on the <select id='sel_country'> and <select id='sel_state'>.
Country selection –
- Read the selected value. Empty the state and city dropdown and send AJAX request to
'ajaxfile.php'and pass{ request: 1, countryid: countryid }asdata.
On AJAX successful callback loop on the JSON response and append states list using <option > in <select id='sel_state'> element.
State selection –
- Read the selected value. Empty the city dropdown and send AJAX request to
'ajaxfile.php'and pass{ request: 2, stateid: stateid }asdata.
On AJAX successful callback loop on the JSON response and append cities list using <option > in <select id='sel_city'> element.
Completed Code
$(document).ready(function(){
// Country
$('#sel_country').change(function(){
var countryid = $(this).val();
// Empty state and city dropdown
$('#sel_state').find('option').not(':first').remove();
$('#sel_city').find('option').not(':first').remove();
// AJAX request
$.ajax({
url: 'ajaxfile.php',
type: 'post',
data: {request: 1, countryid: countryid},
dataType: 'json',
success: function(response){
var len = response.length;
for( var i = 0; i<len; i++){
var id = response[i]['id'];
var name = response[i]['name'];
$("#sel_state").append("<option value='"+id+"'>"+name+"</option>");
}
}
});
});
// State
$('#sel_state').change(function(){
var stateid = $(this).val();
// Empty city dropdown
$('#sel_city').find('option').not(':first').remove();
// AJAX request
$.ajax({
url: 'ajaxfile.php',
type: 'post',
data: {request: 2, stateid: stateid},
dataType: 'json',
success: function(response){
var len = response.length;
for( var i = 0; i<len; i++){
var id = response[i]['id'];
var name = response[i]['name'];
$("#sel_city").append("<option value='"+id+"'>"+name+"</option>");
}
}
});
});
});
5. AJAX File
Create an ajaxfile.php file.
Handle 2 requests –
- If $request == 1 – Read the POST
countryid. Fetch records from thestatestable based on the country id.
Loop on the fetched records and initialize $response Array with an associative array which has id and name keys.
Return $response Array in JSON format.
- If $request == 2 – Read the POST
stateid. Fetch records from thecitiestable based on the state id.
Loop on the fetched records and initialize $response Array with an associative array which has id and name keys.
Return $response Array in JSON format.
Completed Code
<?php
include "config.php";
$request = 0;
if(isset($_POST['request'])){
$request = $_POST['request'];
}
// Fetch state list by countryid
if($request == 1){
$countryid = $_POST['countryid'];
$stmt = $conn->prepare("SELECT * FROM states WHERE country=:country ORDER BY name");
$stmt->bindValue(':country', (int)$countryid, PDO::PARAM_INT);
$stmt->execute();
$statesList = $stmt->fetchAll();
$response = array();
foreach($statesList as $state){
$response[] = array(
"id" => $state['id'],
"name" => $state['name']
);
}
echo json_encode($response);
exit;
}
// Fetch city list by stateid
if($request == 2){
$stateid = $_POST['stateid'];
$stmt = $conn->prepare("SELECT * FROM cities WHERE state=:state ORDER BY name");
$stmt->bindValue(':state', (int)$stateid, PDO::PARAM_INT);
$stmt->execute();
$statesList = $stmt->fetchAll();
$response = array();
foreach($statesList as $state){
$response[] = array(
"id" => $state['id'],
"name" => $state['name']
);
}
echo json_encode($response);
exit;
}
6. Demo
7. Conclusion
You need to bind change event on the dropdown on which selection bases you want to auto-populate another dropdown.
In the example, I auto-populate 2 dropdowns but you can similarly auto-populate single or more dropdowns if you have on a page.
If you found this tutorial helpful then don't forget to share.