How to Create Multi Dependent Dropdowns Using PHP, SQL, and JavaScript

Leave a Comment / Programming Language, PHP / By

Multi-Dependent Dropdown List

Creating dependent dropdowns is a common requirement in web development. It enhances user experience by dynamically filtering options based on previous selections.

This guide will walk you through a simple yet effective method to create Multi Dynamic Dependent Select Box Dropdowns. This solution is straightforward, easy to implement, and perfect for beginners.

What Are Dependent Dropdowns?

Dependent dropdowns are a set of form elements where the options in one dropdown depend on the selection made in another. For example, selecting a country from the first dropdown will filter the cities in the second dropdown, and selecting a city will filter the areas in the third dropdown.

Why Use Dependent Dropdowns?

  • Enhanced User Experience: Provides a seamless and intuitive way for users to find information.
  • Reduced Errors: Limits user input to valid options, reducing mistakes.
  • Dynamic Content: Fetches and displays relevant data based on user selections, improving data accuracy.

How to Create Dynamic Dependent Select Box Dropdown

Step 1: Database Setup

MariaDB included with XAMPP, uses the same SQL syntax as MySQL, so the process for creating tables and inserting data is quite similar. Here’s a step-by-step guide tailored for MariaDB using PHPMyAdmin:

  • Open your web browser and go to http://localhost/phpmyadmin.

Create a New Database

  • Click on the “Databases” tab at the top.
  • In the “Create database” field, enter a name for your database, e.g., geo_data.
  • Click “Create”.

Create Tables

  • Open the newly created database, click on SQL and paste the code below. It will create three tables.
CREATE TABLE countries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE cities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    country_id INT,
    FOREIGN KEY (country_id) REFERENCES countries(id)
);

CREATE TABLE areas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city_id INT,
    FOREIGN KEY (city_id) REFERENCES cities(id)
);

Step 2: Create PHP and HTML Files

db_connection.php

<?php $servername = "localhost";
$username = "root";
$password = "";
$dbname = "geo_data";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

index.php

<?php
include 'db_connection.php';
// Fetch countries
$countries = $conn->query("SELECT * FROM countries");
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Dynamic Dependent Select Box Dropdown with PHP, AJAX and SQL</title>
    <meta name="description" content="Learn how to create dependent dropdowns using PHP, SQL, and JavaScript with easy-to-follow instructions and example code.">
</head>
<body>
    <h1>How to Create Dependent Dropdowns with PHP and JavaScript</h1>
    <form method="post">
        <label for="country">Select Country:</label>
        <select id="country" name="country_id">
            <option value="">Select Country</option>
            <?php while ($row = $countries->fetch_assoc()): ?>
                <option value="<?php echo $row['id']; ?>"><?php echo $row['name']; ?></option>
            <?php endwhile; ?>
        </select>

        <label for="city">Select City:</label>
        <select id="city" name="city_id" disabled>
            <option value="">Select City</option>
        </select>

        <label for="area">Select Area:</label>
        <select id="area" name="area_id" disabled>
            <option value="">Select Area</option>
        </select>
    </form>

    <script>
        document.getElementById('country').addEventListener('change', function() {
            var countryId = this.value;
            var citySelect = document.getElementById('city');
            citySelect.disabled = true;
            citySelect.innerHTML = '<option value="">Select City</option>';

            if (countryId) {
                var xhr = new XMLHttpRequest();
                xhr.open('GET', 'fetch_data.php?type=cities&country_id=' + countryId, true);
                xhr.onload = function() {
                    if (xhr.status === 200) {
                        citySelect.innerHTML += xhr.responseText;
                        citySelect.disabled = false;
                    }
                };
                xhr.send();
            }
        });

        document.getElementById('city').addEventListener('change', function() {
            var cityId = this.value;
            var areaSelect = document.getElementById('area');
            areaSelect.disabled = true;
            areaSelect.innerHTML = '<option value="">Select Area</option>';

            if (cityId) {
                var xhr = new XMLHttpRequest();
                xhr.open('GET', 'fetch_data.php?type=areas&city_id=' + cityId, true);
                xhr.onload = function() {
                    if (xhr.status === 200) {
                        areaSelect.innerHTML += xhr.responseText;
                        areaSelect.disabled = false;
                    }
                };
                xhr.send();
            }
        });
    </script>
</body>
</html>

fetch_data.php

<?php
include 'db_connection.php';

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$type = $_GET['type'];
if ($type == 'cities') {
    $country_id = $_GET['country_id'];
    $sql = "SELECT * FROM cities WHERE country_id = $country_id";
    $result = $conn->query($sql);
    while ($row = $result->fetch_assoc()) {
        echo '<option value="' . $row['id'] . '">' . $row['name'] . '</option>';
    }
} elseif ($type == 'areas') {
    $city_id = $_GET['city_id'];
    $sql = "SELECT * FROM areas WHERE city_id = $city_id";
    $result = $conn->query($sql);
    while ($row = $result->fetch_assoc()) {
        echo '<option value="' . $row['id'] . '">' . $row['name'] . '</option>';
    }
}
$conn->close();

To check the results, we first need to add data to our geo_data database. Open the database “geo_data”, click on SQL, and add these insert statements.

-- Insert countries
INSERT INTO countries (id, name) VALUES
(1, 'United States'),
(2, 'United Kingdom'),
(3, 'Canada'),
(4, 'Australia'),
(5, 'India'),
(6, 'Germany'),
(7, 'France'),
(8, 'Brazil'),
(9, 'Japan'),
(10, 'South Africa');

