Dynamic dependent select box using jQuery, Ajax, and PHP

Saturday , 8, May 2021 Leave a comment

Now we’ are Create Dynamic dependent select box using jQuery Ajax, , PHP and SQL. Typically, this is using for automatically add dependent data to a dropdown list without page load.

It is very common feature of website that is used in the selection of country according to the list of drop-down. if you Select any country you can see state of this country ,and after select state you can see city of this state .

Your user interface will look like below.

Steps to implement the dynamic dependent select box

  1. Create tables in the database
  2. Create HTML Select box
  3. jQuery Ajax
  4. Create PHP load files

1. Create tables in the database

First of all, we will create database and run SQL code or create three tables in the database for insert country, state and city data.

Table: countray

This city table will have two columns – id and name. To create a countray table run the following SQL query

CREATE TABLE `countray` (
  `id` int(11) NOT NULL,
  `countray` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `countray`
--

INSERT INTO `countray` (`id`, `countray`) VALUES
(1, 'Bangladesh'),
(2, 'India'),
(3, 'Pakistan');

Table: state

This city table will have three columns – id, state , countray_name. To create the city select box dependent on the country’s select box, run the following SQL query.

CREATE TABLE `state` (
  `id` int(11) NOT NULL,
  `state` text NOT NULL,
  `countray_name` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `state`
--

INSERT INTO `state` (`id`, `state`, `countray_name`) VALUES
(1, 'Dhaka', 'Bangladesh'),
(2, 'Mymensingh', 'Bangladesh'),
(3, 'Kolkata', 'India'),
(4, 'Dilhi', 'India'),
(5, 'Karachi', 'Pakistan'),
(6, 'Islamabad', 'Pakistan');

Table: citty

This city table will have three columns – id, city, state_name . To create the city select box dependent on the state select box, run the following SQL query.

CREATE TABLE `city` (
  `id` int(11) NOT NULL,
  `city` text NOT NULL,
  `state_name` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `city`
--
INSERT INTO `city` (`id`, `city`, `state_name`) VALUES
(0, 'Mymensingh city', 'Mymensingh'),
(0, 'Dhaka cty', 'Dhaka'),
(0, 'Kolkata city', 'Kolkata'),
(0, 'Dilhi city', 'Dilhi'),
(0, 'Karachi city', 'Karachi'),
(0, 'Islamabad city', 'Islamabad');

2. Create HTML select box

create a file named index.php where create two dropdowns box like this or copy it.

<?php 
require_once('DB_conntect.php');
$db_handle = new DBcontrroler();
$query = "SELECT * FROM `countray`";
$result = $db_handle->runQuery($query);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>ajax</title>
    <link rel="stylesheet" href="css/bootstrap.min.css">

    <script src="js/jquery.js"></script>
    <script src="js/bootstrap.min.js"></script>
    <script src="js/main.js"></script>
</head>
<body>
<div class="container">
    <div>
        <h1 class="text-center" >Document dropdown list ,countray ,atate ,city select</h1>
    </div>

    <div class="form-group">
        <label for="contray">countray</label>
        <select class="form-control" name="" id="contray">
            <option value="">select your countray</option>
            <?php foreach($result as $content){ ?>
                <option value="<?= $content['countray']; ?>"><?= $content['countray']; ?></option> 
            <?php } ?>
        </select>
    </div>
    <div class="form-group">
        <label for="state">state</label>
        <select class="form-control" name="" id="state">
        <option  value="">select your state</option>
        </select>
    </div>
    <div class="form-group">
        <label for="city">city</label>
        <select class="form-control" name="" id="city">
            <option value="">select your city</option>
        </select>
    </div>
</div>
</body>
</html>

we have included the jQuery and Bootstrap library in the HTML code. but not Use any extra design. The jQuery will be used to work with Ajax call

3.jQuery Ajax

we will fetch data Using Ajax (state and city) from the database without reloading the page.

Save the code below to create main.js file

jQuery(document).ready(function($){
    $("#contray").change(function(){
        $.ajax({
            type:'post',
            url: 'getState.php',
            data: {
                countray_name: $("#contray").val(),
            },
            success: function(content){
                document.getElementById("state").innerHTML = content;
            }
        });
    });
    $("#state").change(function(){
        $.ajax({
            type:'post',
            url: 'getCity.php',
            data: {
                state_name: $("#state").val(),
            },
            success: function(content){
                document.getElementById("city").innerHTML = content;
            }
        });
    });
})

4. Create PHP load files

create a file for database connection named DB_conntect.php.

<?php 
class DBcontrroler{
    public function DB(){
        return  mysqli_connect('localhost','root','','dropdown');
    }
    public function runQuery($query){
        
        $result = mysqli_query($this->DB(),$query);
        while($content = mysqli_fetch_assoc($result)){
            $resultset[] = $content;
        }
        if(!empty($resultset))
            return $resultset;     
    }
    public function nurRows($jquery){
        $result = mysqli_query($this->DB(),$query);
       $content_count = mysqli_num_rows($result);
            return $content_count;
    }
}
?>

The Ajax request is sent to getState.php and then retrieves State data from Database

below code to collect State data from database , create getState.php and copy the code:

<?php 
require_once('DB_conntect.php');
$coutray_name = $_POST['countray_name'];
if( ! empty($coutray_name)){
    $db_handle = new DBcontrroler();
    $query = "SELECT * FROM `state` WHERE `countray_name` = '$coutray_name'";
    $result = $db_handle->runQuery($query);
?>
<option  value="">select your state</option>
<?php
    foreach($result as $con_state){
    ?>
    <option  value="<?= $con_state['state']; ?>"><?= $con_state['state']; ?></option>
    
    <?php } 
}else{
?>
<option  value="">select your state</option>
<?php } ?>

below code to collect city data from database , after change in State dropdown select box . you can see dependent city of this State.

create getCity.php and insert the code:

<?php 
require_once('DB_conntect.php');
$state_name = $_POST['state_name'];
if( ! empty($state_name)){
    $db_handle = new DBcontrroler();
    $query = "SELECT * FROM `city` WHERE `state_name` = '$state_name'";
    $result = $db_handle->runQuery($query);
    ?>
    <option  value="">select your city</option>
    <?php

    foreach($result as $con_state){
    ?>
    <option  value="<?= $con_state['city']; ?>"><?= $con_state['city']; ?></option>
    
    <?php } 
}else{
?>
<option  value="">select your city</option>
<?php } ?>

Source Code

Tags:, ,

Leave a Reply

Your email address will not be published. Required fields are marked *