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
Firstly, here is the download link to the example code as promised.
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 – Usehttp://
notfile://
.
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.
DEPENDENT DROPDOWN
All right, let us now get into the example of a dependent dropdown using AJAX, PHP, and MYSQL.
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.
id
The primary key.parent
Parent ID of this category. 0 is a “root category”.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.
USEFUL BITS & LINKS
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 in the database. For example,
ID 10 | PARENT 3 | NAME APPLE PHONES
andID 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
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!