PHP MySQL Multidimensional Array - Dropdown Menu

You might need to restructure your DB first. Consider a join table. This comes handy especially if your Product falls into multiple categories.

Master table:

    NavigationID    Name       Url
    1               Home       home
    2               About      about
    3               Products   products
    4               Category1  #
    5               Category2  #
    6               Product1   #
    7               Product2   #

Lookup Table:

    NavigationID    ParentId
    1               1
    2               2 
    3               3
    4               3
    5               3
    6               4
    7               5

Then in your class, you can make it structured like:

<?php 
class Navigation{
    private $menuitems;

    public function __construct($par){
        if(is_array($par))
            $this->menuitems = $par;
    }

    public funtion __toString() {
        $this->printNavigation($this->menuitems);
    }

     private function printMenuItem($menu) {
        echo '<li><a href="'.$menu->url.'">'.$menu->name.'</a>';

        if(count($menu->children)) {
            print printNavigation($menu->children);
        } 
        '</li>';
     }

    private function printNavigation($menuItems) {
        echo "<ul>";
        foreach ($menuitems as $menu {
            $this->printMenuItem($menu);
        }
        echo "</ul>";
    }
}


class MenuItem{

    private $url;
    private $name;
    private $children; 

    public function __construct($par){
        if(is_array($par)) {
            $this->url = $par['url'];
            $this->$name = $par['name'];
            $this->children = $this->fetchChildren($par['NavigationID']);
        }

    }

    function fetchChildren($id) {
        $query = mysql_query("SELECT * from navigation n INNER JOIN Lookup l on l.parentID = n.NavigationID 
                                WHERE n.NavigationID = $id") or die(mysql_error());
        $num = mysql_num_rows($query);
        if($num > 0) {

            while($row = mysql_fetch_assoc($query)){

                $this->children[] = new MenuItem($row);
            }
        }
    }

}

$query = mysql_query("SELECT * from navigation n INNER JOIN Lookup l on l.NavigationID = n.NavigationID 
                            WHERE l.NavigationID = l.parentIDn
                            AND l.NavigationID != n.NavigationID") or die(mysql_error());
$num = mysql_num_rows($query);
$menuitems = array();

while($row = mysql_fetch_assoc($query)){

    $menuitems[] = new MenuItem($row);
}

$navigation = new Navigation($menuitems);

echo "<div id='nav'>$navigation</div>";

Why to make it complicated, this could be done with a very simple recursive function.

This is what I did in my local machine. I have the connection parameter and then called a function

bulit_tree(0);

• In this function it will check if the argument is 0 then select all the item where id and parentid is same.
• Then loop through and use the recursive function and generate sub tree.

• Need to make sure that the $con is accesible within the function.

  $con = mysql_connect("localhost","testuser","testpass");
  
  $db_selected = mysql_select_db('testdb', $con);
  if (!$db_selected) {
      die ('Can\'t use testdb : ' . mysql_error());
  }
  
  bulit_tree(0);
  
  function bulit_tree($pid=0){ 
      global $con ;   
      if($pid == 0 ){
              $qry = "select * from Navigation where NavigationID = ParentID";
              $q = mysql_query($qry,$con);
              if(mysql_num_rows($q) > 0 ){ 
                  echo '<ul>';
                  while($row = mysql_fetch_assoc($q)){
                      echo '<li><a href="'.$row["Url"].'">'.$row["Name"].'</a>';
                      bulit_tree($row["NavigationID"]);
                      echo '</li>';
                  }
                  echo '</ul>';
              }
      }else{
          $qry = "select * from Navigation where ParentID = ".$pid." AND NavigationID <> ".$pid;
          $q = mysql_query($qry,$con);
              if(mysql_num_rows($q) > 0 ){
                  echo '<ul>';
                  while($row = mysql_fetch_assoc($q)){
                      echo '<li><a href="'.$row["Url"].'">'.$row["Name"].'</a>';
                      bulit_tree($row["NavigationID"]);
                      echo '</li>';
                  }
                  echo '</ul>';
              }
      }
  }
  

Thanx for all the comments. I've gone with Agha's suggestion of using a recursive function.

http://crisp.tweakblogs.net/blog/317/formatting-a-multi-level-menu-using-only-one-query.html