Dynamically Dependent country state city drop down box using jQuery, Ajax and PHP:
This is commonly used for country state city drop down or we can use as per our requirement. In other words if we select country, states of that country will be displayed in the drop down box and on select state the city of that particular state will appear in the drop down box.
That means in country state city city is dependent on state and state is dependent on country.
Country state city drop down box using jQuery, Ajax and PHP
Create a database in phpmyadmin : mylocationdb . The database will consist of three table countries, states, cities. Below is the table structure of the countries, states, cities tables. One can create the table according to the structure given below or copy paste the table structure in sql query browser in php myadmin.
CREATE TABLE IF NOT EXISTS `countries` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`state` int(11) NOT NULL DEFAULT ‘1’,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `states` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`country_id` int(11) NOT NULL DEFAULT ‘1’,
`status` int(11) NOT NULL DEFAULT ‘1’,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `cities` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`state_id` int(11) NOT NULL,
`country_id` int(11) NOT NULL,
`status` int(11) NOT NULL DEFAULT ‘1’,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Now go to localhost xampp->htdocs-> create folder location in it.
the location folder will consist of the following files :
1 ) config.php
2) jquery.min.js
3) autocomplete.js
4) ajax_autocomplete.php
5) index.php
File content of config.php
<?php
$dbHost = ‘localhost’;
$dbUsername = ‘root’;
$dbPassword = ”;
$dbName = ‘mylocationdb’;
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
if ($db->connect_error) {
die(“Connection error: ” . $db->connect_error);
}
?>Download the file from jquery library, link https://ajax.googleapis.com/ajax/libs/jquery/1.12.2/jquery.min.js
autocomplete.js file content
$(document).ready(function(){
$(‘#country’).on(‘change’,function(){
var countryID = $(this).val();
if(countryID){
$.ajax({
type:’POST’,
url:’ajaxData.php’,
data:’country_id=’+countryID,
success:function(html){
$(‘#state-div’).html(html);
$(‘#city-div’).html(‘<option value=””>Select state</option>’);
}
});
}else{
$(‘#state’).html(‘<option value=””>Select country</option>’);
$(‘#city’).html(‘<option value=””>Select state </option>’);
}
});
$(‘#state’).on(‘change’,function(){
var stateID = $(this).val();
if(stateID){
$.ajax({
type:’POST’,
url:’ajaxData.php’,
data:’state_id=’+stateID,
success:function(html){
$(‘#city-div’).html(html);
}
});
}else{
$(‘#city’).html(‘<option value=””>Select state</option>’);
}
});
});ajax_autocomplete.php
<?php
include(‘config.php’);
if(isset($_POST[“country_id”]) && !empty($_POST[“country_id”])){
$query = $db->query(“SELECT * FROM states WHERE country_id = “.$_POST[‘country_id’].” AND status = 1 ORDER BY name ASC”);
$rowCount = $query->num_rows;
if($rowCount > 0){
echo ‘<option value=””>Select state</option>’;
while($row = $query->fetch_assoc()){
echo ‘<option value=”‘.$row[‘id’].’”>’.$row[‘name’].'</option>’;
}
}else{
echo ‘<option value=””>State not available</option>’;
}
}
if(isset($_POST[“state_id”]) && !empty($_POST[“state_id”])){
$query = $db->query(“SELECT * FROM cities WHERE state_id = “.$_POST[‘state_id’].” AND status = 1 ORDER BY name ASC”);
$rowCount = $query->num_rows;
if($rowCount > 0){
echo ‘<option value=””>Select city</option>’;
while($row = $query->fetch_assoc()){
echo ‘<option value=”‘.$row[‘id’].’”>’.$row[‘name’].'</option>’;
}
}else{
echo ‘<option value=””>City not available</option>’;
}
}
?>index.php
<html>
<head>
<title>Dynamically Dependent drop down box using jQuery, Ajax and PHP</title>
<script src=”jquery.min.js”></script>
<script src=”autocomplete.js”></script>
</head>
<body>
<?php
include(‘config.php’);
$query = $db->query(“SELECT * FROM countries WHERE status = 1 ORDER BY country_name ASC”);
$rowCount = $query->num_rows;
?>
<select name=”country” id=”country”>
<option value=””>Select Country</option>
<?php
if($rowCount > 0){
while($row = $query->fetch_assoc()){
echo ‘<option value=”‘.$row[‘country_id’].’”>’.$row[‘name’].'</option>’;
}
}else{
echo ‘<option value=””>Country not available</option>’;
}
?>
</select>
<select name=”state” id=”state-div”>
<option value=””>Select country</option>
</select>
<select name=”city” id=”city-div”>
<option value=””>Select state</option>
</select>
</body>
</html>