在php中显示多级数据库驱动菜单 [英] display multilevel database driven menu in php

问题描述

我想显示一个数据库驱动的多级菜单，下面是我迄今为止尝试过的，但我没有得到想要的结果.任何人都可以帮我解决这个问题.我得到的输出只是父 id 为 0 的主菜单，而不是子菜单项.

<?php//选择main_menu表中的所有行$q = "SELECT * FROM catelogue WHERE cat_visibility = '1'";$r = mysqli_query($dbc, $q);echo "
";而 ($rows = mysqli_fetch_assoc($r)) {如果($rows['cat_parentid'] == 0){echo "<li><a href='#'>".$rows['cat_name'] ."</a>";echo "
";foreach ($rows 作为 $item) {if ($item['cat_parentid'] == $rows['cat_id']) {echo "<li><a href='#'>".$item['cat_name'] ."</a>";echo "</li>";}}echo "</ul>";echo "</li>";}}echo "</ul>";?>

我的数据库结构是

-----------------------------------------|cat_id |猫名|cat_parentid |-----------------------------------------|1 |首页 |0 ||2 |关于 |0 ||3 |联系 |0 ||4 |历史 |2 ||5 |服务 |2 |-----------------------------------------

我想要的期望输出:

<li><a href='#'>首页</a></li><li><a href='#'>关于</a><ul><li><a href='#'>历史</a></li><li><a href='#'>服务</a></li>
<li><a href='#'>联系方式</a></li>

解决方案

这是一个递归解决方案.

代码已完全注释.

processMenuEntry 例程中有两个有用的检查可以方便地完成，以便您可以决定是否要进行不同的操作.

• 检查当前"条目"是否为根"节点.

  $isRoot = $currentEntry['cat_id'] == 0;//做第一次"处理

