php - 在 php 中显示多级数据库驱动的菜单

Question

我想显示一个数据库驱动的多级菜单，下面是我到目前为止尝试过的方法，但我没有得到想要的结果。谁能帮我解决这个问题。我得到的输出只是父 id 为 0 的主菜单，而不是子菜单项。

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

?>

我的数据库结构是

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

我想要的期望输出:

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

Answer 1

这是一个递归的解决方案。

代码已完全注释。

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

• 检查'current''entry'是否为'root'节点。

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


• 检查当前'entry'是否有'submenu'。

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

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

代码:

数据库连接:

$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);

子菜单查询:

/** -----------------------------------------------------------------
 * 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 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
 *
 * 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>';
?>

输出生成的 HTML:

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

生成的 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>