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!

ⓘ 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

 

 

QUICK NOTES

• Create a test database and import 1-db.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.

 

SCREENSHOT

 

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

 

TUTORIAL VIDEO