PHP MySQL Multidimensional Array - Dropdown Menu

Question

I want to create my dropdown menu from a mysql query, but i'm having trouble with the sub-items.

My basic table:

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

My simple MySQL Query and adding to array:

class Navigation{
    private $data;

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

    public function __toString(){
        return '<li><a href="'.$this->data['Url'].'">'.$this->data['Name'].'</a></li>';
    }
}

$query = mysql_query("SELECT * FROM Navigation n") or die(mysql_error());
$num = mysql_num_rows($query);
$menuitems = array();

while($row = mysql_fetch_assoc($query)){
    $menuitems[] = new Navigation($row);
}

echo '<div id="nav"><ul>';
foreach($menuitems as $item){
    echo $item;
}
echo '</ul></div>';

The result of this is:

<div id="nav"><ul>
   <li><a href="home">Home</a></li>
   <li><a href="about">About</a></li>
   <li><a href="products">Products</a></li>
   <li><a href="#">Category1</a></li>
   <li><a href="#">Category2</a></li>
   <li><a href="#">Product1</a></li>
   <li><a href="#">Product2</a></li>
</ul></div>

But what I would REALLY like is this:

<div id="nav"><ul>
   <li><a href="home">Home</a></li>
   <li><a href="about">About</a></li>
   <li><a href="products">Products</a>
       <ul>
          <li><a href="#">Category1</a>
              <ul>
                 <li><a href="#">Product1</a></li>
              </ul>
          </li>
          <li><a href="#">Category2</a>
              <ul>
                 <li><a href="#">Product2</a></li>
              </ul>
          </li>
        </ul>
   </li>
</ul></div>

How can I achieve this result? I've tried many other examples, but none seems to help me. Maybe I'm not searching for the right thing.

Answer 1

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 function __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>";

Answer 2

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>';
              }
      }
  }
  

Answer 3

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