Q29910284-display-multilevel-database-driven-menu

<?php // https://stackoverflow.com/questions/29910284/display-multilevel-database-driven-menu-in-php
//  use functions...

 /*
  * We will use Mysqli
  */

$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 comlete 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' ... wait...
        // We already have a routine to do that - 'processMenuEntry'...

        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>';
?>
<!DOCTYPE html>
<html>
<head>
    <title>Test Recursive Menu Builder</title>
</head>
<body>
<?= $htmlMenu ?>
</body>
</html>