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):

Cat A

Sub-Cat 1

Sub_Sub_Cat 1

Sub_Sub_Cat 2

Sub_Cat 2

Cat B

Cat C

...

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?

score 49 · Accepted Answer · edited May 23 '17 at 12:02

49

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?

edited May 23 '17 at 12:02

Community

1
1

answered Dec 15 '10 at 16:44

simshaun

21,263
1
57
73

1

+1 Amazing that first part did exactly what I needed and was so much less complicated that I thought it needed to be. – brenjt Dec 15 '11 at 06:20
This is very clever idea I came to realize. I am interested to convert this array into JSON array but without IDs as indexes. Is that also possible? – Umair A. Mar 21 '12 at 16:24
I'm having trouble using this in an already dynamic created menu.I need to get rid of the first generated ul. – HenryW Jun 30 '13 at 20:13
i fixed it by quoting out the start and end UL and closed the toUL within them. Like: $html .= '
';
– HenryW Jun 30 '13 at 21:14
What should be passed as an array to toUL() method?? $refs or $list or $row?? – Dev Jun 26 '14 at 10:21
@Dev you should pass the `$list` – Rob Schmuecker Jun 30 '14 at 12:53
it is very helpful to me – hetal gohel Nov 16 '18 at 04:19

score 2 · Answer 2 · answered Nov 13 '15 at 18:40

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.

However, what if you ever have to make changes in the hierarchy, like say shift `subcat1_2` to `subcat3_2`, or delete a category? — Akhil Gupta, May 19 '16 at 11:55

score 2 · Answer 3 · answered Jun 15 '17 at 05:04

Try the following code

//connect to mysql and select db

$conn = mysqli_connect('localhost', 'user', 'password','database');

if( !empty($conn->connect_errno)) die("Error " . mysqli_error($conn));

//call the recursive function to print category listing
category_tree(0);

//Recursive php function
function category_tree($catid){
global $conn;

$sql = "select * from category where parent_id ='".$catid."'";
$result = $conn->query($sql);

while($row = mysqli_fetch_object($result)):
$i = 0;
if ($i == 0) echo '<ul>';
 echo '<li>' . $row->cat_name;
 category_tree($row->id);
 echo '</li>';
$i++;
 if ($i > 0) echo '</ul>';
endwhile;
}
//close the connection
mysqli_close($conn);
?>

More...

#Waruna Manjula Plus 1 for your solution mate! I turned it into dropdown menu but I am experience problem to set the parent category as selected. How can I do that? I know how to set selected the current category, but I need to show selected the parent category.. — Europeuser, Nov 11 '20 at 17:20

score 1 · Answer 4 · answered Jun 29 '15 at 02:37

@Amnon Your code works perfectly. Just tested it with CodeIgniter and it worked like a charm. Here's the working code if anyone needs it:

<?php

function disTree($all_cats) {
$tree = array();
foreach ($all_cats as $cat)
{
    $pid  = $cat->parent_id;
    $id   = $cat->cat_id;
    $name = $cat->cat_name;

    // Create or add child information to the parent node
    if (isset($tree[$pid]))
        // a node for the parent exists
        // add another child id to this parent
        $tree[$pid]["children"][] = $id;
    else
        // create the first child to this parent
        $tree[$pid] = array("children"=>array($id));

    // Create or add name information for current node
    if (isset($tree[$id]))
        // a node for the id exists:
        // set the name of current node
        $tree[$id]["name"] = $name;
    else
        // create the current node and give it a name
        $tree[$id] = array( "name"=>$name );
}
return $tree;
}


function toUL($tree, $id, $html){
  $html .= '<ul>'.PHP_EOL;

  if (isset($tree[$id]['name']))
    $html .= '<li>' . $tree[$id]['name'];

  if (isset($tree[$id]['children']))
  {
    $arChildren = &$tree[$id]['children'];
    $len = count($arChildren);
    for ($i=0; $i<$len; $i++) {
        $html .= toUL($tree, $arChildren[$i], "");
    }
    $html .= '</li>'.PHP_EOL;
  }

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

$tree = disTree($all_cats);
// Display the tree
echo toUL($tree, 0, "");

?>

The only thing I changed was adding my own array ($all_cats).

Thank you. you made it so much cleaner, easier to customize to your own needs and more understandable. Thank you! I sat with this similar code for hours and finally got it to work. — 今際のアリス, Jun 27 '16 at 01:20

score 0 · Answer 5 · answered Feb 25 '13 at 00:27

There's another way to achieve the same effect which I find a bit easier to follow (without the reference trick). You build the tree by adding the relevant information to the current node and to its parent (assume the foreach iterates over the returned rows from the SQL query):

$tree = array();
foreach ($query->result() as $row)
{
    $pid  = $row->parent_id;
    $id   = $row->id;
    $name = $row->name;

    // Create or add child information to the parent node
    if (isset($tree[$pid]))
        // a node for the parent exists
        // add another child id to this parent
        $tree[$pid]["children"][] = $id;
    else
        // create the first child to this parent
        $tree[$pid] = array("children"=>array($id));

    // Create or add name information for current node
    if (isset($tree[$id]))
        // a node for the id exists:
        // set the name of current node
        $tree[$id]["name"] = $name;
    else
        // create the current node and give it a name
        $tree[$id] = array( "name"=>$name );
}
return $tree;

and to display the tree:

function toUL($tree, $id, $html){
  $html .= '<ul>'.PHP_EOL;

  if (isset($tree[$id]['name']))
    $html .= '<li>' . $tree[$id]['name'];

  if (isset($tree[$id]['children']))
  {
    $arChildren = &$tree[$id]['children'];
    $len = count($arChildren);
    for ($i=0; $i<$len; $i++) {
        $html .= toUL($tree, $arChildren[$i], "");
    }
    $html .= '</li>'.PHP_EOL;
  }

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

// Display the tree
echo toUL($tree, 0, "");

Category Hierarchy (PHP/MySQL)

5 Answers5

Linked