AJAX PHP MySQL Dependent Dropdown List (Step-By-Step Example)

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

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

Download & Notes	Dropdown	Useful Bits & Links
The End

 

 

QUICK NOTES

• Download and unzip into your HTTP folder.
• Create a test database and import 1-cat.sql.
• Change the database settings in 2-lib.php to your own.
• Launch 4-selector.html in the browser. Captain Obvious – Use http:// not file://.

If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

 

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=latin1;
 
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 () {
    try {
      $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
        ]);
    } catch (Exception $ex) { exit($ex->getMessage()); }
  }
 
  // (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", "utf8");
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) The constructor automatically connects to the database when $_CAT = new Category() is created. The destructor closes the database connection.
• (C) There’s just a single function to get the categories from the database.
• (D) Database settings – Change to your own.
• (E) Don’t think this needs explanation…

 

 

PART 3) CATEGORY AJAX HANDLER

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

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 root categories.

 

PART 4) HTML DEPENDENT DROPDOWN

4A) THE HTML

<form onsubmit="return false;">
  <label>Main Category</label>
  <select id="cat1" onchange="upcat(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) UPDATE CATEGORY SELECTOR
// level 1 = main category
// level 2 = sub category
function upcat (level) {
  // (A1) GET HTML SELECTOR
  var selector = document.getElementById("cat" + level);
 
  // (A2) GET PARENT ID
  if (level == 1) { id = 0; }
  else { id = document.getElementById("cat" + (level - 1)).value; }
 
  // (A3) FORM DATA
  var data = new FormData();
  data.append("id", id);
 
  // (A4) AJAX FETCH CATEGORIES
  fetch("3-ajax.php", {
    method: "POST",
    body: data
  })
  .then(res => res.json())
  .then(res => {
    // (A4-1) UPDATE SELECTOR
    selector.innerHTML = "";
    for (let i in res) {
      let opt = document.createElement("option");
      opt.value = i;
      opt.innerHTML = res[i];
      selector.appendChild(opt);
    }
 
    // (A4-2) CASCADE LOAD
    level++;
    if (level<3) { upcat(level); }
  });
}
 
// (B) INIT LOAD
window.onload = () => { upcat(1); };

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

• upcat(1) will AJAX fetch the main categories and populate <select id="cat1">.
• On completion, it will cascade load upcat(2) to populate <select id="cat2">.
• Notice <select id="cat1" onchange="upcat(2)">? Yep, manually changing the main category will also cascade load 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 in the database. For example, ID 10 | PARENT 3 | NAME APPLE PHONES and ID 11 | PARENT 3 | NAME ANDROID PHONES.
• Add <select id="cat3">.
• Attach change listener <select id="cat2" onchange="upcat(3)">.
• In the Javascript, increment the number of levels in (A4-2) – if (level<4).

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