Create Dynamic Hierarchical Lists with PHP and MySQL on Ubuntu 20.04
Introduction
A nested category is a hierarchical list that can support sub-categories up to the nth level. Usually, you can use the list when designing applications such as e-commerce websites, accounting systems, animal kingdoms, geospatial data, and more. To better understand this model, think about a corporate ladder. At the top, you have the CEO. Then, there can be several departmental heads reporting to them. Then, from each department, you can have thousands of employees under the supervisory teams. Another great example is the Linux file system that uses multi-level directory hierarchies.
In most cases, you'll code a dynamic nested multi-level category with a server-side scripting language such as Python, Node.js, or PHP and not at the database level. However, your script should pull data from a relational database such as MySQL. While creating the hierarchical list might seem simple, you need to achieve this functionality with a recursive function.
In this guide, you'll set up a sample database with some product categories organized in a table and linked by a parent_id column. Then, you'll use a recursive PHP function to retrieve the categories and organize them in a tree structure. Your final list will be dynamic in nature. That is, you can insert or remove entries from the list without affecting the order of other items.
Prerequisites
To complete this PHP multi-level hierarchical list tutorial, make sure you have the following:
- An Ubuntu 20.04 server.
- A sudo user.
- A Lamp Stack.
1. Set Up a Sample Database
To maintain the hierarchy of your categories, you need to define a database that stores the connected data through a linked table. SSH to your server and log in to MySQL as root.
$ sudo mysql -u root -pNext, enter the root password for your MySQL server and press Enter to proceed. Then, issue the command below to create a sample store_db database and privileged store_db_user user. Replace EXAMPLE_PASSWORD with a strong value.
mysql> CREATE DATABASE store_db;
CREATE USER 'store_db_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
GRANT ALL PRIVILEGES ON store_db.* TO 'store_db_user'@'localhost';
FLUSH PRIVILEGES;Confirm the output below to make sure you've successfully created the database and user.
...
Query OK, 0 rows affected (0.01 sec) Next, run the MySQL USE statement to switch to the new store_db database.
mysql> USE store_db;Ensure that you've selected the new database by confirming the output below.
Database changedNext, you'll set up a table to store the products categories. In this table, each entry contains a category_id which is a unique PRIMARY KEY defined as BIGINT. Then, the category_name column stores a human-readable string for your category(For instance, SHOES, T-SHIRTS, and COMPUTERS) in a VARCHAR(50) data type. Then, the parent_id links the sub-categories to the main categories.
Create the product_categories table by executing the following statement.
mysql> CREATE TABLE products_categories (
category_id BIGINT PRIMARY KEY,
category_name VARCHAR(50),
parent_id INT
) ENGINE = InnoDB;Verify the output below to make sure you've set up the table.
Query OK, 0 rows affected (0.03 sec)Once the store_db database and product_categories table are in place, you'll insert some sample records for testing purposes.
2. Insert Products Categories in a Database Table
In this sample application, you'll assume a hierarchy of data similar to the output shown below.
SHOES
--------OFFICE SHOES
--------CASUAL SHOES
--------SAFETY BOOTS
T-SHIRTS
--------POLO NECK
--------V-NECK
COMPUTERS
--------DELL
------------DESKTOPS
------------LAPTOPS
--------HP
------------NOTEBOOKS
------------WORKSTATIONSIn the above list, you've 3 main categories. That is, SHOES, T-SHIRTS, and COMPUTERS. Then each category has one or several levels of sub-categories. For instance, in the COMPUTERS category, you've DELL and HP as the sub-categories. In addition, you've further categorized DELL to DESKTOPS and LAPTOPS, and HP to NOTEBOOKS and WORKSTATIONS.
To create a database schema that will preserve and store the relations of the hierarchical list above, you'll need to enter the value of the parent_id column in each sub-category that relates to a parent category similar to the following output.
+-------------+---------------+-----------+
| category_id | category_name | parent_id |
+-------------+---------------+-----------+
| 1 | SHOES | 0 |
| 2 | T-SHIRTS | 0 |
| 3 | COMPUTERS | 0 |
| 4 | OFFICE SHOES | 1 |
| 5 | CASUAL SHOES | 1 |
| 6 | SAFETY BOOTS | 1 |
| 7 | POLO NECK | 2 |
| 8 | V-NECK | 2 |
| 9 | DELL | 3 |
| 10 | DESKTOPS | 9 |
| 11 | LAPTOPS | 9 |
| 12 | HP | 3 |
| 13 | NOTEBOOKS | 12 |
| 14 | WORKSTATIONS | 12 |
+-------------+---------------+-----------+To do this, insert the following records into the products_categories table by executing the following commands:
Begin by inserting the main categories.
mysql> INSERT INTO products_categories(category_id, category_name, parent_id) VALUES (1, 'SHOES', 0); INSERT INTO products_categories(category_id, category_name, parent_id) VALUES (2, 'T-SHIRTS', 0); INSERT INTO products_categories(category_id, category_name, parent_id) VALUES (3, 'COMPUTERS', 0);Then, enter the
3SHOESsub-categories. Please note: For these sub-categories, you're using1as the value for theparent_idcolumn which relates back to theSHOEScategory.mysql> INSERT INTO products_categories(category_id, category_name, parent_id) VALUES (4, 'OFFICE SHOES', 1); INSERT INTO products_categories(category_id, category_name, parent_id) VALUES (5, 'CASUAL SHOES', 1); INSERT INTO products_categories(category_id, category_name, parent_id) VALUES (6, 'SAFETY BOOTS', 1);Next, enter the
2T-SHIRTSsub-categories. Similarly, you're using2as the value of theparent_idcolumn to link the records to theT-SHIRTScategory.mysql> INSERT INTO products_categories(category_id, category_name, parent_id) VALUES (7, 'POLO NECK', 2); INSERT INTO products_categories(category_id, category_name, parent_id) VALUES (8, 'V-NECK', 2);Then, insert
DELLas a sub-category with acategory_idof9under theCOMPUTERSparent category. Next, create two more sub-categories underDELLand remember to change the value of theirparent_idsto9.mysql> INSERT INTO products_categories(category_id, category_name, parent_id) VALUES (9, 'DELL', 3); INSERT INTO products_categories(category_id, category_name, parent_id) VALUES (10, 'DESKTOPS', 9); INSERT INTO products_categories(category_id, category_name, parent_id) VALUES (11, 'LAPTOPS', 9);Finally, create an
HPsub-category with acategory_idof12still under the main parentCOMPUTERScategory. Then, insertNOTEBOOKSandWORKSTATIONSsub-categories underHP.mysql> INSERT INTO products_categories(category_id, category_name, parent_id) VALUES (12, 'HP', 3); INSERT INTO products_categories(category_id, category_name, parent_id) VALUES (13, 'NOTEBOOKS', 12); INSERT INTO products_categories(category_id, category_name, parent_id) VALUES (14, 'WORKSTATIONS', 12);
After executing each INSERT command, you should receive the following output.
Query OK, 1 row affected (0.00 sec)Use the MySQL SELECT statement to confirm the records from the products_categories table.
mysql> SELECT
category_id,
category_name,
parent_id
FROM products_categories;You should now see the following output.
+-------------+---------------+-----------+
| category_id | category_name | parent_id |
+-------------+---------------+-----------+
| 1 | SHOES | 0 |
| 2 | T-SHIRTS | 0 |
| 3 | COMPUTERS | 0 |
| 4 | OFFICE SHOES | 1 |
| 5 | CASUAL SHOES | 1 |
| 6 | SAFETY BOOTS | 1 |
| 7 | POLO NECK | 2 |
| 8 | V-NECK | 2 |
| 9 | DELL | 3 |
| 10 | DESKTOPS | 9 |
| 11 | LAPTOPS | 9 |
| 12 | HP | 3 |
| 13 | NOTEBOOKS | 12 |
| 14 | WORKSTATIONS | 12 |
+-------------+---------------+-----------+
14 rows in set (0.00 sec)Exit from the MySQL command-line interface.
mysql> QUIT;Output.
Bye You've now formatted and inserted your sample hierarchical data into your database table. Next, you'll code a PHP script that uses a recursive function to loop through the records and create a nested list.
3. Create a Nested List with PHP Recursive Function
In programming, you can't create a nested list by utilizing a linear function that only executes a loop statement once. Instead, you must implement a recursive process. That is, you must come up with a routine that calls itself either directly or indirectly.
To put things in a better perspective, you should code a function that fetches parent or base product categories from a database. Then, after retrieving each parent record, the script should loop recursively to obtain the sub-categories up to the nth level.
To do this, use nano to create a new /var/www/html/products_categories.php file.
$ sudo nano /var/www/html/products_categories.phpThen, enter the information below into the /var/www/html/products_categories.php file. Replace EXAMPLE_PASSWORD with the correct value.
<?php
function getPdo(){
try {
$db_name = 'store_db';
$db_user = 'store_db_user';
$db_password = 'EXAMPLE_PASSWORD';
$db_host = 'localhost';
$pdo = new PDO('mysql:host=' . $db_host . '; dbname=' . $db_name, $db_user, $db_password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $pdo;
} catch (PDOException $e) {
echo $e->getMessage();
}
}
try {
$pdo = getPdo();
$sql = 'select * from products_categories where parent_id = 0';
$stmt = $pdo->prepare($sql);
$stmt->execute();
$data = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo $row['category_name'] . '<br>';
getSubCategories($row['category_id'], 0);
}
} catch (Exception $e) {
echo $e->getMessage();
}
function getSubCategories($parent_id, $level) {
try {
$pdo = getPdo();
$sql = "select * from products_categories where parent_id = '$parent_id'";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$data = [];
$level++;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo str_repeat("-", ($level * 4)) . $row['category_name'] . '<br>';
getSubCategories($row['category_id'], $level);
}
} catch (Exception $e) {
echo $e->getMessage();
}
}Save and close the file when you're through with editing.
The /var/www/html/products_categories.php explained:
At the beginning of the script, you've created a PHP
getPdo()function that connects to your database and returns aPDOobject ($pdo) which can be re-used by the rest of the functions.Next, you're connecting to your database server and retrieving all parent categories using the statement
select * from products_categories where parent_id = 0. Remember parent categories have aparent_idof0since they appear top in the hierarchical list.You're then looping through the main categories using the statement below. The line
while (...) {...}statement executes for every parent category found in the database table. Then, the magic happens here, for each parent category, you're calling a separategetSubCategoriesfunction as shown below.while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $row['category_name'] . '<br>'; getSubCategories($row['category_id'], 0); }Finally, the function
getSubCategories($parent_id, $level) { }again connects to the database and retrieves any sub-categories under the requested main category. Please note, this function accepts two parameters. The first parameter is the$parent_idof the category under which you want to retrieve sub-categories. The$levelparameter is a dynamic value. For parent categories, the$levelvalue will always be0. However, it will increment once each time the recursive function is called. You're achieving this by including the$level++;statement.As you retrieve the sub-categories using the
getSubCategoriesroutine, the function keeps calling itself until it finds all the sub-categories up to thenthlevel. When thegetSubCategoriesfunction calls itself, this is referred to as recursion.while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo str_repeat("-", ($level * 4)) . $row['category_name'] . '<br>'; getSubCategories($row['category_id'], $level); }The statement
echo str_repeat("-", ($level * 4))puts some formating-characters at the beginning of every list item to determine how deep the record appears when displayed. You can also use spaces(echo str_repeat(" ", ($level * 4))) or the*symbol (echo str_repeat("*", ($level * 4))).
You've now coded the recursive function to display the parent categories and sub-categories. In the next step, you'll test your application.
4. Testing the Recursive Function
In this step, you'll run the script on a web browser. Open the URL below and replace 192.0.2.1 with the correct public IP address or domain name.
You should now see the following output displaying all categories and the related sub-categories in a nice format.
SHOES
--------OFFICE SHOES
--------CASUAL SHOES
--------SAFETY BOOTS
T-SHIRTS
--------POLO NECK
--------V-NECK
COMPUTERS
--------DELL
------------DESKTOPS
------------LAPTOPS
--------HP
------------NOTEBOOKS
------------WORKSTATIONSThe recursive function is now working as expected.
Conclusion
In this guide, you've used a PHP recursive function to organize products categories in a hierarchical list. You can use this logic to format other types of information, including charts of accounts in an accounting system, files/directories in your server, departments in an organization structure, towns and associated data in a geospatial application, and more.