Reading Time: 8 minutes

2,916 Views

Inside this article we will see the concept of PHP MySQLi Dynamic Dependent Dropdown Using jQuery Ajax. We will have dropdowns which is connected with the previous selected value.

For example – Country (Dropdown) >> State (Dropdown) >> City (Dropdown)

This tutorial will help you to understand to handle dynamic dependent dropdowns using jquery ajax in PHP MySQLi. Also it will be super easy to implement in your code as well.

We will cover the concept of Ajax request, reading parameter from query string of URL in PHP MySQLi too in this dropdown tutorial.

Learn More –

Let’s get started.

Create Database & Table

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE php_applications;

Inside this database, we need to create tables. Tables we need – countries, states, cities.

Dummy Data for Application

Here, you need to copy mysql query from this given file and run into sql tab of phpmyadmin. First download it and run.

It will create tables and their data.

Download Data – Countries, States, Citites

You will see something like this after this test data insertion.

“countries” table

“states” table

“cities” table

Application Folder Structure

You need to create a folder structure to develop this application in PHP and MySQLi. Have a look the files and folders inside this application –

Create a folder with name dependent-dropdown and create these 3 files into it.

Database Configuration

Open dbconfig.php file from application. Add these lines of code into it.

<?php
// Database configuration
$host   = "localhost";
$dbuser = "admin";
$dbpass = "Admin@123";
$dbname = "php_applications";

// Create database connection
$conn = new mysqli($host, $dbuser, $dbpass, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

Frontend Dropdown Layout

Open index.php file from application. Add these lines of code into it.

<?php
// Include the functions file
require 'functions.php';
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <title>PHP MySQLi Dynamic Dependent Dropdown 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>
</head>

<body>

    <div class="container">
        <h4>PHP MySQLi Dynamic Dependent Dropdown using jQuery Ajax</h4>
        <div class="panel panel-primary">
            <div class="panel-heading">PHP MySQLi Dynamic Dependent Dropdown using jQuery Ajax</div>
            <div class="panel-body">
                <div class="form-group">
                    <label for="country">Country:</label>
                    <select id="country" name="country" class="form-control">
                        <option value="" selected disabled>Select Country</option>
                        <?php foreach ($countries as $key => $country) { ?>
                            <option value="<?= $country['id'] ?>"> <?= $country['name'] ?></option>
                        <?php } ?>
                    </select>
                </div>
                <div class="form-group">
                    <label for="state">State:</label>
                    <select name="state" id="state" class="form-control"></select>
                </div>
                <div class="form-group">
                    <label for="city">City:</label>
                    <select name="city" id="city" class="form-control"></select>
                </div>
            </div>
        </div>
    </div>
    <script type=text/javascript>
        // when country dropdown changes
        $('#country').change(function() {

            var countryID = $(this).val();

            if (countryID) {

                $.ajax({
                    type: "POST",
                    url: "functions.php",
                    data: {
                        country_id: countryID,
                        type: "get_states"
                    },
                    success: function(res) {
                        var data = JSON.parse(res);
                        if (res) {

                            $("#state").empty();
                            $("#state").append('<option>Select State</option>');
                            $.each(data.data, function(key, value) {
                                $("#state").append('<option value="' + value.id + '">' + value.name +
                                    '</option>');
                            });

                        } else {

                            $("#state").empty();
                        }
                    }
                });
            } else {

                $("#state").empty();
                $("#city").empty();
            }
        });

        // when state dropdown changes
        $('#state').on('change', function() {

            var stateID = $(this).val();

            if (stateID) {

                $.ajax({
                    type: "POST",
                    url: "functions.php",
                    data: {
                        state_id: stateID,
                        type: "get_cities"
                    },
                    success: function(res) {
                        var data = JSON.parse(res);
                        if (res) {
                            $("#city").empty();
                            $("#city").append('<option>Select City</option>');
                            $.each(data.data, function(key, value) {
                                $("#city").append('<option value="' + value.id + '">' + value.name +
                                    '</option>');
                            });

                        } else {

                            $("#city").empty();
                        }
                    }
                });
            } else {

                $("#city").empty();
            }
        });
    </script>
</body>

</html>

Ajax Handler & Functions

Open functions.php file. Add these lines of it.

<?php

// Include the database configuration file 
require 'dbconfig.php';

$countrySelect = $conn->prepare("SELECT * FROM countries");
$countrySelect->execute();
$countries = $countrySelect->get_result();

// To read ajax
$type = isset($_REQUEST['type']) && !empty($_REQUEST['type']) ? $_REQUEST['type'] : "";

if (!empty($type)) {

    if ($type == "get_states") {

        $country_id = isset($_REQUEST['country_id']) && !empty($_REQUEST['country_id']) ? $_REQUEST['country_id'] : "";
        $stateSelect = $conn->prepare("SELECT * FROM states WHERE country_id = ?");
        $stateSelect->bind_param("i", $country_id);
        $stateSelect->execute();
        $states = $stateSelect->get_result();

        $itemRecords = array();
        while ($item = $states->fetch_assoc()) {
            extract($item);
            $itemDetails = array(
                "id" => $id,
                "name" => $name
            );
            array_push($itemRecords, $itemDetails);
        }

        echo json_encode(array(
            "status" => true,
            "data" => $itemRecords
        ));

        die;
    } else if ($type == "get_cities") {

        $state_id = isset($_REQUEST['state_id']) && !empty($_REQUEST['state_id']) ? $_REQUEST['state_id'] : "";
        $citySelect = $conn->prepare("SELECT * FROM cities WHERE state_id = ?");
        $citySelect->bind_param("i", $state_id);
        $citySelect->execute();
        $cities = $citySelect->get_result();

        $itemRecords = array();
        while ($item = $cities->fetch_assoc()) {
            extract($item);
            $itemDetails = array(
                "id" => $id,
                "name" => $name
            );
            array_push($itemRecords, $itemDetails);
        }

        echo json_encode(array(
            "status" => true,
            "data" => $itemRecords
        ));

        die;
    }
}

Application Testing

Now,

URL: http://localhost/dependent-dropdown/index.php

Download Complete Source Code

Download Source Code

We hope this article helped you to PHP MySQLi Dynamic Dependent Dropdown using jQuery Ajax in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.