display multilevel database driven menu in php

Question

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>

$r = mysqli_query($dbc, $q) or die(mysqli_error($dbc)); see if your query is failing. — Funk Forty Niner
– Funk Forty Niner, Commented Apr 28, 2015 at 4:23
you're not echoing $id = $rows['cat_id']; nor using $id anywhere else. — Funk Forty Niner
– Funk Forty Niner, Commented Apr 28, 2015 at 4:26
@Fred-ii- no its not failing. i am getting the output but only the main menu items not the sub menus — Kumarjit
– Kumarjit, Commented Apr 28, 2015 at 4:27
Instead of if($item['cat_parentid'] == $rows['cat_id']){ try if($item['cat_parentid'] == $id){ since you were using $id = $rows['cat_id']; but have removed it in an edit. — Funk Forty Niner
– Funk Forty Niner, Commented Apr 28, 2015 at 4:29
@Fred-ii- yes but its of no use, if i remove it also it makes no difference. I tried that too. then i changed it to if($item['cat_parentid'] == $rows['cat_id']){ — Kumarjit
– Kumarjit, Commented Apr 28, 2015 at 4:29

Ryan Vincent · Accepted Answer · 2015-04-30 02:59:30Z

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>

This code applies class to all the <ul class="dropdown"> but i want it in the starting <ul> tag only
@Kumarjit, I deliberately put them there. Sorry. Code changed to only add it to the outer 'unordered list'.
@Kumarjit, was fun to do - I learn a lot from doing these problems. Glad you find it useful.

Honza Haering · Accepted Answer · 2015-04-28 06:56:07Z

1

What you call $rows is actually one row. Then, in the foreach ($rows as $item) loop, it iterates through the columns of this row. So, there is no $item['cat_parentid']. Try to see the output of the $rows and $item with var_dump().

A draft idea of one possible solution that comes to my mind is to first iterate through the result rows and save the sub items in a parent item (note: some array initializations would have to be added here):

while ($row = mysqli_fetch_assoc($r)) {
    $menuItems[$row['cat_id']] = $row;

    $parentId = $row['cat_parentid'];
    $menuItems[$parentId]['sub_items'][] = $row['cat_id'];
}

And then iterate through the $menuItems array generating output, recursion would be great for this.

Also, ordering the sql results would be beneficial, to be sure that top menu items come first:

"SELECT * FROM catelogue WHERE cat_visibility = '1' ORDER BY cat_parentid ASC";

edited Apr 28, 2015 at 6:56

answered Apr 28, 2015 at 5:02

Honza Haering

8126 silver badges17 bronze badges

6 Comments

Honza Haering Over a year ago

Updated my answer with a draft of one possible way to go.

Kumarjit Over a year ago

I want my top <ul> to have a class="dropdown" in it. Is it possible in recursive function?

Mave Over a year ago

@Kumarjit: Sure, why not? Extend the recursive function with a $counter, and when it calls itself, add the variable: recursiveFunction($content, $counter+1). When $counter = 0, add the class.

Kumarjit Over a year ago

@Mave I don't know how to do that. Can you please show me. That will be a great help. Thanks

Kumarjit Over a year ago

@Mave Yes i got some code from internet, but the problem in that is if i put ` <ul> class ` to the top menu items, it also applies to sub menus as well.

|

Collectives™ on Stack Overflow

display multilevel database driven menu in php

2 Answers 2

5 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related