Dynamic Dependent Drop down in PHP using jQuery AJAX


Dynamic Dependent Drop down in PHP using jQuery AJAX
Hello Friends, in this post we will discuss how we can create Dynamic dependent drop down country, state and city in php using jQuery AJAX without refreshing the page.
The dynamic dependent select box is used to automatically add dependent data to the dropdown list. Based on the dropdown options, the dependent data is retrieved from the database and displayed in the next select option field. In general, the field of dynamic dependency select option is used to implement the country,state and city functionality. Using Ajax and PHP, you can easily implement dynamically dependent dropdown without refreshing the page.
Dynamically dependent drop-down is very useful for retrieving dynamic relational data from databases and listed in selected fields. In this tutorial, we will show you how to implement a relational dropdown country, state and city using jQuery, Ajax, PHP, and MySQL. This means that the state is related to the country and the city is related to the state. Based on changing of country name and state name, respective state and city is retrieve from the database without reloading/refreshing page using jQuery, Ajax, PHP, and MySQL.
Here we are using three file for Insert data in MySql database using Ajax.
  • dbConfig.php
  • index.php
  • action.php

Creating Database Tables

Three tables are required in the database to store countries, states, and cities data. There will also be relationship between countries, states and cities tables. The states table has a relationship with the countries table and cities table has a relationship with the states table.
Create a countries table in the Mysql database.
--
-- Table structure for table `countries`
--
CREATE TABLE `countries` (
`id` int(11) NOT NULL,
`country_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Create a states table with foreign key country_id fields in Mysql database.
--
-- Table structure for table `states`
--
CREATE TABLE `states` (
`id` int(11) NOT NULL,
`country_id` int(11) NOT NULL,
`state_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Create a states table with foreign key state_id fields in Mysql database.
--
-- Table structure for table `cities`
--
CREATE TABLE `cities` (
`id` int(11) NOT NULL,
`state_id` int(11) NOT NULL,
`city_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Database connectivity (dbConfig.php).

<?php

// create database connectivity
$servername = "localhost";
$username = "root";
$password = "";
$database = "testing";
// Create connection
$con = new mysqli($servername, $username, $password, $database);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
?>

 

Drop-down Dependent Select option(index.php)

First, all country data is retrieve from the database and listed in the Country drop-down. once value is selected in the Country drop-down, the corresponding data is listed in the State and City dropdown.
<?php 
// Include the database config file
include_once 'dbConfig.php';
?>

<!DOCTYPE html>
<html lang="en">
<head>
<title>Dynamic Dependent Drop down in PHP using jQuery AJAX</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body style="background: #fdfcfc">
<div class="container">
<h2 style="text-align: center;">Dynamic Dependent Drop down in PHP using jQuery AJAX</h2><br><br>
   <form action="" method="post">
<div class="col-md-3"></div>
<div class="form-group col-md-6">
<!-- Country dropdown -->

<label for="country">Country</label>
<select class="form-control" id="country">
<option value="">Select Country</option>
<?php
$query = "SELECT * FROM countries";
$result = $con->query($query);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
         echo "<option value='{$row["id"]}'>{$row['country_name']}</option>";
 }
}else{
echo "<option value=''>Country not available</option>";
            }
?>

</select><br>

<!-- State dropdown -->
<label for="country">State</label>
<select class="form-control" id="state">
<option value="">Select State</option>
</select><br>

<!-- City dropdown -->
<label for="country">City</label>
<select class="form-control" id="city">
<option value="">Select City</option>
</select>
</div>
</form>
</div>
</body>
</html>

jQuery AJAX Code:

Initiates an Ajax request to retrieve dependent data (state and city) from the database without reloading the page with jQuery.
  1. when changing the value of country drop-down.
  • POST of the country Id selected in the server-side script (action.php) via an Ajax request.
  • The select options is fetch from the response and HTML is placed to the  state dependent drop-down.
     2.when changing the value of states drop-down.
  • POST of the stated Id selected in the server-side script (action.php) via an Ajax request.
  • The select options is fetch from the response and HTML is placed to the  state dependent drop-down.

<script type="text/javascript">
   $(document).ready(function(){
// Country dependent ajax
   $("#country").on("change",function(){
   var countryId = $(this).val();
   if (countryId) {
$.ajax({
     url :"action.php",
type:"POST",
cache:false,
data:{countryId:countryId},
success:function(data){
$("#state").html(data);
$('#city').html('<option value="">Select state</option>');
}
});
}else{
$('#state').html('<option value="">Select country</option>');
$('#city').html('<option value="">Select state</option>');
}
});

// state dependent ajax
$("#state").on("change", function(){
var stateId = $(this).val();
if (stateId) {
$.ajax({
url :"action.php",
type:"POST",
cache:false,
data:{stateId:stateId},
success:function(data){
$("#city").html(data);
}
});
}else{
$('#city').html('<option value="">Select state</option>');
}
});
});
</script>

   

Retrieve Dependent Data from database(action.php)

<?php 
    // Include the database config file
include_once 'dbConfig.php';

// Get country id through state name

    $countryId = $_POST['countryId'];

if (!empty($countryId)) {
// Fetch state name base on country id
$query = "SELECT * FROM states WHERE country_id = {$countryId}";

$result = $con->query($query);

if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
        echo '<option value="'.$row['id'].'">'.$row['state_name'].'</option>';
}
}else{
    echo '<option value="">State not available</option>';
}
}elseif (!empty($_POST['stateId'])) {
$stateId = $_POST['stateId'];
// Fetch city name base on state id

$query = "SELECT * FROM cities WHERE state_id = {$stateId}";

$result = $con->query($query);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
       echo '<option value="'.$row['id'].'">'.$row['city_name'].'</option>';
    }
}else{
    echo '<option value="">City not available</option>';
}
    }

?>

Conclusion

The dynamically dependent select box is a very useful element if you want to allow users to select values ​​from various drop-down lists. In the code sample, we show the selected fields that depend dynamically for the city status drop-down in PHP. You can easily extend functionality that dynamically depends on the chosen field and implement the “Ajax State City” drop-down status with PHP and MySQL.
Dynamic Dependent Drop down in PHP using jQuery AJAX, Dynamic dependent drop down select on change jquery AJAX,Dynamically depende
You can always support by sharing on social media or recommending my blog to your friends and colleagues.    
If you have any suggestions / problems about this tutorial, please comment on the  form below.😊


Leave a Reply

This Post Has One Comment