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.
Thank you for the great tutorial.
If I use a form instead of a table. How would I store the options selected in variables?
Thank you,
Rhys.