How to Create Dependent Dropdown using Ajax, PHP

The dependent dropdown using ajax is the most popular feature of Web development. It can automatically display dependent values in the dropdown. when the user selects one value of the first dropdown options. The next dropdown will be filled with dependent values automatically.

The Dependent values will be fetched dynamically from the database table using ajax, PHP & MYSQL. Even the page will not refresh. So, It makes the project user-friendly & attractive.

In this tutorial, I have provided an example of three dependent dropdowns like Country, State & City. Once you learn it with the given script, you will able to develop other multiple dependent dropdowns. Therefore, You should read all the points of the given steps.

Dependent Dropdown Using Ajax, PHP & MySQL

Before writing the Dependent Dropdown Script, You should read the following points. These points will help you to implement it quickly.

• First of all, you, create a database & Table according to your project requirement. But remember that you must combine to keep the common column (id ) in each table using Foreign Key.
• Store required data into the table of a database. Either you import external data files or insert data directly.
• You need to create HTML dropdown Input according to your project requirement.
• By default, You have to display data in the first dropdown list by fetching it from the database. The remaining dropdown lists data will be displayed through the dependent ajax script.
• Send the request to the backend script to fetch dependent data from the database to change the value of the dropdown list.
• In this way, You will easily implement it

Read Also – 

Autocomplete Search in PHP  Using jQuery UI

Change URL without Reloading Page Using jQuery Ajax PHP

Folder Structure –

Create the project folder in the following structure. Otherwise, use the given script directly in your existing project folder.

dependent-dropdown/
    |__database.php
    |__dropdown-form.css
    |__ajax-script.js
    |__backend-script.php
    |

Now, Configure the following steps with a simple way –

1. Create MySQL Database and Table

Here I have put the database & table name for the testing purpose. You should put your own database & table name according to your project name.

Create a MySQL database codingstatus

Create a table countries to store country name using the following query

Table Name – countries

CREATE TABLE `countries` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Also, Create a table states to store the state name using the following query. It must have a common column that stores country id.

Table Name – states

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',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4121 ;

Create a table cities to store the city name using the following query. It must have a common column to store state id.

Table Name – cities

CREATE TABLE IF NOT EXISTS `cities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `state_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=47577 ;

2. Store Data in the Created MySQL tables

Get the countries, states & cities SQL file through the following URL and import it into your database.

• • • •  Get countries table
      •  Also, Get the states table
      •  Get cities table

If you don’t want the above file then Create tables for countries, states & cities in the database. These three tables must have a relation between each other.

• countries  must have a relation with the states table.
• states must have a relation with the cities table.

3. Connect Dependent Dropdown Script to MySQL Database

Connect dependent dropdown script to MySQL database using the following script. In this script, I have declared a connection variable with my local connection value. you should replace these values with your own according to your project requirement.

File Name – database.php

<?php

// Database configuration 
$dbHost     = "localhost";  //  your hostname
$dbUsername = "root";       //  your table username
$dbPassword = "";          // your table password
$dbName     = "codingstatus";  // your database name
 
// Create database connection 
$conn = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
 
// Check connection 
if ($conn->connect_error) { 
    die("Connection failed: " . $conn->connect_error); 
} 
?>

4. Create Dropdown Input Form Using HTML

To create dropdown input, You need to configure the following points –

• First of all, Include the database connection file database.php.
• Include jQuery CDN just above the </body> to execute jquery ajax script.
• Include external ajax file ajax-script.js just above the </body> and below the jQuery CDN.
• Create a Country dropdown field with id="country".
• Create State dropdown field with id="state".
• Even create City dropdown field with id="city".
• Create State Dropdown Field with
• Display country name in the first dropdown field by fetching from the table countries.
• Leave empty State & City dropdown field. Both fields will be filled with dependent data.

File Name – dropdown-form.php

<?php

include('database.php');
?>

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>     
<body>

<!--===== dependent dropdown form============-->
<div class="dependent-dropdown">
<form autocomplete="off" action="">
  <div class="input-field">
   <select id="country">
     <option value="">Select Country</option>
    
