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 drop-down list. Based on the drop-down 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 drop-down 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 drop-down 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.
Creating the Database Table
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 PRIMARY KEY AUTO_INCREAMENT, `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 PRIMARY KEY AUTO_INCREAMENT, `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 PRIMARY KEY AUTO_INCREAMENT, `state_id` int(11) NOT NULL, `city_name` varchar(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Creating MYSQL Connection in PHP
For importing and exporting database in MySql will make a separate file ‘dbCongif.php’. Add the following code and replace the database credentials with yours. You can find your db credentials in Application Access details:
<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = "webscodex";
$connection = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if(!$connection){
die('Database connection error : ' .mysql_error());
}
?>Dynamic Dependent Select Options
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 drop-down.
<?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
The action.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.
1. Retrieve the ID from the Ajax request using PHP $_POST method.
2. 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.
3. 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.
4. Render options of the drop-down list.
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.
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.😊
Wow! Nice. Easily understood the whole thing.
Great one…I had been stuck but now I am ok