Dynamic multi level Menu with sorting – how to find out the menu level – PHP, MySQL

I have a multilevel menu, a submenu with sorting. I need to find out the menu level so I can assign some characters as bullets.

I want to list it using select and add in

lvl2 – <option>str_repeat ("&nbsp;", 2) Item Lorem ipsum

lvl3 – <option>str_repeat ("&nbsp;", 3) Item Lorem ipsum

lvl4 – <option>str_repeat ("&nbsp;", 4) Item Lorem ipsum

Any idea how to do this?

I get max on lv2

CREATE TABLE `menu` (
  `id` int(11) UNSIGNED NOT NULL,
  `title` varchar(50) NOT NULL,
  `parent` int(11) UNSIGNED DEFAULT NULL,
  `page` varchar(45) DEFAULT NULL,
  `sort_order` tinyint(4) NOT NULL DEFAULT '100',
  `lang` varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `menu` (`id`, `title`, `parent`, `page`, `sort_order`, `lang`) VALUES
(1, 'Home', NULL, 'index.php', 1, ''),
(2, 'Products', NULL, NULL, 2, ''),
(3, 'Services', NULL, NULL, 1, ''),
(4, 'About', NULL, 'about.php', 100, ''),
(6, 'Service 1', 3, 'service1.php', 110, ''),
(7, 'Service 2', 3, 'service2.php', 100, ''),
(8, 'Product 1', 2, 'product1.php', 100, ''),
(9, 'Product 2', 2, 'product2.php', 100, ''),
(10, 'Product 3', 2, 'product3.php', 100, ''),
(11, 'Product 4', 2, 'product4.php', 100, ''),
(12, 'Product 5', 2, 'product5.php', 50, ''),
(14, 'Contact', NULL, 'contact.php', 100, ''),
(15, 'Service 1.1', 6, 'service1.1.php', 100, ''),
(16, 'Service 1.2', 6, 'service1.2.php', 100, ''),
(17, 'Service 1.1.1', 15, NULL, 100, ''),
(18, 'Service 2.1', 7, NULL, 100, ''),
(19, 'Service 2.2', 7, NULL, 100, ''),


ALTER TABLE `menu`
  ADD PRIMARY KEY (`id`),
  ADD KEY `menu_id` (`parent`);

ALTER TABLE `menu`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;


ALTER TABLE `menu`
  ADD CONSTRAINT `menu_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `menu` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

PHP

public function displayMenu($parent = null)
{
    if ($parent == null) {
        $query = $this->menuManager->getPublicMenus()->where('parent', null)->order('sort_order');
    } else {
        $query = $this->menuManager->getPublicMenus()->where('parent = ?', $parent)->order('sort_order');
    }

    if ($this->menuManager->getPublicMenus()->count() > 0) {
        $menu = '';
        foreach ($query as $row) {
            if ($row->parent > 0) {
                $menu .= "<option class=''>" . '&nbsp;&nbsp;' . $row->title . '</option>';
            } else {
                $menu .= "<option class=''>" . $row->title . '</option>';
            }
            $menu .= $this->displayMenu($row->id);
            }
        return $menu;
    }
}

Answer

The best option would be to have the levels indicated in a field in your database — instead of calculating them on the fly over and over again. If that’s not an option, then…

We prepare the results into an array with the id for index key and add a level field:

$menu = [];
foreach($query as $row) {
    $row->level = null;
    $menu[$row->id] = $row;
}

Once that’s done, the rest is straight-forward:

foreach($menu as &$item) {
    if (is_null($item->parent)) {
        $item->level = 1;
    } else {
        $item->level = $menu[$item->parent]->level + 1;
    }
}

In other words: If parent is null, the item is level 1. Otherwise, it’s the level of its parent + 1. This results in the following (relevant fields shown) array:

array(17) {
    [1] · object(stdClass)#2 (7) {
        ["title"] · string(4) "Home"
        ["parent"] · NULL
        ["level"] · int(1)
    } ...
    [6] · object(stdClass)#6 (7) {
        ["title"] · string(9) "Service 1"
        ["parent"] · int(3)
        ["level"] · int(2)
    } ...
    [15] · object(stdClass)#14 (7) {
        ["title"] · string(11) "Service 1.1"
        ["parent"] · int(6)
        ["level"] · int(3)
    } ...
    [17] · object(stdClass)#16 (7) {
        ["title"] · string(13) "Service 1.1.1"
        ["parent"] · int(15)
        ["level"] · int(4)
    }
}

Then you can simply do str_repeat (" ", $item['level']). There’s a caveat to mind here though: It will not work if your menu items are “out of order”, ie. if there’s a child before its parent.