Dynamic dependent dropdown select list using jquery and php
In this tutorial we are going to see how to change states drop down list ans city drop down list option based on the dropdown select country name. we are going use jquery and mysql database for select country and retrieve state data. means Loading records from database dynamically and display it in select box without refreshing the whole page with the help of Ajax and jQuery and PHP code. country state city Dropdown, using ajax and jqurey fill country state city dropdown. Ajax country state city dropdown php, onchange in jquery, dynamic dropdown.
Step 1: Create database and database table With content. Create database name “Country_db” and table with Country, state and city
Country table:
CREATE TABLE IF NOT EXISTS `countries` ( `country_id` int(11) NOT NULL, `country_name` varchar(30) CHARACTER SET utf8 NOT NULL, `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:Blocked, 1:Active' ) ENGINE=InnoDB AUTO_INCREMENT=240 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Cities Table
CREATE TABLE IF NOT EXISTS `cities` ( `city_id` int(11) NOT NULL, `city_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL, `state_id` int(11) NOT NULL, `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:Blocked, 1:Active' ) ENGINE=InnoDB AUTO_INCREMENT=6178 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
States Table
CREATE TABLE IF NOT EXISTS `states` ( `state_id` int(11) NOT NULL, `state_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL, `country_id` int(11) NOT NULL, `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:Blocked, 1:Active' ) ENGINE=InnoDB AUTO_INCREMENT=1652 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Step 2: Database Config
<?php
//db details
$dbHost = 'localhost';
$dbUsername = 'root';
$dbPassword = '';
$dbName = 'country';
//Connect and select the database
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}
?>
Step 2: Create the index page
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
</head>
<body>
<div class="select-boxes">
<?php
//Include database configuration file
include('dbConfig.php');
//Get all country data
$query = $db->query("SELECT * FROM countries WHERE status = 1 ORDER BY country_name ASC");
//Count total number of rows
$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['country_name'].'</option>';
}
}else{
echo '<option value="">Country not available</option>';
}
?>
</select>
<select name="state" id="state">
<option value="">Select country first</option>
</select>
<select name="city" id="city">
<option value="">Select state first</option>
</select>
</div>
</body>
</html>
Step 3: create jquery with ajax function to pass country id into next page and select state and cities form the database table
<script src="jquery.min.js"></script>
<script type="text/javascript">
$(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').html(html);
$('#city').html('<option value="">Select state first</option>');
}
});
}else{
$('#state').html('<option value="">Select country first</option>');
$('#city').html('<option value="">Select state first</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').html(html);
}
});
}else{
$('#city').html('<option value="">Select state first</option>');
}
});
});
</script>
Ajax page for select database of cities and states
<?php
//Include database configuration file
include('dbConfig.php');
if(isset($_POST["country_id"]) && !empty($_POST["country_id"])){
//Get all state data
$query = $db->query("SELECT * FROM states WHERE country_id = ".$_POST['country_id']." AND status = 1 ORDER BY state_name ASC");
//Count total number of rows
$rowCount = $query->num_rows;
//Display states list
if($rowCount > 0){
echo '<option value="">Select state</option>';
while($row = $query->fetch_assoc()){
echo '<option value="'.$row['state_id'].'">'.$row['state_name'].'</option>';
}
}else{
echo '<option value="">State not available</option>';
}
}
if(isset($_POST["state_id"]) && !empty($_POST["state_id"])){
//Get all city data
$query = $db->query("SELECT * FROM cities WHERE state_id = ".$_POST['state_id']." AND status = 1 ORDER BY city_name ASC");
//Count total number of rows
$rowCount = $query->num_rows;
//Display cities list
if($rowCount > 0){
echo '<option value="">Select city</option>';
while($row = $query->fetch_assoc()){
echo '<option value="'.$row['city_id'].'">'.$row['city_name'].'</option>';
}
}else{
echo '<option value="">City not available</option>';
}
}
?>
[sociallocker]
[/sociallocker]
That’s it enjoy the code. demo and download the script and use it easily. Feel free to comment your suggestions regarding this tutorial. Subscribe me.
