Dynamic Dependent Select Box Dropdown using jQuery, Ajax and PHP

The dynamic dependent select box is used to auto-populate the dependent data in the dropdown list. Based on the drop-down selection, the dependent data are retrieved from the database and displayed in the next select box. Generally, the dynamic dependent select box is used to implement Country State City dependent dropdown functionality. You can easily implement dynamic dependent dropdown without page refresh using Ajax in PHP.

Dynamic dependent dropdown is very useful to fetch dynamic relational data from the database and listed in multiple select boxes. In this tutorial, we will show you how to implement a relational dropdown of country state city using jQuery, Ajax, PHP, and MySQL. In the dependent select box, the state is related to the country, and the city is related to the state. Based on changing of country, the respective state & city is fetched from the database without reloading the page using jQuery, Ajax, PHP, and MySQL.

In this example script, we will integrate country, state, and city dependent dropdown select boxes with PHP and MySQL.

• Initially, all the countries will be listed in the Country dropdown.
• When a country is selected, the respective states will be fetched from the database and appear in the state dropdown.
• Alike when a state is selected, the respective cities will be fetched from the database and appear in the city dropdown.

Before getting started to build a dynamic dependent dropdown list with PHP and MySQL, take a look at the file structure.

dynamic_dependent_dropdown_with_php/
├── dbConfig.php
├── index.php
├── ajaxData.php
└── js/
    └── jquery.min.js

Create Database Tables

To store the data of the country, state, and city, three tables are required in the database. Also, there would be a relationship between countries, states, and cities table. The states table has a relation with the countries table and the cities table has a relation with the states table.

The following SQL creates a countries table in the MySQL database.

