PHP的MySQL多维数组 - 下拉菜单 [英] PHP MySQL Multidimensional Array - Dropdown Menu

问题描述

我想从一个MySQL查询创建我的下拉菜单中，但我有与子项的麻烦。

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

我的基本表：

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   #

我的简单的MySQL查询，并增加数组：

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.

推荐答案

您可能需要先调整你的数据库。考虑连接表。这都方便，特别是如果你的产品分为多个类别。

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

主表：

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

查找表：

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

这篇关于PHP的MySQL多维数组 - 下拉菜单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！