Php/MySQL Build Tree Menu

PHP / MySQL build tree menu

I like @mario's solution, and have improved on it with the prevention of the excess <ul>. I would just recommend doing an ORDER BY on your SQL query to get the menu in the order you want (might even recommend a weight/sequence column be added to the schema.

Data setup:

$menu = array( // Presumed to have been coming from a SQL SELECT, populated for demo.
  array('id'=>1,'title'=>'Menu 1',          'parent_id'=>null),
  array('id'=>2,'title'=>'Sub 1.1',         'parent_id'=>1),
  array('id'=>3,'title'=>'Sub 1.2',         'parent_id'=>1),
  array('id'=>4,'title'=>'Sub 1.3',         'parent_id'=>1),
  array('id'=>5,'title'=>'Menu 2',          'parent_id'=>null),
  array('id'=>6,'title'=>'Sub 2.1',         'parent_id'=>5),
  array('id'=>7,'title'=>'Sub Sub 2.1.1',   'parent_id'=>6),
  array('id'=>8,'title'=>'Sub 2.2',         'parent_id'=>5),
  array('id'=>9,'title'=>'Menu 3',          'parent_id'=>null),
);

Handling:

function has_children($rows,$id) {
  foreach ($rows as $row) {
    if ($row['parent_id'] == $id)
      return true;
  }
  return false;
}
function build_menu($rows,$parent=0)
{  
  $result = "<ul>";
  foreach ($rows as $row)
  {
    if ($row['parent_id'] == $parent){
      $result.= "<li>{$row['title']}";
      if (has_children($rows,$row['id']))
        $result.= build_menu($rows,$row['id']);
      $result.= "</li>";
    }
  }
  $result.= "</ul>";

  return $result;
}
echo build_menu($menu);

Output:

<ul>  <li>Menu 1<ul>    <li>Sub 1.1</li>    <li>Sub 1.2</li>    <li>Sub 1.3</li>  </ul></li>  <li>Menu 2<ul>    <li>Sub 2.1<ul>      <li>Sub Sub 2.1.1</li>    </ul></li>    <li>Sub 2.2</li>  </ul></li>  <li>Menu 3</li></ul>

Hierarchical tree menu - PHP / MySQL

Try this :

<?php
include 'db.php';

$sql = "SELECT * FROM menu_items WHERE status = 'ACTIVE' ORDER BY pid ASC, sortorder ASC, menu_item_name ASC";
$query = $db->query($sql);

$menu_items = array();

while($data = $query->fetch(PDO::FETCH_ASSOC)) {
    if($data['pid'] == 0) {
        $menu_items[$data['id']] = array();
        $menu_items[$data['id']]['id'] = $data['id'];
        $menu_items[$data['id']]['name'] = $data['menu_item_name'];
        $menu_items[$data['id']]['url'] = $data['menu_url'];
        $menu_items[$data['id']]['children'] = array();
    } else if($data['pid'] != 0) {
        $tmp = array();
        $tmp['id'] = $data['id'];
        $tmp['name'] = $data['menu_item_name'];
        $tmp['url'] = $data['menu_url'];

        array_push($menu_items[$data['pid']]['children'],$tmp);
        unset($tmp);
    }
}

function create_list($arr)
{
    $html = "";
    foreach($arr as $key => $value) {
        if(count($value['children']) > 0) {
            $html .= '  <a class="menuitem submenuheader" href="'. $value['url'] .'">'.$value['name'].'</a>
                        <div class="submenu">
                        <ul>';

            foreach($value['children'] AS $child) {
                $html .= '  <li>
                                <a id="'.$child['id'].'">'.$child['name'].'</a>
                            </li>';
            }

            $html .= '  </ul>
                        </div>';
        } else{
            $html .= '  <a id="'.$value['id'].'">'.$value['name'].'</a>';
        }
    }

    return $html;
}

echo "<div class=\"glossymenu\">";
    echo create_list($menu_items);
echo "</div>";
?>

how to create tree menu use data in mysql?

Another solution, should be able to deal with various sizes of list levels.

$listData[]=array('menu_code' => '01','menu_name' => 'setting', 'menu_link' => null);
$listData[]=array('menu_code' => '01.01','menu_name' =>  'admin', 'menu_link' => null);
$listData[]=array('menu_code' => '01.01.01','menu_name' =>  'add', 'menu_link'   =>'add.php');
$listData[]=array('menu_code' => '01.01.02','menu_name' =>  'edit', 'menu_link' => 'edit.php');
$listData[]=array('menu_code' => '02','menu_name' =>  'people', 'menu_link' => null);
$listData[]=array('menu_code' => '02.01','menu_name' =>  'people_edit', 'menu_link' => 'edit.php');
$listData[]=array('menu_code' => '02','menu_name' => 'people', 'menu_link' => null);

now the function