-- Insert cities
INSERT INTO cities (id, name, country_id) VALUES
(1, 'New York', 1),
(2, 'Los Angeles', 1),
(3, 'Chicago', 1),
(4, 'London', 2),
(5, 'Manchester', 2),
(6, 'Birmingham', 2),
(7, 'Toronto', 3),
(8, 'Vancouver', 3),
(9, 'Montreal', 3),
(10, 'Sydney', 4),
(11, 'Melbourne', 4),
(12, 'Brisbane', 4),
(13, 'Mumbai', 5),
(14, 'Delhi', 5),
(15, 'Bangalore', 5),
(16, 'Berlin', 6),
(17, 'Munich', 6),
(18, 'Hamburg', 6),
(19, 'Paris', 7),
(20, 'Marseille', 7),
(21, 'Lyon', 7),
(22, 'São Paulo', 8),
(23, 'Rio de Janeiro', 8),
(24, 'Brasília', 8),
(25, 'Tokyo', 9),
(26, 'Osaka', 9),
(27, 'Kyoto', 9),
(28, 'Johannesburg', 10),
(29, 'Cape Town', 10),
(30, 'Durban', 10);

-- Insert areas
-- United States
INSERT INTO areas (id, name, city_id) VALUES
(1, 'Manhattan', 1),
(2, 'Brooklyn', 1),
(3, 'Queens', 1),
(4, 'Hollywood', 2),
(5, 'Santa Monica', 2),
(6, 'Venice', 2),
(7, 'Downtown', 3),
(8, 'Lincoln Park', 3),
(9, 'Hyde Park', 3),

-- United Kingdom
(10, 'Westminster', 4),
(11, 'Camden', 4),
(12, 'Greenwich', 4),
(13, 'City Centre', 5),
(14, 'Didsbury', 5),
(15, 'Salford', 5),
(16, 'Edgbaston', 6),
(17, 'Selly Oak', 6),
(18, 'Aston', 6),

-- Canada
(19, 'Downtown', 7),
(20, 'Scarborough', 7),
(21, 'North York', 7),
(22, 'Richmond', 8),
(23, 'Burnaby', 8),
(24, 'Surrey', 8),
(25, 'Old Montreal', 9),
(26, 'Plateau Mont-Royal', 9),
(27, 'Westmount', 9),

-- Australia
(28, 'Bondi', 10),
(29, 'Parramatta', 10),
(30, 'Surry Hills', 10),
(31, 'Carlton', 11),
(32, 'Fitzroy', 11),
(33, 'St Kilda', 11),
(34, 'South Bank', 12),
(35, 'Fortitude Valley', 12),
(36, 'Kangaroo Point', 12),

-- India
(37, 'Andheri', 13),
(38, 'Bandra', 13),
(39, 'Colaba', 13),
(40, 'Connaught Place', 14),
(41, 'Karol Bagh', 14),
(42, 'South Delhi', 14),
(43, 'Whitefield', 15),
(44, 'Koramangala', 15),
(45, 'Indiranagar', 15),

-- Germany
(46, 'Mitte', 16),
(47, 'Kreuzberg', 16),
(48, 'Charlottenburg', 16),
(49, 'Altstadt', 17),
(50, 'Schwabing', 17),
(51, 'Maxvorstadt', 17),
(52, 'St. Pauli', 18),
(53, 'Altona', 18),
(54, 'Eimsbüttel', 18),

-- France
(55, 'Montmartre', 19),
(56, 'Le Marais', 19),
(57, 'Latin Quarter', 19),
(58, 'Old Port', 20),
(59, 'Le Panier', 20),
(60, 'Prado', 20),
(61, 'Presqu\'île', 21),
(62, 'Croix-Rousse', 21),
(63, 'Fourvière', 21),

-- Brazil
(64, 'Jardins', 22),
(65, 'Vila Madalena', 22),
(66, 'Itaim Bibi', 22),
(67, 'Copacabana', 23),
(68, 'Ipanema', 23),
(69, 'Barra da Tijuca', 23),
(70, 'Asa Sul', 24),
(71, 'Asa Norte', 24),
(72, 'Lago Sul', 24),

-- Japan
(73, 'Shinjuku', 25),
(74, 'Shibuya', 25),
(75, 'Ginza', 25),
(76, 'Namba', 26),
(77, 'Umeda', 26),
(78, 'Tennoji', 26),
(79, 'Gion', 27),
(80, 'Arashiyama', 27),
(81, 'Higashiyama', 27),

-- South Africa
(82, 'Sandton', 28),
(83, 'Soweto', 28),
(84, 'Rosebank', 28),
(85, 'Waterfront', 29),
(86, 'Camps Bay', 29),
(87, 'Green Point', 29),
(88, 'Umhlanga', 30),
(89, 'Berea', 30),
(90, 'Morningside', 30);

  1. Countries Table: Contains 10 entries for countries.
  2. Cities Table: Contains cities for each country, referenced by country_id.
  3. Areas Table: Contains areas for each city, referenced by city_id.

You can adapt and expand this dataset by adding more countries, cities, and areas as needed. This setup provides a basic framework for dependent dropdown functionality.

Result:

dynamic-dependent-select-box-dropdown

Download the code

Conclusion:

Creating a dynamic dependent select box dropdown with PHP and SQL can significantly enhance your web forms by providing a dynamic and user-friendly interface. With the simple code provided, you can easily set up these dropdowns on your website, improving user experience and data accuracy.

If you have any questions or need further assistance, feel free to leave a comment below and follow for more!!

What is the Role of JavaScript in Dependent Dropdowns?

JavaScript handles user interactions and makes AJAX requests to fetch and update the dropdown options dynamically. When a user selects an option from one dropdown, JavaScript sends a request to the server to get the related data and updates the other dropdowns

Related Posts

Leave a Comment

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

Subscribe
Subscribe