<?php

        $contryData="SELECT id, name from countries";
        $result=mysqli_query($conn,$contryData);
        if(mysqli_num_rows($result)>0)
        {
          while($arr=mysqli_fetch_assoc($result))
          {
              
?>

         <option value=""></option>
             
<?php
}} 
?>

   </select>
   
  </div>
  <div class="input-field">
    <select id="state">
     <option value="">State</option>
   </select>
  </div>
  <div class="input-field">
    <select id="city">
     <option value="">City </option>
   </select>
  </div>
  
</form>
</div>
<!--===== dependent dropdown form============-->

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="ajax-script.js" type="text/javascript"></script>
</body>
</html>

5. Create Dependent Dropdown Using jQuery Ajax

You have to create two dependent dropdown scripts in the file ajax-script.js for State Name & City Name. So, configure these scripts through the following separate part –

Dependent State Name –

Create an ajax script to change the state name automatically on the basis of changing the country name. Dependent state name will be fetched from the file backend.php.

Now, configure the following steps –

• Apply change event on the country dropdown field id  #country.
• Get the value of the country dropdown field and assign it to variable countryID.
• If countryID has a value, configure the following points.
  • Assign a backend script file backend-script.php to get dependent data through the post method.
  • If dependent data will be fetched successfully, display these data in the city dropdown.

Dependent City Name – 

Create an ajax script to change the City name automatically based on changing the State name. Dependent City name will also be fetched from the file backend.php.

Now, configure the following steps –

• Apply change event on state dropdown field id  #state.
• Get the value of the State dropdown field and assign it to variable stateID.
• If stateID has a value, configure the following points.
  • Assign a backend script file backend-script.php to get dependent data through the post method.
  • If dependent data will be fetched successfully, display these data in the city dropdown.

Complete Script –

Use the following ajax script directly in your project.

File Name – ajax-script.js

// ajax script for getting state data
   $(document).on('change','#country', function(){
      var countryID = $(this).val();
      if(countryID){
          $.ajax({
              type:'POST',
              url:'backend-script.php',
              data:{'country_id':countryID},
              success:function(result){
                  $('#state').html(result);
                 
              }
          }); 
      }else{
          $('#state').html('<option value="">Country</option>');
          $('#city').html('<option value=""> State </option>'); 
      }
  });

    // ajax script for getting  city data
   $(document).on('change','#state', function(){
      var stateID = $(this).val();
      if(stateID){
          $.ajax({
              type:'POST',
              url:'backend-script.php',
              data:{'state_id':stateID},
              success:function(result){
                  $('#city').html(result);
                 
              }
          }); 
      }else{
          $('#city').html('<option value="">City</option>');
          
      }
  });

6. Fetch Dependent Dropdown Data using PHP & MySQL

You have to write two backend scripts in the file backend-script.js for State Name & City Name. So, configure these scripts through the following separate part –

• Before writing the following scripts, you must include the database connection file database.php
• Fetch State Name based on Country Name Id
• Fetch City Name Base on State Name Id

File Name – backend-script.php

<?php

include('database.php');
// Fetching state data
$country_id=!empty($_POST['country_id'])?$_POST['country_id']:'';
if(!empty($country_id))
  {
  
  $query="SELECT id, name from states WHERE country_id=?";
  $countryData = $conn->prepare($query);
  $countryData->bind_param('s',$country_id); 
  $countryData->execute();
  $result=$query->get_result();
        
  if($result->num_rows>0)
  {
     echo "<option value=''>Select State</option>";
     while($arr= $result->fetch_assoc())
     {
        echo "<option value='".$arr['id']."'>".$arr['name']."</option><br>";
        
      }
   }  
 }


   // Fetching city data
$state_id=!empty($_POST['state_id'])?$_POST['state_id']:'';
if(!empty($state_id))
  {
  $query="SELECT id, name from cities WHERE state_id=?";
  $countryData = $conn->prepare($query);
  $countryData->bind_param('i',$state_id); 
  $countryData->execute();
  $result=$query->get_result();

  if($result->num_rows>0)
  {
     echo "<option value=''>Select City</option>";
     while($arr= $result->fetch_assoc())
     {
            echo "<option value='".$arr['id']."'>".$arr['name']."</option><br>";
        
     }
  }  
}
?>

My Suggestion

Dear Developer, I hope you have learned to create a dependent dropdown. Now you can create three or more dependent dropdowns according to your project needs. So, You can modify the above script and use it in your project.

If you have any suggestions or questions regarding the web programming topics, ask me directly through the comment box.

Thanks for visiting this tutorial.