Dependent Dropdown Using Ajax, PHP & MySQL

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

How to Create Dependent Dropdown using Ajax, PHP

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

Load More Data on Infinite Scroll Using Ajax PHP

 

Folder Structure –

Create the project folder like 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 using the following query.

"CREATE DATABASE codingstatus";

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

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.

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.

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

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

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 from, 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="<?php echo $arr['id']; ?>"><?php echo $arr['name']; ?></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 city dropdown.
// 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>'); 
      }
  });

 

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 city dropdown.
// 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>');
        
    }
});

 

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 using the following script

<?php
include('database.php');
?>

 

Fetch State Name based on Country Name Id –

This script will fetch State Name based on Country Name id.

// Fetching state data
$country_id=!empty($_POST['country_id'])?$_POST['country_id']:'';
if(!empty($country_id))
  {
        $contryData="SELECT id, name from states WHERE country_id=$country_id";
        $result=mysqli_query($conn,$contryData);
        if(mysqli_num_rows($result)>0)
        {
          echo "<option value=''>Select State</option>";
          while($arr=mysqli_fetch_assoc($result))
          {
          	echo "<option value='".$arr['id']."'>".$arr['name']."</option><br>";
        
          }
        }  
   }

 

Fetch City Name Base on State Name Id –

This script will fetch city name based on state name id.

   // Fetching city data
$state_id=!empty($_POST['state_id'])?$_POST['state_id']:'';
if(!empty($state_id))
  {
        $cityData="SELECT id, name from cities WHERE state_id=$state_id";
        $result=mysqli_query($conn,$cityData);
        if(mysqli_num_rows($result)>0)
        {
          echo "<option value=''>Select City</option>";
          while($arr=mysqli_fetch_assoc($result))
          {
          	echo "<option value='".$arr['id']."'>".$arr['name']."</option><br>";
        
          }
        }  
   }

Complete Backend Script –

You can use one of the following scripts in backend-script.php  according to your choice.

Backend Script – 1

This script is created by using MySQli Procedural.

<?php
include('database.php');
// Fetching state data
$country_id=!empty($_POST['country_id'])?$_POST['country_id']:'';
if(!empty($country_id))
  {
        $contryData="SELECT id, name from states WHERE country_id=$country_id";
        $result=mysqli_query($conn,$contryData);
        if(mysqli_num_rows($result)>0)
        {
          echo "<option value=''>Select State</option>";
          while($arr=mysqli_fetch_assoc($result))
          {
          	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))
  {
        $cityData="SELECT id, name from cities WHERE state_id=$state_id";
        $result=mysqli_query($conn,$cityData);
        if(mysqli_num_rows($result)>0)
        {
          echo "<option value=''>Select City</option>";
          while($arr=mysqli_fetch_assoc($result))
          {
          	echo "<option value='".$arr['id']."'>".$arr['name']."</option><br>";
        
          }
        }  
   }

   
         ?>

Backend Script – 2

This Script is created by using MySQLi Prepared Statement to protect from

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

Backend Script – 3

This script is created by using PDO Statement.

<?php
$host = 'localhost';
$db   = 'codingstatus.com';
$user = 'root';
$pass = '';
$options = [
    \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    \PDO::ATTR_EMULATE_PREPARES   => false,
 ];
 $dsn = "mysql:host=$host;dbname=$db";
 try {
      $pdo = new \PDO($dsn, $user, $pass, $options);
 } catch (\PDOException $e) {
      throw new \PDOException($e->getMessage(), (int)$e->getCode());
 }


// 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=?";
 $stmt = $pdo->prepare($query);
 $stmt->execute([$country_id]);
  echo "<option value=''>Select State</option>";
 while ($arr = $stmt->fetch(PDO::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=?";
 $stmt = $pdo->prepare($query);
 $stmt->execute([$state_id]);
  echo "<option value=''>Select City</option>";
 while ($arr = $stmt->fetch(PDO::FETCH_ASSOC)) {
   echo "<option value='".$arr['id']."'>".$arr['name']."</option><br>";
 }
  
}
         ?>

 

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.

16 thoughts on “Dependent Dropdown Using Ajax, PHP & MySQL”

  1. in dropdown_data.php line:

    $country_id=$_POST[‘country_id’] ?? ”;

    the line have syntax error.

    change it to:
    $country_id=$_POST[‘country_id’]

    Reply
    • Thanks for your suggestion… You can also declare as $country_id= !empty($_POST[‘country_id’])? $_POST[‘country_id’] : ”;

      Reply
  2. Hi, I tried your code already using my own database, unfortunately it only loads the first dropdown while the other two are empty. I couldn’t find any error as far as I see. What issue that can be possibly happening here?

    Reply
    • Hi, Thanks for asking about your issue…
      I have tested this code, It works fine. Even you can see its live demo.
      If you have an issue in this code by executing your own computer then you can check this issue yourself by doing the following things –

      • first of all review your code to find out the missing code statement of Ajax & PHP.
      • Make sure that dropdown option value must have id
      • Inspect your web browser.
      • Click on the network option.
      • Press Ctrl + R
      • Change the first dropdown
      • A URL will appear in the network section after changing the first dropdown
      • Click appeared URL in network section.
      • Then some option will appear on right side
      • Click Headers option and check the Request URL is valid or not
      • Click the preview option and see there code error.

      I hope that the above information will help you to find out your issue and your code will work fine

      Reply
      • Hi, thank you for the steps! I managed to get the error and resolved it. Seems that I missed a pair of ‘ symbols in my code. Thank you so much for your code, sir. Keep on developing and educating. Thank you so much again. I appreciate this.

        Reply
  3. Thanks for the guide. But I am stuck at the edit form. How to retrieve data for the stored db for the country, state and city in edit?

    Reply
    • Welcome, Zaharin…
      You have to write same scripts and replace change event with load event for the edit form.
      By the way, I will share a tutorial on this for the edit form soon.

      Reply
  4. Hi, thank you very much for the tutorial, I wonder if it’s possible to have both combo box from one table for example if have category which is text field and sub category which is also text field stored in the same table, then to fetch all sub category based on category selected.
    thanks in advance.

    Reply
  5. Hi, thanks for this tutorial I would also like to thank you for steps on resolving the issue.

    I have a problem with data being shown in the drop down. I have wasted time from morning around 8 hours. I followed your steps and in the preview the data is coming but I am not able to find why its not populating in the second drop down.

    Any help is greatly appreciated. Once again thanks for the tutorial.

    Reply
  6. Hi,
    I think the problem is with the bootstrap and materialize theme without any styles the dropdowns are working perfectly. I don’t know if I will be able to solve the issue. Any ways thanks for making good tutorials. I hope you will be making many more like this.

    Reply

Leave a Comment