Dynamic Dependent Dropdown in PHP using jQuery AJAX

May 26, 2020 XpertPhp

In this tutorial, we will discuss how to create a dynamic dependent dropdown in PHP using jquery and ajax. The dynamic dependent select box depends on another dropdown so here we will use the ajax. because when the user changes the dropdown at that time we need to ajax. because of without reloading the page we can get the data by ajax. so here we will use the ajax dynamic dependent dropdown.

Here we are taking an example of Dynamic dependent dropdown country, state, and city in PHP using jQuery AJAX. When a country dropdown is selected, the relevant states will be retrieved from the MySQL database and will be displayed in the state dropdown list. As when a state is selected, the relevant cities will be retrieved from the MySQL database and will be displayed in the cities dropdown list.

So you can follow the below steps and see our example for Dynamic Dependent Dropdown in PHP using jQuery AJAX.

Overview

Create a Database and Table

Connect to Database

Create Dynamic Dependent Select Boxes

Retrieved dependent data from database using jquery ajax

Create a Database and Table

First of all for we will create a database and table. after then we will add dummy data in the table. here we take the products table and also added products related data into the product table. so you can see the below code.

CREATE TABLE `countries` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



INSERT INTO `countries` (`id`, `name`, `created_at`, `updated_at`) VALUES
(1, 'India', '2021-01-31 18:30:00', NULL),
(2, 'USA', '2021-01-31 18:30:00', NULL);


CREATE TABLE `states` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `country_id` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



INSERT INTO `states` (`id`, `name`, `country_id`, `created_at`, `updated_at`) VALUES
(1, 'Gujarat', 1, '2021-01-31 18:30:00', NULL),
(2, 'California', 2, '2021-01-31 18:30:00', NULL),
(3, 'Florida', 2, '2021-01-31 18:30:00', NULL),
(4, 'Bihar', 1, '2021-01-31 18:30:00', NULL);


CREATE TABLE `cities` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `state_id` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



INSERT INTO `cities` (`id`, `name`, `state_id`, `created_at`, `updated_at`) VALUES
(1, 'Patna', 4, '2021-01-31 18:30:00', NULL),
(2, 'Surat', 1, '2021-01-31 18:30:00', NULL),
(3, 'Rajkot', 1, '2021-01-31 18:30:00', NULL),
(4, 'Ahmedabad', 1, '2021-01-31 18:30:00', NULL),
(5, 'Simi Valley', 2, '2021-01-31 18:30:00', NULL),
(6, 'Vacaville', 2, '2021-01-31 18:30:00', NULL),
(7, 'Palm Beach', 3, '2021-01-31 18:30:00', NULL),
(8, 'Palm Bay', 3, '2021-01-31 18:30:00', NULL);

ALTER TABLE `countries`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `states`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `cities`
  ADD PRIMARY KEY (`id`);  
  
ALTER TABLE `countries`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

ALTER TABLE `states`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;  
  
ALTER TABLE `cities`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

