Dynamic Dependent Select Box using jQuery, Ajax and PHP


We have received many tutorial requests for making the dynamic dependent select box with PHP. Based on that request, we are going to explain the dynamic dependent multiple select boxes using jQuery, Ajax, PHP and MySQL.

The dynamic dependent select box is mostly used for country state city dropdown. In this tutorial, we’ll implement relational dropdown of country state city using jQuery, Ajax, PHP and MySQL. Means state is related with country and city is related with the state. Based on changing of country & state, respective state & city will be fetched from the database without reloading the page using jQuery, Ajax, PHP and MySQL.

dynamic-dependent-select-box-using-jquery-ajax-php-by-codexworld

At first, the country dropdown would be displayed with all countries. When a country would be chosen, the respective states would be fetched from the MySQL database and appear in the states dropdown. Alike when a state would be chosen, the respective cities will be fetched from the MySQL database and appear in the cities dropdown.

Database & Tables

Create a database, like location_db. location_db consists of three tables countries, states, and cities. states table has a relation with countries table and cities table has a relation with states table.

countries Table:
This table contains all the countries data. countries table SQL would like below.

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 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

states Table:
This table contains all the states data and country_id FOREIGN KEY. states table SQL would like below.

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 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

cities Table:
This table contains all the cities data and state_id FOREIGN KEY. cities table SQL would like below.

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 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

dbConfig.php File

This file helps to connect and select the database.

<?php
//db details
$dbHost 'localhost';
$dbUsername 'root';
$dbPassword '';
$dbName 'location_db';

//Connect and select the database
$db = new mysqli($dbHost$dbUsername$dbPassword$dbName);

if (
$db->connect_error) {
    die(
"Connection failed: " $db->connect_error);
}
?>

index.php File

This file contains a jQuery library, JavaScript, HTML and PHP code.

The following JavaScript code is used for getting the state and city data from ajaxData.php file using ajax. Also, it displays the returned HTML of the ajaxData.php file to the respective select box.

<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>

Using PHP & HTML the initial country select box is displayed along with the state and city select box.

<?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>

ajaxData.php File

This file is requested by the Ajax and in this file state or city data is fetched from the database based on the requested country_id or state_id. Also, the respective select options HTML are returned to the Ajax success function.

<?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>';
    }
}
?>

15 Comments

  1. Sasikumar Said...
  2. Ivan Said...
  3. Ivan Said...
  4. Satyasundar Das Said...
  5. Bishop Said...
  6. Sergiu Said...
    • CodexWorld Said...
  7. Peterpeterson Said...
  8. SteveRogers Said...
  9. Anupam Said...
  10. Manoj Goyal Said...
  11. Fahad Shah Said...
  12. Viorica Said...
  13. Pradeep Kumar Said...
  14. Mahmud Said...

Leave a reply

Connect With CodexWorld