php mysql 动态多级菜单和子菜单

Question

我有一个需要从数据库动态创建的菜单。需要有菜单和子菜单

 <?php

$sql =('SELECT rubriques.id,rubriques.intitule,actions.intitulee,actions.lien,actions.idr   FROM rubriques,actions where rubriques.id=actions.idr ');

$stmt = $conn->query($sql);

    if($stmt->num_rows > 0)

    {

while($row=$stmt->fetch_assoc())

{

    extract($row);

    ?>

          <li class="active"><a href="index.html"><?php echo $intitule; ?></a>

          <ul class="dropdown">

                <li><a href="<?php echo $lien; ?>"><?php echo $intitulee; ?></a></li>

              </ul>

    <?php

   }  }         

  ?>  

例如（我想要什么）：

如果 A 是菜单项而 A1 A2 A3 是子菜单项我想要的是这样的菜单 A

A1

A2

A3

但我得到的这段代码是

AAA

A1 A2 A3

 ```CREATE TABLE IF NOT EXISTS `actions` (

     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

     `intitulee` varchar(255) NOT NULL,

     `lien` varchar(255) NOT NULL,

     `idr` int(255) NOT NULL,

    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

     INSERT INTO `actions` (`id`, `intitulee`, `lien`, `idr`) VALUES

     (1, 'Estivage', 'estirage.php', 1),

     (4, 'Excursions', 'exurcions.html', 1),

     (5, 'Equipe foot', '404.html', 2),

     (6, 'Clubs de sports ', '404.html', 0),

      (7, 'Fete des femmes', '404.html', 3),

 CREATE TABLE IF NOT EXISTS `rubriques` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`intitule` varchar(255) NOT NULL,

 PRIMARY KEY (`id`)

 ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

   INSERT INTO `rubriques` (`id`, `intitule`) VALUES

   (1, 'Voyages'),

   (2, 'ACTIVITES CULTURELLES ET SPORTIVES.'),

   (3, 'FETES & RECEPTIONS'),

Answer 1

由于您menu和sub-menu在不同的表中，您可以menu先选择，然后sub-menu根据所选菜单进行选择。即：&nbsp;<?php//getting menu first$sql&nbsp; = 'SELECT id,intitule FROM rubriques';$stmt = $conn->query($sql);if ($stmt->num_rows > 0) {&nbsp; &nbsp;while($row = $stmt->fetch_assoc()){&nbsp; &nbsp; &nbsp; &nbsp;//getting values&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; $intitule = $row['intitule '];&nbsp; &nbsp; &nbsp; &nbsp; $idr = $row['id']; ?>&nbsp; &nbsp; &nbsp; &nbsp;<!--your menu-->&nbsp; &nbsp; <li class="active"><a href="index.html"><?php&nbsp; &nbsp; &nbsp; &nbsp; echo $intitule;&nbsp; &nbsp; &nbsp;?></a>&nbsp; <?php&nbsp; &nbsp;//passing the id from first to action table for compare and retrieve that rows only&nbsp; &nbsp; &nbsp; &nbsp; $sql1&nbsp; = 'SELECT&nbsp; * FROM actions where idr= ' . $idr;&nbsp; &nbsp; &nbsp; &nbsp; $stmt1 = $conn->query($sql1);&nbsp; &nbsp;?>&nbsp; &nbsp; &nbsp;<ul class="dropdown">&nbsp; &nbsp; <?php&nbsp; &nbsp; &nbsp; &nbsp; while ($row1 = $stmt->fetch_assoc()) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $lien&nbsp; = $row1['lien'];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $intitulee = $row1['intitulee'];&nbsp; &nbsp; ?>&nbsp; &nbsp; &nbsp; &nbsp; <!--your submenu-->&nbsp; <li><a href="<?php echo $lien;?>"><?php echo $intitulee; ?></a></li>&nbsp; &nbsp; &nbsp;<?php&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; ?>&nbsp; &nbsp; &nbsp; &nbsp;</ul> <!--closing ul -->&nbsp; &nbsp; &nbsp;</li><?php&nbsp; }//closing while} //closing if?>&nbsp;

Answer 2

最终代码&nbsp; <?php&nbsp; &nbsp;//getting menu first&nbsp; &nbsp;$sql&nbsp; = 'SELECT id,intitule FROM rubriques ';&nbsp; &nbsp;$stmt = $conn->query($sql);&nbsp; &nbsp;if ($stmt->num_rows > 0) {&nbsp; &nbsp;while ($row = $stmt->fetch_assoc()){&nbsp; &nbsp;//getting values&nbsp;&nbsp;&nbsp; &nbsp; $intitule =$row['intitule'];&nbsp; &nbsp; $id = $row['id']; ?>&nbsp; &nbsp;<!--your menu--><li class="active"><a href="index.html"><?php&nbsp; &nbsp; echo $intitule;&nbsp;?></a>&nbsp;<?php&nbsp; &nbsp;//passing the id from first to action table for compare and retrieve that rows only&nbsp; &nbsp; &nbsp; &nbsp;$sql1&nbsp; = 'SELECT&nbsp; * FROM actions where idr= ' . $id;&nbsp; &nbsp; $stmt1 = $conn->query($sql1);&nbsp; &nbsp; &nbsp; &nbsp;?>&nbsp; &nbsp; <ul class="dropdown">&nbsp; &nbsp; &nbsp; <?php&nbsp; &nbsp; &nbsp; &nbsp; while ($row1 = $stmt1->fetch_assoc()) {&nbsp; &nbsp; &nbsp; &nbsp; $lien = $row1['lien'];&nbsp; &nbsp; &nbsp; &nbsp; $intitulee = $row1['intitulee'];?>&nbsp; &nbsp; <!--your submenu--><li><a href="<?php echo $lien;?>"><?php echo $intitulee; ?></a></li>&nbsp;<?php&nbsp; &nbsp; }&nbsp;?>&nbsp; &nbsp;</ul> <!--closing ul -->&nbsp;</li>&nbsp; &nbsp;<?php&nbsp; &nbsp; }}&nbsp; &nbsp; &nbsp; $conn->close();&nbsp; &nbsp; &nbsp;//closing if&nbsp; &nbsp; &nbsp;?>&nbsp;

Answer 3

看到这样写的查询会更常见：$sql = "SELECT r.id&nbsp; &nbsp; &nbsp;, r.intitule&nbsp; &nbsp; &nbsp;, a.intitulee&nbsp; &nbsp; &nbsp;, a.lien&nbsp; &nbsp; &nbsp;, a.idr&nbsp; &nbsp;&nbsp; FROM rubriques r&nbsp; JOIN actions a&nbsp; &nbsp; ON a.idr = r.id&nbsp;ORDER&nbsp;&nbsp; &nbsp; BY r.id;&nbsp; &nbsp; ";