In this tutorial, we will see how to create a cascaded dropdown list or a dependent dropdown list or dependent Select Box list in PHP using JQuery and AJAX.
Dependent dropdown is generally used for country,state and city dropdown where state is related to country and city is related to state.In dependent select box we will see how one dropdown option value load on changing option value of another dropdown.
We have two dropdown lists of country and state and on changing country dropdown option value, a jquery and ajax is used to load dependent state dropdown options ,this type of dropdown is called dependent dropdown list and can be created using jQuery and AJAX in PHP.
Initially we fetch all country name from database and display in country dropdown whereas state and city dropdown has no options. On changing country name we use jquery and ajax to get respective state name from database and append the result to state dropdown. When user select a state name from dropdown then again we jquery and ajax to get respective city name and append the result to city dropdown.
So in this example we will create dependent select box with three dropdown as Country, state and city. Now first of all create Database and tables ,then files. After reading this tutorial, I hope that you will be able to easily use dependent select box in your web applications.
Database & Tables
Database
- country_info
Tables
- country
- state
- city
Now we will see how to load these dropdown option values based on previous selected dropdown value. First of all create database “country_info” which will consist three tables “country”,”state” and “city” which shown below. In this State table will be related with country table and city table will be related with state table.
Country table
CREATE TABLE IF NOT EXISTS `country` ( `id` int(11) NOT NULL AUTO_INCREMENT, `country_name` varchar(35) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=202 ;
State table
CREATE TABLE IF NOT EXISTS `state` ( `id` int(11) NOT NULL AUTO_INCREMENT, `state_name` varchar(35) NOT NULL, `country_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=95 ;
City table
CREATE TABLE IF NOT EXISTS `city` ( `id` int(20) NOT NULL AUTO_INCREMENT, `city_name` varchar(35) NOT NULL, `state_id` int(25) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=65 ;
Files
List of PHP file to create dependent select box are as follows:
- dbconfig.php
- index.php
- get_ajax_data.php
dbconfig.php
<?php
$conn = mysqli_connect("localhost","root","","country_info") or die("connection error" . mysqli_error($conn));
?>
Create index.php where all three dropdown will be listed and seen by user.
index.php
<?php
//database config file
include_once 'dbconfig.php';
?>
<div class="data-info">
<h2>Dynamic Dependent Select Box Using Jquery and Ajax in PHP</h2>
<select id="country" name="country" onChange="getState(this.value);">
<option selected="selected">Select Country</option>
<?php
//Get all country data
$query = mysqli_query($conn,"SELECT * FROM `country` WHERE `id` IN (80,9,193)");
if(mysqli_num_rows($query)>0){
while($countries = mysqli_fetch_array($query)){
?>
<option value="<?php echo $countries['id']; ?>"><?php echo $countries['country_name']; ?></option>
<?php }}
else{
echo '<option value="">Country not available</option>';
} ?>
</select>
<select id="state" name="state" onChange="getCity(this.value);">
<option selected="selected">Select State</option>
</select>
<select id="city" name="city">
<option selected="selected">Select city</option>
</select>
</div>
This below given snippet sends current country_id and state_id when dropdown option value selected. Get response from get_ajax_data.php and display html data to respective dropdown list.
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<script>
function getState(val) {
$.ajax({
type: "POST",
url: "get_ajax_data.php",
data: "country_id="+val,
success: function(response){
$('#state').html(response);
}
});
}
function getCity(val) {
$.ajax({
type: "POST",
url: "get_ajax_data.php",
data: "state_id="+val,
success: function(response){
$('#city').html(response);
}
});
}
</script>
<style>
.data-info
{
margin: 0 auto;
text-align: center;
width: 350px;
}
select
{
margin: 25px;
border: none;
background: #f7f7f7;
padding: 15px 10px;
font-size: 16px;
width:100%;
box-sizing: border-box;
-moz-box-sizing: border-box;
-webkit-box-sizing: border-box;
}
</style>
get_ajax_data.php
AJAX request is send on this php file and then it retrieve data like state list or city list from database based on request id sent (country_id or state_id).
- Select state records from state table and load data dynamically based on country select box option on change.
- Select city records from city table and load data dynamically based on state select box option on change.
<?php
//database config file
include_once 'dbconfig.php';
if(isset($_POST['country_id']) && !empty($_POST['country_id'])){
//get state list
$sql = mysqli_query($conn, "SELECT * FROM `state` WHERE `country_id`='".$_POST['country_id']."'");
//check for rows
if(mysqli_num_rows($sql)>0){
while($states=mysqli_fetch_array($sql))
{ ?>
<option value ='<?php echo $states['id']; ?> '><?php echo $states['state_name']; ?> </option>
<?php }
}
else
{
echo '<option value="">State not available</option>';
}}
//when state id in post
if(isset($_POST['state_id']) && !empty($_POST['state_id']))
{
//get all city list
$sql = mysqli_query($conn,"SELECT * FROM `city` WHERE `state_id`='".$_POST['state_id']."'");
//check for rows
if(mysqli_num_rows($sql)>0){
while($cities=mysqli_fetch_array($sql))
{ ?>
<option value ='<?php echo $cities['id']; ?> '><?php echo $cities['city_name']; ?> </option>
<?php }
}
else
{
echo '<option value="">City not available</option>';
}
}
?>
To get complete script of three dependent select box, download the zipped file. Download by click on given link. This is simple dependent select box example for country, state and city.
That’s it. Enjoy coding 🙂 🙂