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 user-friendly & 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 & MySQL
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 it 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 to 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
Folder Structure –
Create the project folder in 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
Create a table countries to store country name using the following query
Table Name – countries
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.
Table Name – states
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.
Table Name – cities
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
Get the countries, states & cities SQL file through the following URL and import it 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.
countriesmust have a relation with thestatestable.statesmust have a relation with thecitiestable.
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, 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.jsjust 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=""></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
changeevent on the country dropdown field id#country. - Get the value of the country dropdown field and assign it to variable
countryID. - If
countryIDhas a value, configure the following points.- Assign a backend script file
backend-script.phpto get dependent data through the post method. - If dependent data will be fetched successfully, display these data in the city dropdown.
- Assign a backend script file
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
changeevent on state dropdown field id#state. - Get the value of the State dropdown field and assign it to variable
stateID. - If
stateIDhas a value, configure the following points.- Assign a backend script file
backend-script.phpto get dependent data through the post method. - If dependent data will be fetched successfully, display these data in the city dropdown.
- Assign a backend script file
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 - Fetch State Name based on Country Name Id
- Fetch City Name Base on State Name Id
File Name – backend-script.php
<?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>";
}
}
}
?>
My 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.