CREATE TABLE `countries` (
 `country_id` int(11) NOT NULL AUTO_INCREMENT,
 `country_name` varchar(50) CHARACTER SET utf8 NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The following SQL creates a states table with parent country_id field in the MySQL database.

CREATE TABLE `states` (
 `state_id` int(11) NOT NULL AUTO_INCREMENT,
 `country_id` int(11) NOT NULL,
 `state_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`state_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The following SQL creates a cities table with parent state_id field in the MySQL database.

CREATE TABLE `cities` (
 `city_id` int(11) NOT NULL AUTO_INCREMENT,
 `state_id` int(11) NOT NULL,
 `city_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Database Configuration (dbConfig.php)

The dbConfig.php file is used to connect the database using PHP and MySQL. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your database credentials.

<?php 
// Database configuration 
$dbHost     = "localhost"; 
$dbUsername = "root"; 
$dbPassword = "root"; 
$dbName     = "codexworld"; 
 
// Create database connection 
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
 
// Check connection 
if ($db->connect_error) { 
    die("Connection failed: " . $db->connect_error); 
}

Dynamic Dependent Select Boxes (index.php)

HTML & PHP Code:
Initially, all the country data is fetched from the database and listed in the country dropdown. Once a value is selected in the country dropdown, the respective data is listed in the state and city dropdown.

<?php 
    // Include the database config file 
    include_once 'dbConfig.php'; 
     
    // Fetch all the country data 
    $query = "SELECT * FROM countries WHERE status = 1 ORDER BY country_name ASC"; 
    $result = $db->query($query); 
?>

<!-- Country dropdown -->
<select id="country">
    <option value="">Select Country</option>
    <?php 
    if($result->num_rows > 0){ 
        while($row = $result->fetch_assoc()){  
            echo '<option value="'.$row['country_id'].'">'.$row['country_name'].'</option>'; 
        } 
    }else{ 
        echo '<option value="">Country not available</option>'; 
    } 
    ?>
</select>

<!-- State dropdown -->
<select id="state">
    <option value="">Select country first</option>
</select>

<!-- City dropdown -->
<select id="city">
    <option value="">Select state first</option>
</select>

JavaScript Code:
Once a country is selected, the dependent states are fetched from the server-side script (ajaxData.php) using jQuery and Ajax. Likewise, by selecting the state, the dependent cities are retrieved from the server-side script (ajaxData.php) using jQuery and Ajax.

Include the jQuery library first, it is required to initiate Ajax requests.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.0/jquery.min.js"></script>

Initiate Ajax request to fetch the dependent data (state and city) from the database without page refresh using jQuery.

• On changing the value in the country dropdown,
  • POST the selected country ID to the server-side script (ajaxData.php) via Ajax request.
  • The select box option list is retrieved from the response and HTML is set to the dependent state dropdown.
• On changing the value in state dropdown,
  • POST selected state ID to the server-side script (ajaxData.php) via Ajax request.
  • The select box option list is retrieved from Ajax response and HTML is set to the dependent city dropdown.

<script>
$(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>

Retrieved Dependent Data from Database (ajaxData.php)

The ajaxData.php is called by the Ajax request to retrieve the dependent data from the database using PHP and MySQL. The state and city dropdown HTML is returned to the success method of the Ajax request.

• Retrieve the ID from the Ajax request using PHP $_POST method.
• If country_id is provided,
  • Fetch the state data based on the specific country ID from the database.
  • Generate HTML of the state options list.
• If state_id is provided,
  • Fetch the city data based on the specific state ID from the database.
  • Generate HTML of the city options list.
• Render options of the dropdown list.

<?php 
// Include the database config file 
include_once 'dbConfig.php'; 
 
if(!empty($_POST["country_id"])){ 
    // Fetch state data based on the specific country 
    $query = "SELECT * FROM states WHERE country_id = ".$_POST['country_id']." AND status = 1 ORDER BY state_name ASC"; 
    $result = $db->query($query); 
     
    // Generate HTML of state options list 
    if($result->num_rows > 0){ 
        echo '<option value="">Select State</option>'; 
        while($row = $result->fetch_assoc()){  
            echo '<option value="'.$row['state_id'].'">'.$row['state_name'].'</option>'; 
        } 
    }else{ 
        echo '<option value="">State not available</option>'; 
    } 
}elseif(!empty($_POST["state_id"])){ 
    // Fetch city data based on the specific state 
    $query = "SELECT * FROM cities WHERE state_id = ".$_POST['state_id']." AND status = 1 ORDER BY city_name ASC"; 
    $result = $db->query($query); 
     
    // Generate HTML of city options list 
    if($result->num_rows > 0){ 
        echo '<option value="">Select city</option>'; 
        while($row = $result->fetch_assoc()){  
            echo '<option value="'.$row['city_id'].'">'.$row['city_name'].'</option>'; 
        } 
    }else{ 
        echo '<option value="">City not available</option>'; 
    } 
} 
?>

Post and Get Value of Dynamic Dependent Dropdown

After the form submission, you can get the value of the dynamic dependent select boxes using PHP. Use the $_POST method to retrieve the selected option value in PHP.

HTML Code:

<form action="" method="post">
    <!-- Country dropdown -->
    <select id="country" name="country">
        <option value="">Select Country</option>
    </select>
	
    <!-- State dropdown -->
    <select id="state" name="state">
        <option value="">Select state</option>
    </select>
	
    <!-- City dropdown -->
    <select id="city" name="city">
        <option value="">Select city</option>
    </select>
	
    <input type="submit" name="submit" value="Submit"/>
</form>

PHP Code:

<?php 
if(isset($_POST['submit'])){ 
    echo 'Selected Country ID: '.$_POST['country']; 
    echo 'Selected State ID: '.$_POST['state']; 
    echo 'Selected City ID: '.$_POST['city']; 
} 
?>

Multi-select Dropdown List with Checkbox using jQuery

Conclusion

The dynamic dependent select boxes are a very useful element when you want to allow the user to select values from the multiple dropdown list. In the example code, we have shown the dynamic dependent select boxes for country state city dropdown in PHP. You can easily extend the dynamic dependent select boxes functionality and implement Ajax country state city dropdown with PHP and MySQL. If you have a large number of data, use the JSON data type in Ajax and PHP for a fast response.