Knowledgebase

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

Next, 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 changed

Next, 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
------------WORKSTATIONS

In 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 3 SHOES sub-categories. Please note: For these sub-categories, you're using 1 as the value for the parent_id column which relates back to the SHOES category.

    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 2 T-SHIRTS sub-categories. Similarly, you're using 2 as the value of the parent_id column to link the records to the T-SHIRTS category.

    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 DELL as a sub-category with a category_id of 9 under the COMPUTERS parent category. Next, create two more sub-categories under DELL and remember to change the value of their parent_ids to 9.

    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 HP sub-category with a category_id of 12 still under the main parent COMPUTERS category. Then, insert NOTEBOOKS and WORKSTATIONS sub-categories under HP.

    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.php

Then, 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:

1. At the beginning of the script, you've created a PHP getPdo() function that connects to your database and returns a PDO object ($pdo) which can be re-used by the rest of the functions.

2. 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 a parent_id of 0 since they appear top in the hierarchical list.

3. 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 separate getSubCategories function as shown below.

             while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                 echo $row['category_name'] . '<br>';
                 getSubCategories($row['category_id'], 0);
             }   

4. 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_id of the category under which you want to retrieve sub-categories. The $level parameter is a dynamic value. For parent categories, the $level value will always be 0. 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 getSubCategories routine, the function keeps calling itself until it finds all the sub-categories up to the nth level. When the getSubCategories function 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.

• http://192.0.2.1/products_categories.php

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

The 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.