AJAX PHP MySQL Dependent Dropdown List (Simple Example)

Welcome to a tutorial on how to create a dependent dropdown list using AJAX, PHP, and MySQL. Loading dropdown options from the database are pretty easy, but how do we “link” many of these together? Let us walk through an example – Read on!

 

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Here is the download link to the example code, so you don’t have to copy-paste everything.

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

DEPENDENT DROPDOWN

All right, let us now get into the example of a dependent dropdown using AJAX, PHP, and MYSQL.

 

TUTORIAL VIDEO

 

PART 1) DUMMY CATEGORY DATABASE

1-cat.sql
-- (A) CATEGORY TABLE
CREATE TABLE `category` (
  `id` bigint(20) NOT NULL,
  `parent` bigint(20) NOT NULL DEFAULT 0,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `category`
  ADD PRIMARY KEY (`id`),
  ADD KEY `parent` (`parent`),
  ADD KEY `name` (`name`);
 
ALTER TABLE `category`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
 
-- (B) DUMMY DATA
INSERT INTO `category` (`id`, `parent`, `name`) VALUES
(1, 0, 'Electronics'),
(2, 0, 'Sports'),
(3, 1, 'Mobile'),
(4, 1, 'Tablet'),
(5, 1, 'Laptop'),
(6, 1, 'Desktop'),
(7, 2, 'Jogging'),
(8, 2, 'Swimming'),
(9, 2, 'Cycling');

First, let us start with a dummy database to work with. This is nothing but a simple list of categories.

  1. id The primary key.
  2. parent Parent ID of this category. 0 is a “root category”.
  3. name The category name.

For those who are lost:

  • Electronics and sports are the main categories here.
  • Mobile, tablet, laptop, desktop are sub-categories of electronics.
  • Jogging, swimming, cycling are sub-categories of sports.

 

 

PART 2) PHP CATEGORY LIBRARY

2-lib.php
<?php
class Category {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = null;
  function __construct () {
    $this->pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
  }
 
  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }
 
  // (C) GET BY PARENT CATEGORY
  function get ($pid) {
    $this->stmt = $this->pdo->prepare("SELECT * FROM `category` WHERE `parent`=?");
    $this->stmt->execute([$pid]);
    $results = [];
    while ($row = $this->stmt->fetch()) { $results[$row["id"]] = $row["name"]; }
    return $results;
  }
}
 
// (D) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");
 
// (E) NEW CATEGORY OBJECT
$_CAT = new Category();

Next, we need a PHP library to work with the database. This may seem confusing at first, but keep calm and look carefully.

  • (A, B, E) The constructor automatically connects to the database when $_CAT = new Category() is created. The destructor closes the database connection.
  • (C) get() The only “useful function” to get the categories from the database.
  • (D) Database settings – Change to your own.

 

 

PART 3) CATEGORY AJAX LOADER

3-ajax.php
<?php
// (A) LOAD LIBRARY
require "2-lib.php";
 
// (B) GET CATEGORIES
$id = isset($_POST["id"]) ? $_POST["id"] : 0 ;
echo json_encode($_CAT->get($id));

Of course, the library is not going to do anything by itself. So here is a simple AJAX handler to fetch the categories from the database, and output them in a JSON encoded string. For those who are lost once again – Just send $_POST["id"] to this script to get the categories. For example, $_POST["id"] = 0 will output all the main categories.

 

PART 4) HTML DEPENDENT DROPDOWN

4A) THE HTML

4a-selector.html
<form onsubmit="return false;">
  <label>Main Category</label>
  <select id="cat1" onchange="loadcat(2)"></select>
 
  <label>Sub Category</label>
  <select id="cat2"></select>
</form>

Finally, the HTML page itself. Nothing special here, just two <select> for the main and sub-categories.

 

 

4B) THE JAVASCRIPT

4b-selector.js
// (A) LOAD CATEGORY SELECTOR
// level 1 = main category
// level 2 = sub category
function loadcat (level) {
  // (A1) GET SELECTED PARENT ID
  var data = new FormData();
  data.append("id", (level==1 ? 0 : document.getElementById("cat1").value));
 
  // (A2) AJAX FETCH CATEGORIES
  fetch("3-ajax.php", { method: "POST", body: data })
  .then(res => res.json())
  .then(cat => {
    // (A2-1) UPDATE HTML SELECTOR
    let selector = document.getElementById("cat" + level);
    selector.innerHTML = "";
    for (let i in cat) {
      let opt = document.createElement("option");
      opt.value = i;
      opt.innerHTML = cat[i];
      selector.appendChild(opt);
    }
 
    // (A2-2) CASCADE LOAD SUB-CATEGORY
    if (level==1) { loadcat(2); }
  });
}
 
// (B) INIT LOAD
window.onload = () => loadcat(1);

Not going to explain this line-by-line, but essentially:

  • (B) loadcat(1) will run on window load. This will fetch the main categories and populate <select id="cat1">.
  • (A2-2) On completion, it will cascade load loadcat(2) to populate <select id="cat2">.
  • (HTML) Notice <select id="cat1" onchange="loadcat(2)">? Yep, changing the main category will also trigger this to reload the sub-category.

 

EXTRAS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

WHAT IF THERE ARE MORE LEVELS?

What if there’s a sub-sub category? Or even a sub-sub-sub category? Very simply –

  • Add your sub-sub entries to the database. For example, sub-sub-categories for “mobile”.
    • ID: 10, PARENT: 3, NAME: Apple Phones
    • ID: 11, PARENT: 3, NAME: Android Phones
  • Add <select id="cat3">.
  • Attach change listener <select id="cat2" onchange="loadcat(3)">.
  • Change the Javascript:
    • (A1) Get the respective selected category – data.append("id", document.getElementById("cat" + (level-1)).value))
    • (A2-2) Update the cascade load – level++; if (level<4) { loadcat(level); }.

The end.

 

COMPATIBILITY CHECKS

This example will work on all modern “Grade A” browsers.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

4 thoughts on “AJAX PHP MySQL Dependent Dropdown List (Simple Example)”

  1. Hi every body i want to Know how to save the the dependent dropdown list to a table in database because i have a form that contain dependent dropdown list and textbox help me please

    1. thanks for your reply but i want to save the values get from the dependent dropdown list and save or insert into database table

Leave a Comment

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