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

709

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!

761

Answer

Solution:

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 (),
      ),
    ),
  ),
)

anddisplayCategoryTree($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>
769

Answer

Solution:

As others mentioned in comments, you'd better to usenested 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 myforeach on$rows with your fetchwhile 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

People are also looking for solutions to the problem: php - PDO last insert id Alternative?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.