• 检查当前条目"是否有子菜单".

  if (!empty($subMenu)) { ...

Q29910284-display-multilevel-database-driven-menu.php

代码:

数据库连接:

$DB_HOST = "localhost";$DB_USER = "测试";$DB_PASSWORD = "测试";$DB_TO_USE = "testmysql";$dbc = new mysqli($DB_HOST, $DB_USER, $DB_PASSWORD, $DB_TO_USE);

子菜单查询:

/** -----------------------------------------------------------------* 选择给定条目 ID 的所有菜单条目** 注意:进行参数替换"而不是使用是安全的* 'prepared queries' with placeholders as this 'entry Id' never* 来自外部来源.** @param mysqli $dbc* @param 整数 $currentEntryId* @return 菜单项数组*/函数 selectSubMenu($currentEntryId, $dbc){$sql = "SELECT cat_id, cat_name, cat_parent_id来自目录哪里 cat_parent_id = {$currentEntryId}按 cat_id 排序";$resultSet = mysqli_query($dbc, $sql);返回 $resultSet->fetch_all(MYSQLI_ASSOC);}

处理当前菜单条目:

/** --------------------------------------------------------------------------* 处理当前菜单项 - 它将以 HTML 格式返回一个完整的菜单** 这些需要知道当前条目是否有子菜单和* 因此需要为当前条目生成一个无序列表".** 唉，它也必须不显示根"条目的列表项文本"* 但处理子菜单".* 这使 <li> 复杂化了.当前条目文本生成略.** @param array $currentEntry - 一行* @param array $subMenu - 多行 - 可能为空* @param mysqli $dbc - 数据库连接* @return string - 菜单的 HTML*/函数 processMenuEntry($currentEntry, 数组 $subMenu, $dbc) {$outMenu = '';$isRoot = $currentEntry['cat_id'] == 0;//做第一次"处理//将当前条目文本显示为列表项"$outMenu .= !$isRoot ?"<li><a href='#'>".$currentEntry['cat_name'] .</a>":'';//是否有子菜单...if (!empty($subMenu)) {//我们需要添加一个 
 的完整子菜单；... </ul>//作为无序列表的子菜单的开始——决定需要什么如果($isRoot){$outMenu .='
';}别的 {$outMenu .='
';}//将子菜单的每个条目显示为列表项"foreach ($subMenu 作为 $submenuEntry) {$outMenu .= processMenuEntry($submenuEntry,selectSubMenu($submenuEntry['cat_id'], $dbc),$dbc);}//关闭当前子菜单 - 终止无序列表$outMenu .= '</ul>';}//终止当前列表项$outMenu .= !$isRoot ?'</li>':'';返回 $outMenu;};

处理所有菜单项:

/* -------------------------------------------------------------------* 处理所有菜单项** 我们需要一个完整的菜单树"，其中包含一个未提供的根"* 在数据库中.我认为应该是.无论如何，我需要一个.**初始化树走"我总是觉得有趣"；-/*/$root = array('cat_id' => 0, 'cat_name' => '', 'cat_parent_id' => 0);//构建输出菜单$outMenu = processMenuEntry($root,selectSubMenu($root['cat_id'], $dbc),$dbc);//将其包裹在 
 中$htmlMenu = '<div style="border: 1px solid red">'.$outMenu.'</div>';?>

输出生成的HTML:

<头><title>测试递归菜单生成器</title><身体><?= $htmlMenu ?></html>

生成的 HTML

<头><title>测试递归菜单生成器<身体><div style="border: 1px 纯红色"><ul class="下拉菜单"><li><a href='#'>首页</a><li><a href='#'>关于</a><ul><li><a href='#'>历史</a><li><a href='#'>服务</a><li><a href='#'>联系方式</a>

</html>

I want to display a database driven Multilevel menu and below is what I have tried so far, but I am not getting the result as desired. Can any one help me to fix this. the output I am getting is only the main menu with parent id as 0, but not the sub menu items.

<?php
include('system/connection.php');
?>
<?php

//select all rows from the main_menu table
$q = "SELECT * FROM catelogue WHERE cat_visibility = '1'";
$r = mysqli_query($dbc, $q);

echo "<ul class='dropdown'>";
while ($rows = mysqli_fetch_assoc($r)) {
    if ($rows['cat_parentid'] == 0) {
        echo "<li><a href='#'>" . $rows['cat_name'] . "</a>";
        echo "<ul>";
        foreach ($rows as $item) {
            if ($item['cat_parentid'] == $rows['cat_id']) {
                echo "<li><a href='#'>" . $item['cat_name'] . "</a>";
                echo "</li>";
            }
        }
        echo "</ul>";
        echo "</li>";
    }
}
echo "</ul>";

?>

My Database structure is

-----------------------------------------
| cat_id   | cat_name    | cat_parentid |
-----------------------------------------
|  1       |  Home       |  0           |
|  2       |  About      |  0           |
|  3       |  Contact    |  0           |
|  4       |  History    |  2           |
|  5       |  Services   |  2           |
-----------------------------------------

Desired Output I want:

<ul class="dropdown">
  <li><a href='#'>Home</a></li>
  <li><a href='#'>About</a>
    <ul>
      <li><a href='#'>History</a></li>
      <li><a href='#'>Services</a></li>
    </ul>
  </li>
  <li><a href='#'>Contact</a></li>
</ul>

解决方案

Here is a recursive solution.

The code is fully commented.

There are two useful checks in the processMenuEntry routine that can conveniently be done so that you can decide if you want different actions to happen.

• Check whether the 'current' 'entry' is the 'root' node.

  $isRoot = $currentEntry['cat_id'] == 0; // do 'First time' processing

• Check whether the current 'entry' has a 'submenu'.

  if (!empty($subMenu)) { ...

Q29910284-display-multilevel-database-driven-menu.php

The code:

Database connection:

$DB_HOST     = "localhost";
$DB_USER     = "test";
$DB_PASSWORD = "test";
$DB_TO_USE   = "testmysql";

$dbc = new mysqli($DB_HOST, $DB_USER, $DB_PASSWORD, $DB_TO_USE);

Submenu Query:

/** -----------------------------------------------------------------
 * Select all the menu entries for a given entry Id
 *
 * Note: it is safe to do 'parameter substitution' rather than using
 *       'prepared queries' with placeholders as this 'entry Id' never
 *       comes from an external source.
 *
 * @param mysqli $dbc
 * @param integer $currentEntryId
 * @return array of menu entries
 */
function selectSubMenu($currentEntryId, $dbc)
{
    $sql = "SELECT cat_id, cat_name, cat_parent_id
                   FROM catalogue
                   WHERE cat_parent_id = {$currentEntryId}
                   ORDER BY cat_id";
    $resultSet = mysqli_query($dbc, $sql);
    return $resultSet->fetch_all(MYSQLI_ASSOC);
}

Process Current Menu Entry:

/** --------------------------------------------------------------------------
 * Process the current menu enty - it will return a complete menu as HTML
 *
 * These needs to know whether the current entry has a submenu and
 * will therefore need to generate an 'unordered list' for the current entry.
 *
 * Alas, it also has to not display the 'list item text' for the  'root' entry
 * but process the 'submenu'.
 * This complicates the <li> current entry text generation slightly.
 *
 * @param array $currentEntry - one row
 * @param array $subMenu - many rows - may be empty
 * @param mysqli $dbc - database connection
 * @return string - the HTML for the menu
 */
function processMenuEntry($currentEntry, array $subMenu, $dbc)  {
    $outMenu = '';
    $isRoot = $currentEntry['cat_id'] == 0; // do 'First time' processing

    // display the current entry text as a 'list item'
    $outMenu .= !$isRoot ? "<li><a href='#'>" . $currentEntry['cat_name'] . "</a>" : '';

    // does it have a submenu...
    if (!empty($subMenu)) { // we need to add a complete submenu of <ul> ... </ul>

        // Start of the submenu as an unordered list -- decide what is required
        if ($isRoot) {
            $outMenu .= '<ul class="dropdown">';
        }
        else {
            $outMenu .= '<ul>';
        }

        // Display each entry of the submenu as a 'list item' 
        foreach ($subMenu as $submenuEntry) {
            $outMenu .= processMenuEntry($submenuEntry,
                                    selectSubMenu($submenuEntry['cat_id'], $dbc),
                                    $dbc);
        }

        // close the current submenu - terminate the unordered list
        $outMenu .= '</ul>';
    }

    // terminate the current list item
    $outMenu .= !$isRoot ? '</li>' : '';
    return $outMenu;
};

Process all the menu entries:

/* -------------------------------------------------------------------
 * Process all the menu entries
 *
 * We need a complete menu 'tree' that includes a 'root' which is not provided
 * in the database. I think it should be. Whatever, i need one.
 *
 * Initializing a 'tree walk' i always find 'interesting' ;-/
 */
$root = array('cat_id' => 0, 'cat_name' => '', 'cat_parent_id' => 0);

// build the output menu
$outMenu = processMenuEntry($root,
                           selectSubMenu($root['cat_id'], $dbc),
                           $dbc);

// wrap it in a <div>
$htmlMenu = '<div style="border: 1px solid red">'
            . $outMenu
            .'</div>';
?>

Output the generated HTML:

<!DOCTYPE html>
<html>
<head>
    <title>Test Recursive Menu Builder</title>
</head>
<body>
<?= $htmlMenu ?>
</body>
</html>

The generated HTML

<!DOCTYPE html>
<html>
  <head>    
    <title>Test Recursive Menu Builder
    </title>
  </head>
  <body>
    <div style="border: 1px solid red">
      <ul class="dropdown">
        <li>
        <a href='#'>Home</a>
        </li>
        <li>
        <a href='#'>About</a>
        <ul>
          <li>
          <a href='#'>History</a>
          </li>
          <li>
          <a href='#'>Services</a>
          </li>
        </ul>
        </li>
        <li>
        <a href='#'>Contact</a>
        </li>
      </ul>
    </div>
  </body>
</html>

这篇关于在php中显示多级数据库驱动菜单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！