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

 

 

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

 

 

 

TUTORIAL VIDEO

 

PART 1) DUMMY CATEGORY DATABASE

-- (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

<?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

<?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

<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

// (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.

 

 

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

• Arrow Functions – CanIUse
• Fetch – CanIUse

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

 

LINKS & REFERENCES

• Fetch API – MDN
• Country State City Dependent Dropdown With PHP MySQL – Code Boxx
• Populate Dropdown With AJAX JSON – Code Boxx
• Loading Spinner On HTML Dropdown Box – Code Boxx