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
states
table according to$country_id
value and assign to$result
. Loop on$result
and initialize$data
Array withid
andname
keys.
Return $data
in JSON format.
- If $request == ‘getCities’ – Fetch records from
cities
table according to$state_id
value and assign to$result
. Loop on$result
and initialize$data
Array withid
andname
keys.
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.
Hi Rhys,
You can read and store the value like this –
var country = document.getElementById(‘country’).value;
var state = document.getElementById(‘state’).value;
var city = document.getElementById(‘city’).value;