我想从MySQL查询创建下拉菜单,但是我在子项目上遇到了麻烦。
我的基本表:
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查询并添加到数组:
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>';
结果是:
<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>
但是我真正想要的是:
<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>
如何实现此结果?我已经尝试了许多其他例子,但是似乎没有帮助我。也许我不是在寻找正确的东西。
为什么要使它变得复杂,这可以通过非常简单的递归功能来完成。
这是我在本地机器中所做的。我有连接参数,然后称为函数
bulit_tree(0);
- 在此功能中,它将检查参数是否为0,然后选择全部ID和parentid相同的项目。
- 然后循环循环并使用递归函数并生成子树。
需要确保$ con在函数中可加入。
$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>'; } } }
您可能需要先重组数据库。考虑一个联接表。这很方便,特别是如果您的产品分为多个类别。
主表:
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
然后,在您的班上,您可以像以下方式结构结构:
<?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>";
thanx用于所有注释。我已经接受了Agha使用递归功能的建议。
http://crisp.tweakblogs.net/blog/317/formatting-a-multi-level-menu-using-menu-using-only-only-one-query.html