Dynamic dependent select box using jQuery, Ajax, and PHP
Today we’ll explain to you how to create a dynamic dependent select box using jQuery, Ajax, and PHP. Typically, this functionality is used to automatically add dependent data to a dropdown list.
This is a very common functionality that is used in the product selection as per category in the shopping cart or student course selection based on the class. According to the drop-down selection, the dependent data is retrieved from the database and displayed in the next drop-down.
Here we will take an example for country, state, city dropdown to implement a dynamic select box using jQuery, Ajax and PHP. Initially, all countries retrieve from the database and listed in the dropdown. When we select the country, the respective states will be fetched from the database and listed in the state dropdown. Similarly, when the state selects, the respective cities will be fetched from the database and listed in the city dropdown.
Your user interface will look like below.
Output - Dynamic dependent select box using jQuery, Ajax, and PHP - Clue Mediator
Steps to implement the dynamic dependent select box
- Create tables in the database
- Database connection
- Create HTML Select box
- Add jQuery Ajax code
- Create PHP action file
- Output
Let’s start with an example for cascading jQuery AJAX dependent dropdown in the PHP. Refer the following project structure.
File Structure
-
dynamic-dependent-select-box
- action.php
- db_config.php
- index.php
1. Create tables in the database
First of all, we will create three tables in the database for storing country, state and city data.
Table: countries
This table will have two columns – `id` and `country_name`. To create a `countries` table and insert the sample data, run the following sql query.
# Create countries table
CREATE TABLE countries(
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
country_name varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# Inserting data in countries table
INSERT INTO `countries` (`id`, `country_name`) VALUES
(1, 'United State'),
(2, 'India'),
(3, 'United Kingdom');
Table: states
This table will have three columns – `id`, `country_id` and `state_name`. To make the state select box dependent on the country's select box, the `country_id` column will work as a foreign key and will be the same as the `id` in the countries table.
Run the following sql query to create a `states` table and insert the sample data into the table.
# Create states table
CREATE TABLE states(
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
country_id int(11) NOT NULL,
state_name varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# Inserting data in states table
INSERT INTO `states` (`id`, `country_id`, `state_name`) VALUES
(1, 1, 'California'),
(2, 1, 'Florida'),
(3, 2, 'Gujarat'),
(4, 2, 'Rajasthan'),
(5, 3, 'Teignbridge'),
(6, 3, 'South Hams ');
Table: cities
This table will also contain three columns – `id`, `state_id` and `city_name`. Same as the states table, `state_id` will work as a foreign key for this table.
Run the following command to create a `cities` table and insert the sample data.
# Create cities table
CREATE TABLE cities(
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
state_id int(11) NOT NULL,
city_name varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# Inserting data in countries table
INSERT INTO `cities` (`id`, `state_id`, `city_name`) VALUES
(1, 1, 'Alameda'),
(2, 1, 'Calexico'),
(3, 1, 'Belmont'),
(4, 2, 'Bartow'),
(5, 2, 'Lakeland'),
(6, 2, 'Melbourne'),
(7, 3, 'Ahmadabad'),
(8, 3, 'Rajkot'),
(9, 3, 'Surat'),
(10, 4, 'Ajmer'),
(11, 4, 'Bikaner'),
(12, 4, 'Jodhpur'),
(13, 5, 'Dawlish'),
(14, 5, 'Ashburton'),
(15, 5, 'Newton Abbot'),
(16, 6, 'Dartmouth'),
(17, 6, 'Totnes');
Country State City relationship with ERD
This ERD shows the relationship between the countries, states and cities tables
ERD - Clue Mediator
2. Database connection
We have to create a file for database connection named `db_config.php`.
db_config.php
<!--?php
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$db = "demo";
$con = mysqli_connect($dbhost, $dbuser, $dbpass , $db) or die($con);
?-->
3. Create HTML select box
Now, we will create a file named `index.php` where three dropdowns will be shown.
index.php
<!--?php
// Include the database connection file
include('db_config.php');
?-->
<title>Dynamic Dependent Select Box using jQuery, Ajax and PHP - Clue Mediator</title>
<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>
<div class="container">
<h3>Dynamic Dependent Select Box - <a href="https://www.cluemediator.com" target="_blank" rel="noopener noreferrer">Clue Mediator</a></h3>
<br>
<form action="" method="post">
<div class="col-md-4">
<!-- 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>
If you noticed that we have included the jQuery and Bootstrap library in the HTML code. The jQuery will be used to work with Ajax call and Bootstrap library will be used for the styling.
4. Add jQuery Ajax code
Using Ajax we will fetch data (state and city) from the database without reloading the page.
Let’s add the below code in the `index.php` file that sends `country_id` and `state_id` to the server-side script (action.php) via Ajax request when dropdown option value is selected. Get response from the action.php file and display the HTML data to respective dropdown list.
<script type="text/javascript">
$(document).ready(function(){
// Country dependent ajax
$("#country").on("change",function(){
var countryId = $(this).val();
$.ajax({
url :"action.php",
type:"POST",
cache:false,
data:{countryId:countryId},
success:function(data){
$("#state").html(data);
$('#city').html('<option value="">Select city</option>');
}
});
});
// state dependent ajax
$("#state").on("change", function(){
var stateId = $(this).val();
$.ajax({
url :"action.php",
type:"POST",
cache:false,
data:{stateId:stateId},
success:function(data){
$("#city").html(data);
}
});
});
});
</script>
5. Create PHP action file
Now, create a PHP file named `action.php` file. The Ajax request is sent to this PHP file and then retrieves the data such as state list or city list from the database based on the request id (country_id or state_id).
action.php
<!--?php
// Include the database connection file
include('db_config.php');<p-->
if (isset($_POST['countryId']) && !empty($_POST['countryId'])) {
// Fetch state name base on country id
$query = "SELECT * FROM states WHERE country_id = ".$_POST['countryId'];
$result = $con->query($query);
if ($result->num_rows > 0) {
echo '<option value="">Select State</option>';
while ($row = $result->fetch_assoc()) {
echo '<option value="'.$row['id'].'">'.$row['state_name'].'</option>';
}
} else {
echo '<option value="">State not available</option>';
}
} elseif(isset($_POST['stateId']) && !empty($_POST['stateId'])) {
// Fetch city name base on state id
$query = "SELECT * FROM cities WHERE state_id = ".$_POST['stateId'];
$result = $con->query($query);
if ($result->num_rows > 0) {
echo '<option value="">Select city</option>';
while ($row = $result->fetch_assoc()) {
echo '<option value="'.$row['id'].'">'.$row['city_name'].'</option>';
}
} else {
echo '<option value="">City not available</option>';
}
}
?>
6. Output
Run the project and check the output in the browser.
I hope you find this article is helpful.
Thank you for reading. Happy Coding..!! 🙂