Yoko Yoko - 1 month ago 26
MySQL Question

Dynamic menu php bootstrap mysql

I have a problem with this code, And I can have the 1st level, On the second level I have an id number and the 3eme level nothing.

I need to find informations on 4 levels of deep

Also the code produce an error :

Illegal string offset 'label'
on this line :

foreach($menu_sub[$submenus['sub_menu']] as $sub2_key => $submenus2) {


result

index
---- 8
--------- not appear
--------- not appear
---- 3
---- 2
---- I

Configuration
---- 9
--------- not appear
--------- not appear
----1
----1
----M
----1

Catalogue


The DB

CREATE TABLE `administrator_menu` (
`id` int(11) NOT NULL,
`link` mediumtext NOT NULL,
`parent_id` int(11) NOT NULL,
`sort_order` int(11) NOT NULL,
`class` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `administrator_menu`
--

INSERT INTO `administrator_menu` (`id`, `link`, `parent_id`, `sort_order`, `class`) VALUES
(1, '', 0, 2, ''),
(3, '', 0, 1, ''),
(5, '', 0, 3, ''),
(6, '', 0, 4, ''),
(7, '', 3, 1, ''),
(8, '', 3, 2, ''),
(9, '', 1, 1, ''),
(10, '', 9, 0, '');

ALTER TABLE `administrator_menu`
ADD PRIMARY KEY (`id`);

ALTER TABLE `administrator_menu`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

CREATE TABLE `administrator_menu_description` (
`id` int(11) NOT NULL,
`label` varchar(255) NOT NULL,
`language_id` int(11) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `administrator_menu_description`
--

INSERT INTO `administrator_menu_description` (`id`, `label`, `language_id`) VALUES
(3, 'Accueil', 1),
(7, 'Administration', 1),
(7, 'Administration', 2),
(5, 'Catalog', 2),
(5, 'Catalogue', 1),
(1, 'Configuration', 1),
(1, 'Configuration', 2),
(10, 'Configuration générale', 1),
(10, 'general Configuration', 2),
(3, 'Index', 2),
(8, 'Index Catalogue', 1),
(8, 'Index Shop', 2),
(9, 'Ma boutique', 1),
(9, 'My shop', 2);

ALTER TABLE `administrator_menu_description`
ADD PRIMARY KEY (`id`,`language_id`),
ADD KEY `label` (`label`);

ALTER TABLE `administrator_menu_description`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;


the script

result of sql request

table administrator_menu

id parent_id sort_order class
1 0 2
3 0 1
5 0 3
6 0 4
7 3 1
8 3 2
9 1 1
10 9 0


Note :
parent_id is the number of id selected to create an hierarchical menu
For example the
id = 10
and
parent_id = 9
, we are on the 3em level

For example the
id = 9
and
parent_id = 1
, we are on the 2em level

For example the
id = 1
and
parent_id = 0
, we are on the 1st level

table description menu

id lable language_id
1 Configuration 1
1 Configuration 2
3 Accueil 1
3 Index 2
5 Catalogue 1
5 Catalog 2
7 Administration 1
7 Administration 2
8 Index Catalogue 1
8 Index Shop 2
9 Ma boutique 1
9 My shop 2
10 Configuration générale 1
10 general Configuration 2

<?php
// Select all entries from the menu table
$Qmenus = $Db->prepare('SELECT a.id,
a.link,
a.parent_id,
a.class,
a.sort_order,
amd.label
FROM :table_administrator_menu a,
:table_administrator_menu_description amd
where a.id = amd.id
and amd.language_id = :language_id
ORDER BY a.parent_id,
a.sort_order
');

$Qmenus->bindInt(':language_id', $Language->getId());
$Qmenus->execute();

$Qmenus = $Qmenus->fetchAll();
?>
<!-- Navigation -->
<nav class="navbar navbar-default navbar-static-top" role="navigation" style="margin-bottom: 0">
<div class="navbar-default sidebar" role="navigation">
<div class="sidebar-nav navbar-collapse">
<ul class="nav" id="side-menu">

<?php
$menu_parent = array();
$menu_sub = array();
foreach ($Qmenus as $menus) {
if ($menus['parent_id'] == 0) {
$menu_parent[$menus['id']] = $menus;
} else {
if (isset($menu_parent[ $menus['parent_id']])) {
$menu_parent[$menus['parent_id']]['sub_menu'] = $menus['id'];
$menu_sub[$menus['id']] = $menus;
} else if (isset($menu_sub[$menus['parent_id']])) {
$menu_sub[$menus['parent_id']]['sub_menu'] = $menus['id'];
$menu_sub[$menus['id']] = $menus;
}
}
}

foreach($menu_parent as $key => $menus) {
echo '<li><a href="#"><i class="fa fa-sitemap fa-fw"></i>' . $menus['label'] . '<span class="fa arrow"></span></a>';

if (!empty($menus['sub_menu'])) {
echo '<ul class="nav nav-second-level">';
foreach($menu_sub[$menus['sub_menu']] as $sub_key => $submenus) {
echo '<li><a href="#">' . $submenus['label'] . '</a>';
if (!empty($submenus['sub_menu'])) {

foreach($menu_sub[$submenus['sub_menu']] as $sub2_key => $submenus2) {

echo '<li><a href="#">' . $submenus2['label'] . '</a>';

if (!empty($submenus2['sub_menu'])) {

foreach($menu_sub[$submenus2['sub_menu']] as $sub3_key => $submenus3) {
echo '<li><a href="#">' . $submenus3['label'] . '</a></li>';
}
}
echo '</li>';
}
}
echo '</li>';
}
echo '</ul>';
}
echo '</li>';
}
?>
</ul>
</div>
<!-- /.sidebar-collapse -->
</div>
<!-- /.navbar-static-side -->
</nav>

Answer

Your code will only generate two level menu, if you need to go through any level you want, i think that you need to use recursion.

Here is an example based on your database structure and samples. In the example we will generate a menu in different levels, the English labels are used for menu items names.

<?php
$db = new PDO('mysql:host=localhost;dbname=testdb', 'root', '');



function drawMenu($db, $parent, $level = null){
    $m = $db->prepare(" SELECT * FROM 
                        administrator_menu, administrator_menu_description
                        where administrator_menu.id = administrator_menu_description.id
                        and language_id = 2
                        and parent_id = $parent");
    $m->execute();


    foreach ($m->fetchAll() as $menu_row) {
        $m = $db->prepare("SELECT count(*) FROM administrator_menu where parent_id = $menu_row[id]");
        $m->execute();
        // The item is parent, so do recursion again
        if($m->fetchAll()[0][0] !== '0' && $level !== 0){ 
            echo "<li>" . $menu_row['label']."<ul>";
            drawMenu($db, $menu_row[0], $level - 1);
            echo "</ul></li>";
        }else{ // The item is a leaf or we reach the end level, i.e. base case, so do print the item label 
            echo "<li>" . $menu_row['label'] . "</li>";
        }

    }

}
?>
<!DOCTYPE html>
<html>
<head>
    <title></title>
</head>
<body>
<?php
echo "<div> <ul>";
drawMenu($db, 0, null); // all levels
echo "</ul></div>";
echo "--------------------------------------------------------";
echo "<div> <ul>";
drawMenu($db, 0, 0); // level 0
echo "</ul></div>";
echo "--------------------------------------------------------";
echo "<div> <ul>";
drawMenu($db, 0, 1); // level 1
echo "</ul></div>";
echo "--------------------------------------------------------";
echo "<div> <ul>";
drawMenu($db, 0, 2); // level 2
echo "</ul></div>";
?>
</body>
</html>

To draw all levels:

echo "<div> <ul>";
drawMenu($db, 0, null); // all levels
echo "</ul></div>";

The drawMenu function works as following:

  • First we pass a $db object to make database queries, $parent that the tree will start with and $level for the level of the tree.
  • The function will start by selecting the child of the given $parent and go in loop for each one foreach ($m->fetchAll() as $menu_row) {...}.
  • In the loop we have two cases:

    1. The item is a leaf i.e. not a parent for other items, or we reach the final level of tree. This case is call the Base case, in which the recursion will stop and return a value echo "<li>" . $menu_row['label'] . "</li>";

    2. The item is a parent, in this case we call the drawMenu function again with the item id $menu_row[0] as a parent and $level - 1 to make sure to stop when reach the end of levels.

Test the code and change it to fit your needs.