CREATE TABLE `countries` (

`id` int(10) UNSIGNED NOT NULL,

`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

`created_at` timestamp NULL DEFAULT NULL,

`updated_at` timestamp NULL DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `countries` (`id`, `name`, `created_at`, `updated_at`) VALUES

(1, 'India', '2021-01-31 18:30:00', NULL),

(2, 'USA', '2021-01-31 18:30:00', NULL);

CREATE TABLE `states` (

`id` int(10) UNSIGNED NOT NULL,

`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

`country_id` int(11) NOT NULL,

`created_at` timestamp NULL DEFAULT NULL,

`updated_at` timestamp NULL DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `states` (`id`, `name`, `country_id`, `created_at`, `updated_at`) VALUES

(1, 'Gujarat', 1, '2021-01-31 18:30:00', NULL),

(2, 'California', 2, '2021-01-31 18:30:00', NULL),

(3, 'Florida', 2, '2021-01-31 18:30:00', NULL),

(4, 'Bihar', 1, '2021-01-31 18:30:00', NULL);

CREATE TABLE `cities` (

`id` int(10) UNSIGNED NOT NULL,

`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

`state_id` int(11) NOT NULL,

`created_at` timestamp NULL DEFAULT NULL,

`updated_at` timestamp NULL DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `cities` (`id`, `name`, `state_id`, `created_at`, `updated_at`) VALUES

(1, 'Patna', 4, '2021-01-31 18:30:00', NULL),

(2, 'Surat', 1, '2021-01-31 18:30:00', NULL),

(3, 'Rajkot', 1, '2021-01-31 18:30:00', NULL),

(4, 'Ahmedabad', 1, '2021-01-31 18:30:00', NULL),

(5, 'Simi Valley', 2, '2021-01-31 18:30:00', NULL),

(6, 'Vacaville', 2, '2021-01-31 18:30:00', NULL),

(7, 'Palm Beach', 3, '2021-01-31 18:30:00', NULL),

(8, 'Palm Bay', 3, '2021-01-31 18:30:00', NULL);

ALTER TABLE `countries`

ADD PRIMARY KEY (`id`);

ALTER TABLE `states`

ADD PRIMARY KEY (`id`);

ALTER TABLE `cities`

ADD PRIMARY KEY (`id`);

ALTER TABLE `countries`

MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

ALTER TABLE `states`

MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

ALTER TABLE `cities`

MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

Connect to Database

Here in this step, we will create a config.php file to connecting the MySQL with PHP. so you can see the below code for configuration.

config.php

<?php
$hostname="localhost";
$username="root";
$password="";
$database="php_dynamic_dependent_dropdown";
$conn = mysqli_connect($hostname,$username,$password,$database); 
?>

<?php

$hostname="localhost";

$username="root";

$password="";

$database="php_dynamic_dependent_dropdown";

$conn = mysqli_connect($hostname,$username,$password,$database);

Create Dynamic Dependent Select Boxes

we need to create the index.php file. after we will create three dropdown boxes(like as country, state, and city) using the bootstrap. we are fetching direct country data from the database and do not use any ajax for the country data. so you can see our example.
index.php

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Dynamic Dependent Drop down in PHP using jQuery AJAX - XpertPhp</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
  <h2>Dynamic Dependent Drop down in PHP using jQuery AJAX</h2>
    <div class="form-group">
      <label for="country">Country:</label>
	  <?php 
			include_once 'config.php'; 
			$query = "SELECT * FROM countries ORDER BY name ASC"; 
			$result = mysqli_query($conn,$query);
		?>
	  <select id="country" name="category_id" class="form-control">
        <option value="" selected disabled>Select Country</option>
         <?php while($row = mysqli_fetch_array($result)){  ?>
         <option value="<?php echo $row['id']; ?>"><?php echo $row['name']; ?></option>
         <?php } ?>
      </select>
    </div>
    <div class="form-group">
      <label for="state">State:</label>
      <select name="state" id="state" class="form-control"></select>
    </div>
	<div class="form-group">
      <label for="city">City:</label>
      <select name="city" id="city" class="form-control"></select>
    </div>
</div>
<script type=text/javascript>
  $('#country').change(function(){
  var countryID = $(this).val();  
  if(countryID){
    $.ajax({
      type:"GET",
      url:"getData.php",
	  data:'country_id='+countryID,
      success:function(res){       
			if(res){
				$("#state").empty();
				$("#state").append('<option>Select State</option>');
				var dataObj = jQuery.parseJSON(res);
				if(dataObj){
					$(dataObj).each(function(){
						$("#state").append('<option value="'+this.id+'">'+this.name+'</option>');
					});
				}else{
					$("#state").empty();
				}
			}else{
				$("#state").empty();
			}
        }
    });
  }else{
    $("#state").empty();
    $("#city").empty();
  }   
  });
  $('#state').on('change',function(){
  var stateID = $(this).val();  
  if(stateID){
    $.ajax({
      type:"GET",
	  url:"getData.php",
	  data:'state_id='+stateID,
      success:function(res){        
			if(res){
				$("#city").empty();
				$("#city").append('<option>Select City</option>');
				var dataObj = jQuery.parseJSON(res);
				if(dataObj){
					$(dataObj).each(function(){
						$("#city").append('<option value="'+this.id+'">'+this.name+'</option>');
					});
				}else{
					$("#city").empty();
				}
			}else{
				$("#city").empty();
			}
      }
    });
  }else{
    $("#city").empty();
  }
  });
</script>
</body>
</html>

<!DOCTYPE html>

<head>

<title>Dynamic Dependent Drop down in PHP using jQuery AJAX - XpertPhp</title>

</head>

<body>

<h2>Dynamic Dependent Drop down in PHP using jQuery AJAX</h2>

<label for="country">Country:</label>

<?php

include_once 'config.php';

$query = "SELECT * FROM countries ORDER BY name ASC";

$result = mysqli_query($conn,$query);

<option value="" selected disabled>Select Country</option>

<?php while($row = mysqli_fetch_array($result)){ ?>

<?php } ?>

</select>

</div>

<label for="state">State:</label>

</div>

</div>

$('#country').change(function(){

var countryID = $(this).val();

if(countryID){

$.ajax({

type:"GET",

url:"getData.php",

data:'country_id='+countryID,

success:function(res){

if(res){

$("#state").empty();

$("#state").append('<option>Select State</option>');

var dataObj = jQuery.parseJSON(res);

if(dataObj){

$(dataObj).each(function(){

$("#state").append('<option value="'+this.id+'">'+this.name+'</option>');

});

}else{

$("#state").empty();

}

}else{

$("#state").empty();

}

});

}else{

$("#state").empty();

$("#city").empty();

}

});

$('#state').on('change',function(){

var stateID = $(this).val();

if(stateID){

$.ajax({

type:"GET",

url:"getData.php",

data:'state_id='+stateID,

success:function(res){

if(res){

$("#city").empty();

$("#city").append('<option>Select City</option>');

var dataObj = jQuery.parseJSON(res);

if(dataObj){

$(dataObj).each(function(){

$("#city").append('<option value="'+this.id+'">'+this.name+'</option>');

});

}else{

$("#city").empty();

}

}else{

$("#city").empty();

}

});

}else{

$("#city").empty();

}

});

</script>

</body>

</html>

Retrieved dependent data from database using jquery ajax

getData.php

<?php
	include_once 'config.php'; 
	if(isset($_GET['country_id'])){
		$query = "SELECT * FROM states WHERE country_id = ".$_GET['country_id']." ORDER BY name ASC";
		$result = mysqli_query($conn,$query);
		$data = array();
		 while($row = mysqli_fetch_array($result)){ 
			$data[] = $row;
         }
		 echo json_encode($data);
	}else if(isset($_GET['state_id'])){
		$query = "SELECT * FROM cities WHERE state_id = ".$_GET['state_id']." ORDER BY name ASC";
		$result = mysqli_query($conn,$query);
		$data = array();
		 while($row = mysqli_fetch_array($result)){ 
			$data[] = $row;
         }
		 echo json_encode($data);
	}
?>