Dynamic dependent dropdown restricts the user selection based on the previous dropdown selection.
In this tutorial, I show how you can auto populate dropdown with MySQL database data using JavaScript and PHP.
Contents
1. Table structure
I am using 3 tables in the example –
countries table (Store countries records) –
CREATE TABLE countries ( id serial PRIMARY KEY, name varchar(80) NOT NULL )
states table (Store states of the countries) –
CREATE TABLE states ( id serial PRIMARY KEY, name varchar(80) NOT NULL, country_id bigint NOT NULL )
cities table (Store cities of the states) –
CREATE TABLE cities ( id serial PRIMARY KEY, name varchar(80) NOT NULL, state_id bigint NOT NULL )
2. Configuration
Create a config.php file.
Completed Code
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = "root"; /* Password */
$dbname = "tutorial"; /* Database name */
// Create connection
$con = new mysqli($host, $user, $password, $dbname);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
3. HTML
Fetch all records from countries table and create 3 <select> elements –
- First
<select >element is to display fetchedcountries. - Second is use to display states based on country selection using JavaScript AJAX, and
- Third is use to display cities based on state selection using JavaScript AJAX.
Completed Code
<?php
include "config.php";
// Fetch countries
$sql = "SELECT * from countries order by name";
$stmt = $con->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
?>
<table>
<tr>
<td>Country</td>
<td>
<select id="country" onchange="getStates(this.value);">
<option value="0" >– Select Country –</option>
<?php
while ($row = $result->fetch_assoc() ){
$id = $row['id'];
$name = $row['name'];
echo "<option value='".$id."' >".$name."</option>";
}
?>
</select>
</td>
</tr>
<tr>
<td>State</td>
<td>
<select id="state" onchange="getCities(this.value);" >
<option value="0" >– Select State –</option>
</select>
</td>
</tr>
<tr>
<td>City</td>
<td>
<select id="city" >
<option value="0" >– Select City –</option>
</select>
</td>
</tr>
</table>
4. PHP
Create ajaxfile.php file.
Handle 2 AJAX requests –
- If $request == ‘getStates’ – Fetch records from
statestable according to$country_idvalue and assign to$result. Loop on$resultand initialize$dataArray withidandnamekeys.
Return $data in JSON format.
- If $request == ‘getCities’ – Fetch records from
citiestable according to$state_idvalue and assign to$result. Loop on$resultand initialize$dataArray withidandnamekeys.
Return $data in JSON format.
Completed Code
<?php
include 'config.php';
// Read POST data
$postData = json_decode(file_get_contents("php://input"));
$request = "";
if(isset($postData->request)){
$request = $postData->request;
}
// Get states
if($request == 'getStates'){
$country_id = 0;
$result = array();$data = array();
if(isset($postData->country_id)){
$country_id = $postData->country_id;
$sql = "SELECT * from states WHERE country_id=?";
$stmt = $con->prepare($sql);
$stmt->bind_param("i", $country_id);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()){
$id = $row['id'];
$name = $row['name'];
$data[] = array(
"id" => $id,
"name" => $name
);
}
}
echo json_encode($data);
die;
}
// Get cities
if($request == 'getCities'){
$state_id = 0;
$result = array();$data = array();
if(isset($postData->state_id)){
$state_id = $postData->state_id;
$sql = "SELECT * from cities WHERE state_id=?";
$stmt = $con->prepare($sql);
$stmt->bind_param("i", $state_id);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()){
$id = $row['id'];
$name = $row['name'];
$data[] = array(
"id" => $id,
"name" => $name
);
}
}
echo json_encode($data);
die;
}
5. Javascript
Create 2 functions –
- getStates() – This function calls when selection is changed on country dropdown.
Empty state and city dropdown. Send AJAX POST request to ajaxfile.php, pass {request: 'getStates', country_id: country_id} as data, and set dataType: 'json'.
On successful callback loop on response and add <option > in state dropdown.
- getCities() – This function calls when selection is changed on state dropdown.
Empty the #city dropdown and send AJAX POST request to ajaxfile.php, pass {request: 'getCities', state_id: state_id} as data, and set dataType: 'json'.
On successful callback loop on response and add <option > in city dropdown.
Completed Code
function getStates(country_id){
// Empty the dropdown
var stateel = document.getElementById('state');
var cityel = document.getElementById('city');
stateel.innerHTML = "";
cityel.innerHTML = "";
var stateopt = document.createElement('option');
stateopt.value = 0;
stateopt.innerHTML = '-- Select State --';
stateel.appendChild(stateopt);
var cityopt = document.createElement('option');
cityopt.value = 0;
cityopt.innerHTML = '-- Select City --';
cityel.appendChild(cityopt);
// AJAX request
var xhttp = new XMLHttpRequest();
xhttp.open("POST", "ajaxfile.php", true);
xhttp.setRequestHeader("Content-Type", "application/json");
xhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
// Response
var response = JSON.parse(this.responseText);
var len = 0;
if(response != null){
len = response.length;
}
if(len > 0){
// Read data and create <option >
for(var i=0; i<len; i++){
var id = response[i].id;
var name = response[i].name;
// Add option to state dropdown
var opt = document.createElement('option');
opt.value = id;
opt.innerHTML = name;
stateel.appendChild(opt);
}
}
}
};
var data = {request:'getStates',country_id: country_id};
xhttp.send(JSON.stringify(data));
}
function getCities(state_id){
// Empty the dropdown
var cityel = document.getElementById('city');
cityel.innerHTML = "";
var cityopt = document.createElement('option');
cityopt.value = 0;
cityopt.innerHTML = '-- Select City --';
cityel.appendChild(cityopt);
// AJAX request
var xhttp = new XMLHttpRequest();
xhttp.open("POST", "ajaxfile.php", true);
xhttp.setRequestHeader("Content-Type", "application/json");
xhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
// Response
var response = JSON.parse(this.responseText);
var len = 0;
if(response != null){
len = response.length;
}
if(len > 0){
// Read data and create <option >
for(var i=0; i<len; i++){
var id = response[i].id;
var name = response[i].name;
// Add option to city dropdown
var opt = document.createElement('option');
opt.value = id;
opt.innerHTML = name;
cityel.appendChild(opt);
}
}
}
};
var data = {request:'getCities',state_id: state_id};
xhttp.send(JSON.stringify(data));
}
6. Demo
7. Conclusion
Follow the same steps to auto-populate multiple dropdowns.
If data is not populating on dropdown when you select a country or a state then use the browser network tab to debug. Check SQL queries and POST values again.
If you found this tutorial helpful then don't forget to share.