function createListMenu($listData){
    $str ='<ul>';
    $lastListLevel=0;
    $firstRow=true;

    foreach($listData as $row){
        $currentListLevel=count(explode('.',$row['menu_code'])) -1 ; // minus one to correct level to Level 0
        $differenceLevel=$currentListLevel-$lastListLevel;

        // deal with the link
        $hrefValue = is_null($row['menu_link']) ? '' : $row['menu_link'];
        $rootLevel=false;

        if($differenceLevel==0){        // stay on the same list level
            if($firstRow){
                $firstRow=false;
            }else{
                $str .='</li>';
            }
            $str .='<li>';
        }elseif($differenceLevel>0){    // go deeper in list level
            for($j=0;$j<$differenceLevel;$j++){
                $str .='<ul><li>';
            }
        }elseif($differenceLevel<0){    // go higher in list level
            for($j=$differenceLevel;$j<=0;$j++){
               if($j==0){  // root level reached
                   $rootLevel=true;
                   $str .='</li>';
               }else{
                   $str .='</li></ul>';
               }
            }
            $str .= ($rootLevel) ? '<li>' : '<ul>';
        }
        $str.='<a href="'.$hrefValue.'">'.$row['menu_name'].'</a>';
        $lastListLevel=$currentListLevel;
    }

    // set end list tags
    for($j=$lastListLevel;$j>=0;$j--){
        $str .='</li></ul>';
    }
    return $str;
}

echo createListMenu($listData);

PHP/MySQL - building a nav menu hierarchy

Dealing with the data structure as you have it will often involve recursion or multiple queries to build the tree.

Have you considered other ways of storing a hierarchy? Check out modified pre-order traversal - here's a nice PHP based article about this.

How to create multidimensional tree menu using PHP and SQL?

You want to save each element in the DB with an ID and a parentID (that can be null if no such parent exists). The PHP is your "biggest" problem, but references are your huge friend here for turning a flat structure into a tree structure.

Consider the following DB result:

----------------------------
| id | parentID | text     |
|----|----------|----------|
| 1  | null     | Item #1  |
| 2  | 5        | Item #2  |
| 3  | 2        | Item #3  |
| 4  | 2        | Item #4  |
| 5  | null     | Item #5  |
| 6  | 5        | Item #6  |
| 7  | 3        | Item #7  |
| 8  | 5        | Item #8  |
| 9  | 1        | Item #9  |
| 10 | 7        | Item #10 |
----------------------------

Consider the following array (that could be from a DB result - it's important that the ID is the key, though. You can simply transform your DB result into something like the following (the only needed key is "parentID"):

$menu = array(
    1 => array('text' => 'Item #1', 'parentID' => null),
    2 => array('text' => 'Item #2', 'parentID' => 5),
    3 => array('text' => 'Item #3', 'parentID' => 2),
    4 => array('text' => 'Item #4', 'parentID' => 2),
    5 => array('text' => 'Item #5', 'parentID' => null),
    6 => array('text' => 'Item #6', 'parentID' => 5),
    7 => array('text' => 'Item #7', 'parentID' => 3),
    8 => array('text' => 'Item #8', 'parentID' => 5),
    9 => array('text' => 'Item #9', 'parentID' => 1),
   10 => array('text' => 'Item #10', 'parentID' => 7),
);

And to turn it into a tree structure:

<?php    
$addedAsChildren = array();

foreach ($menu as $id => &$menuItem) { // note that we use a reference so we don't duplicate the array
    if (!empty($menuItem['parentID'])) {
        $addedAsChildren[] = $id; // it should be removed from root, but we'll do that later

        if (!isset($menu[$menuItem['parentID']]['children'])) {
            $menu[$menuItem['parentID']]['children'] = array($id => &$menuItem); // & means we use the REFERENCE
        } else {
            $menu[$menuItem['parentID']]['children'][$id] = &$menuItem; // & means we use the REFERENCE
        }
    }

    unset($menuItem['parentID']); // we don't need parentID any more
}

unset($menuItem); // unset the reference

foreach ($addedAsChildren as $itemID) {
    unset($menu[$itemID]); // remove it from root so it's only in the ['children'] subarray
}

With this new array we can use a simply recursive function to output it all in a ul..li sense:

echo makeTree($menu);

function makeTree($menu) {
    $tree = '<ul>';

    foreach ($menu as $id => $menuItem) {
        $tree .= '<li>' . $menuItem['text'];

        if (!empty($menuItem['children'])) {
            $tree .= makeTree($menuItem['children']);
        }

        $tree .= '</li>';
    }

    return $tree . '</ul>';
}

Resulting in:

<ul><li>Item #1<ul><li>Item #9</li></ul></li><li>Item #5<ul><li>Item #2<ul><li>Item #3<ul><li>Item #7<ul><li>Item #10</li></ul></li></ul></li><li>Item #4</li></ul></li><li>Item #6</li><li>Item #8</li></ul></li></ul>

..and rendered:

DEMO

How to make tree menu with php

A quick search of google found this solution here. You will need to do a recursive look through your array to get to your final solution.

---

Related Topics