How to create nested category list from category-strings in PHP

Question

I have a MySQL table "products" that has a column called "category". In this field you find the categorie herarchy for every product as a string. E.g.:

female / dresses / long
female / dresses / short
female / shoes  
male / shoes / sneakers / skate
...

I selected all different categories with:

$result = $mysqli->query("SELECT DISTINCT category FROM products ORDER BY category");

Now, I want to be able to print these categories as a nested HTML-list like

female
- dresses
  - long
  - short
- shoes
male
- shoes
  - sneaker
    - skate

Any advice would be very much appreciated. Thanks!

EDIT: I get the data from several csv files, so a solution without the need of transforming the categories would be great!

I advice to have separate table for categories and read about "Nested sets". — Alex, Sep 09 '13 at 20:01
You need to know who is parent and who is child , and also the depth — Charaf JRA, Sep 09 '13 at 20:04
a solution without the need of a second table would be great, since I get the data as csv in this format. — AndiPower, Sep 09 '13 at 20:43
@AndiPower even if you would find a solution without a second table it would perform terrible bad. So spending the time in preprocessing and using `Nested Tree Sets` like _Sanja_ suggested is the best way to avoid bottlenecks and other problems in future. To make the import easier you can for sure use a _path_ to _node in tree_ mapping as fast lookup table for import, with that it shouldn't be a big deal to implement this even with multiple csv files as source. Here a short description of [Nested Tree Sets](http://stackoverflow.com/a/14959883/1960455) I wrote to another question. — t.niese, Sep 09 '13 at 20:57

Yanick Rochon · Accepted Answer · 2013-09-10T16:08:56.023

Just construct your tree using the categories as the node's key. For example :

$categoryLines = array(
    "female / dresses / long",
    "female / dresses / short",
    "female / shoes",
    "male / shoes / sneakers / skate"
);

function buildCategoryTree($categoryLines, $separator) {
    $catTree = array();
    foreach ($categoryLines as $catLine) {
       $path = explode($separator, $catLine);
       $node = & $catTree;
       foreach ($path as $cat) {
           $cat = trim($cat);
           if (!isset($node[$cat])) {
               $node[$cat] = array();
           }
           $node = & $node[$cat];
       }
    }
    return $catTree;
}

function displayCategoryTree($categoryTree, $indent = '') {
    foreach ($categoryTree as $node => $children) {
        echo $indent . $node . "\n";
        displayCategoryTree($children, $indent . '|- ');
    }
}

$categoryTree = buildCategoryTree($categoryLines, '/');

Now, var_export($categoryTree) will output :

array (
  'female' => array (
    'dresses' => array (
      'long' => array (),
      'short' => array (),
    ),
    'shoes' => array (),
  ),
  'male' => array (
    'shoes' => array (
      'sneakers' => array (
        'skate' => array (),
      ),
    ),
  ),
)

and displayCategoryTree($categoryTree) will output :

female
|- dresses
|- |- long
|- |- short
|- shoes
male
|- shoes
|- |- sneakers
|- |- |- skate

Edit

To get the HTML representation of the tree :

function displayHtmlCategoryTree($categoryTree, $id = null, $pathSeparator = '/', $parents = '') {
    if (empty($categoryTree)) return '';

    $str = '<ul' . (!empty($id) ? ' id="'.$id.'"' : '') . '>';
    foreach ($categoryTree as $node => $children) {
        $currentPath = $parents . (empty($parents) ? '' : $pathSeparator) . $node;
        $str .= '<li title="' . $currentPath . '">' . $node . 
                displayHtmlCategoryTree($children, null, $pathSeparator, $currentPath) . 
                '</li>';
    }
    $str .= '</ul>';
    return $str;
}

echo displayHtmlCategoryTree($categoryTree, "test", ' / ');

and will output :

<ul id="test"><li title="female">female<ul><li title="female / dresses">dresses<ul><li title="female / dresses / long">long</li><li title="female / dresses / short">short</li></ul></li><li title="female / shoes">shoes</li></ul></li><li title="male">male<ul><li title="male / shoes">shoes<ul><li title="male / shoes / sneakers">sneakers<ul><li title="male / shoes / sneakers / skate">skate</li></ul></li></ul></li></ul></li></ul>

Leaves on this tree are `Array`. Think it's better to replace it with a single string. — MahanGM, Sep 10 '13 at 13:25
Why does it matter? This is actually better since it does not create an exceptions; meaning that leaves or nodes.... they both have the "label" as keys and an array defining any potential children as value. Doing anything other than that requires extra and unncecessary checks. — Yanick Rochon, Sep 10 '13 at 14:04
I'm not a data structure geek and I don't know what are the consequences of having no leaves in a tree so, I was just mentioning that it'd better to have an ending point in paths. Besides, extra check is just up to look at current node's depth and that's all, no heavy lifting. Cheers :) — MahanGM, Sep 10 '13 at 15:59
@YanickRochon: That's fantastic. You're my hero and now I feel dumb. The buildCategoryTree-function is very clever. Thanks for the title-elements in the HTML! Great! — AndiPower, Sep 10 '13 at 19:16

MahanGM · Answer 2 · 2013-09-10T13:24:00.210

As others mentioned in comments, you'd better to use nested tree sets for this kind of categorization, but since you asked you don't want to change the scheme of your category system and IF and only IF you're not concerned about your run-time overload, you can use this code that I wrote.

For populating an array of categories:

<?php
  $rows = array(
    "female/dresses/long",
    "female/dresses/short",
    "female/dresses/short/1",
    "female/dresses/short/2",
    "female/dresses/short/2/1",
    "female/shoes",
    "male/shoes/sneakers/skate"
  );

  $categories = array();

  function populate()
  {
    global $rows;
    global $categories;

    foreach($rows as $row)
    {
      $category_array = explode(" / ", $row);

      $categories = place($categories, $category_array);
    }
  }

  function place($categories, $category, $counter = 0)
  {
    if($counter < count($category))
    {
      if(!isset($categories[$category[$counter]]))
      {
        $categories[$category[$counter]] = array();
      }
      else if($counter == count($category) - 1)
      {
        $categories[$category[$counter]][] = $category[$counter];
      }

      // Recurse
      $categories[$category[$counter]] = place($categories[$category[$counter]], $category, $counter + 1);
    }

    return $categories;
  }

  // Populate categories
  populate();
?>

You can replace my foreach on $rows with your fetch while loop.

For displaying of categories:

<?php
  function display($items)
  {
    foreach($items as $k => $v)
    {
      echo "<li>" . $k;

      if(is_array($v))
      {
        echo "<ul>";

        display($v);

        echo "</ul>";
      }

      echo "</li>";
    }
  }
?>

<!DOCTYPE HTML>

<html>

<head>
  <title>Categories</title>
</head>

<body>
  <ul>
  <?php
    // Display categories
    display($categories);
  ?>
  </ul>
</body>

</html>

Current demonstration:

female
    dresses
        long
        short
            1
            2
                1
    shoes

male
    shoes
        sneakers
            skate

Thank you for your effort. Your solution is good, too. Yanick just hit the bull's eye. — AndiPower, Sep 10 '13 at 19:20

How to create nested category list from category-strings in PHP

2 Answers2

** Edit **

Edit