Category Hierarchy (PHP/MySQL)

Question

I am trying to get my all categories and sub-categories from MySQL database in a hierarchy:

My result should be like that (just example):

1. Cat A
   • Sub-Cat 1
     • Sub_Sub_Cat 1
     • Sub_Sub_Cat 2
   • Sub_Cat 2
2. Cat B
3. Cat C
4. ...

MySQL code:

CREATE TABLE IF NOT EXISTS `categories` (
   `category_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
   `parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT 'for sub-categories'
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

Simply, how can get it in a hirarchy with PHP codes?

Answer 1

When using an adjacency list model, you can generate the structure in one pass.

Taken from One Pass Parent-Child Array Structure (Sep 2007; by Nate Weiner):

$refs = array();
$list = array();

$sql = "SELECT item_id, parent_id, name FROM items ORDER BY name";

/** @var $pdo \PDO */
$result = $pdo->query($sql);

foreach ($result as $row)
{
    $ref = & $refs[$row['item_id']];

    $ref['parent_id'] = $row['parent_id'];
    $ref['name']      = $row['name'];

    if ($row['parent_id'] == 0)
    {
        $list[$row['item_id']] = & $ref;
    }
    else
    {
        $refs[$row['parent_id']]['children'][$row['item_id']] = & $ref;
    }
}

From the linked article, here's a snippet to create a list for output. It is recursive, if there a children for a node, it calls itself again to build up the subtree.

function toUL(array $array)
{
    $html = '<ul>' . PHP_EOL;

    foreach ($array as $value)
    {
        $html .= '<li>' . $value['name'];
        if (!empty($value['children']))
        {
            $html .= toUL($value['children']);
        }
        $html .= '</li>' . PHP_EOL;
    }

    $html .= '</ul>' . PHP_EOL;

    return $html;
}

Related Question:

• How to obtain a nested HTML list from object's array recordset?

Answer 2

I have a new idea I think it will be nice. The idea is this: in category_parent column we will insert a reference to all parents of this node.

+----+----------------------+-----------------+
| id | category_name        |    hierarchy    |
+----+----------------------+-----------------+
| 1  | cat1                 |        1        |
+----+----------------------+-----------------+
| 2  | cat2                 |        2        |
+----+----------------------+-----------------+
| 3  | cat3                 |        3        |
+----+----------------------+-----------------+
| 4  | subcat1_1            |       1-4       |
+----+----------------------+-----------------+
| 5  | subcat1_2            |       1-5       |
+----+----------------------+-----------------+
| 6  | subsubcat1_1         |      1-4-6      |
+----+----------------------+-----------------+
| 7  | subsubcat1_2         |      1-4-7      |
+----+----------------------+-----------------+
| 8  | subsubcat1_3         |      1-4-8      |
+----+----------------------+-----------------+
| 9  | subsubcat1_3_1       |     1-4-8-9     |
+----+----------------------+-----------------+
| 10 | subsubcat1_3_2       |     1-4-8-10    |
+----+----------------------+-----------------+
| 11 | subsubcat1_3_1_1     |    1-4-8-9-11   |
+----+----------------------+-----------------+
| 12 | subsubsubcat1_3_1_1  |   1-4-8-9-12    |
+----+----------------------+-----------------+
| 13 | subsubsubcat1_3_1_2  |  1-4-8-9-11-13  |
+----+----------------------+-----------------+
| 14 | subsubsubcat1_2_1_3  |  1-4-8-9-11-14  |
+----+----------------------+-----------------+

if you look at my updated table you will notice that every record has an link to its parents, not only the direct one, But also all of parents. And for that job I made some modification to insert to be:

Insert into table_name (category_name, hierarchy) values ('new_name', (concat(parent_hierarch, '-', (SELECT Auto_increment FROM information_schema.tables WHERE table_name='table_name'))))

Now lets make your desired queries:

1- all sub categories of cars:

select * from table_name where hierarchy like '1-%'

2- if you need all parent of BLACK you simply type:

select * from table_name where hierarchy = '1-4-8-9' or hierarchy = '1-4-8' or hierarchy = '1-4' or hierarchy = '1'

(you can build that query from php, splitting hierarchy field at '-' char)

3- To see all categories, with level and direct parent:

select *, SUBSTR(hierarchy, 1, (LENGTH(hierarchy) - LENGTH(id) - 1)) as parent, LENGTH(hierarchy) - LENGTH(REPLACE(hierarchy, '-', '')) as level From table_name

+----+----------------------+-----------------+-----------+--------+
| id | category name        |    hierarchy    |   parent  |  level |
+----+----------------------+-----------------+-----------+--------+
| 1  | cat1                 |        1        |           |    0   |
+----+----------------------+-----------------+-----------+--------+
| 2  | cat2                 |        2        |           |    0   |
+----+----------------------+-----------------+-----------+--------+
| 3  | cat3                 |        3        |           |    0   |
+----+----------------------+-----------------+-----------+--------+
| 4  | subcat1_1            |       1-4       |     1     |    1   |
+----+----------------------+-----------------+-----------+--------+
| 5  | subcat1_2            |       1-5       |     1     |    1   |
+----+----------------------+-----------------+-----------+--------+
| 6  | subsubcat1_1         |      1-4-6      |    1-4    |    2   |
+----+----------------------+-----------------+-----------+--------+
| 7  | subsubcat1_2         |      1-4-7      |    1-4    |    2   |
+----+----------------------+-----------------+-----------+--------+
| 8  | subsubcat1_3         |      1-4-8      |    1-4    |    2   |
+----+----------------------+-----------------+-----------+--------+
| 9  | subsubcat1_3_1       |     1-4-8-9     |   1-4-8   |    3   |
+----+----------------------+-----------------+-----------+--------+
| 10 | subsubcat1_3_2       |     1-4-8-10    |   1-4-8   |    3   |
+----+----------------------+-----------------+-----------+--------+
| 11 | subsubcat1_3_1_1     |    1-4-8-9-11   |  1-4-8-9  |    4   |
+----+----------------------+-----------------+-----------+--------+
| 12 | subsubsubcat1_3_1_1  |   1-4-8-9-12    |  1-4-8-9  |    4   |
+----+----------------------+-----------------+-----------+--------+
| 13 | subsubsubcat1_3_1_2  |  1-4-8-9-11-13  |1-4-8-9-11 |    5   |
+----+----------------------+-----------------+-----------+--------+
| 14 | subsubsubcat1_2_1_3  |  1-4-8-9-11-14  |1-4-8-9-11 |    5   |
+----+----------------------+-----------------+-----------+--------+

This is a new idea and need some improvement.