How to create a nested menu from MySQL with PHP?

Question

I need to create a menu with PHP from a MySQL database.

Table called categories has id, name, parent_id, shortdesc, etc.

The output need to have parent list and children list under the partent list as follows.

If you can show me codes or website, I will appreciate it.

<ul id="catmenu">
    <li class="menulist">Cars
        <ul>
            <li>Ford</li>
            <li>Honda</li>
            <li>Toyota</li>
        </ul>
    </li>
    <li class="menulist">Food
       <ul>
            <li>Pasta</li>
            <li>Pizza</li>
            ...
       </ul>
    </li>
...
...
</ul>

Answer 1

This is specifically for two levels deep. Recommended approach should it be more is to use an optimized table structure for traversal, like http://articles.sitepoint.com/article/hierarchical-data-database/2 (pointed out elsewhere) or to pull the data you need and push it into a dictionary (associative array) and query it that way.

<?php
    $query = <<<EOT
     SELECT
      parent.name as parent_name,
      child.name as child_name,
     FROM
      items child
     INNER JOIN
      items parent
     ON
      child.parent_id = parent.id
     ORDER BY
      parent.name
EOT;

    $result = mysql_query($query) or die('Failure!');

    echo "<ul id=\"catmenu\">";

    $last_parent = '';
    while($row = mysql_fetch_array($result)){
     // If this is a new category, start a new one
     if($last_parent != $row['parent_name']){
      // Unless this is the first item, close the last category
      if($last_parent != ''){
       echo "</ul></li>";
      }
      $last_parent = $row['parent_name'];
      echo "<li class=\"menulist\">{$row['parent_name']}<ul>";
     }
     echo "<li>{$row['child_name']}</li>";
    }

    // If we actually had items, close the "category"
    if($last_parent != ''){
     echo "</ul></li>";
    }

    echo "</ul>";

?>

Answer 2

If you have only two levels then, you could just display them :

echo '<ul id="catmenu">';
foreach($menu as $element) {


    echo '<li><ul class="menulist">';
    foreach($element['submenu'] as $submenu) {

        echo '<li>' . $submenu['name'] . '</li>';
    }
    echo '</ul></li>';
}
echo '</ul>

If you have an undefined number of submenus however you should use a Recursive Function.

function menu($item) {
    $ret = '<li>' . $item['name'];

    if (!empty($item['submenu'])) {
        foreach($item['submenu'] as $submenu) {
            $ret .= menu($submenu);
        }
    }
    return $ret;
}
echo menu($menu);

So every of your subcategories, whatever their number is will be displayed.

Answer 3

You make database like this.

ID    NAME     PARENT
0     Cars     -1
1     Foods    -1
2     Ford      0
3     Honda     0
4     Toyota    0
5     Pasta     1
6     Pizza     1
...

You query them all up and put it in an array.

$Menus = array();
// In a read MySQL loop
$Menus[$i]['ID']
$Menus[$i]['NAME']
$Menus[$i]['PARENT']
// Sorry, lazy to write. I think you know what I mean.

Then you loop all menu looking for PARENT == -1. Generate all UL and IL then sub it with another nested menu. You can simply create a function like this.

var $MenuLevelClass = array("menulist");
 
function CreateMenu($Menus, $Tab = 0, $Parent = -1, $Level = 0) {
    global $MenuLevelClass;
     
    $CatClass  = ($Level != 0) ? '' : ' class="catmenu"';
     $MenuClass = $MenuLevelClass[$Level];
     if ($MenuClass != '')
         $MenuClass = ' class="'.$MenuClass.'"';
      
     $TabCount = $Level + $Tab;
     $TabUL    = "";
     for ($t = 0; $t < $TabCount; $t++)
         $TabUL = $TabUL."\t";
     $TabLI = $TabUL."\t";
      
?>
<?=$TabUL?><ul<?=$CatClass?>>
<?php
     
    $MenuCount = count($Menus);
    for ($m = 0; $m < $MenuCount; $m++) {
        $Menu = $Menu[$m];
        $ID   = $Menu['ID'];
        if ($ID != $Parent)
            continue;
     
?>
<?=$TabLI?><li<?=$MenuClass?>><?=$Menu['Name']?><?=CreateMenu($Menus, $Tab + 1, $ID, $Level + 1)?></li>
<?php
     
?>
<?=$TabUL?></ul>
<?php
     
    }
}

And to use it just run 'CreateMenu($Menus);' or 'CreateMenu($Menus, $PrefixTabCount);'. CreateMenu will recursively create the nested menu for you.

I have not test it so you may have to adjust it.

Hope this helps.