Dynamic menu php bootstrap mysql

Question

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>

Post the full tables structures and sample data – Karim Harazin Nov 01 '16 at 20:09 — Karim Harazin, Nov 01 '16 at 20:09
add db information in explanation, tk – Yoko Nov 01 '16 at 22:15 — Yoko, Nov 01 '16 at 22:15

score 0 · Accepted Answer · answered Nov 02 '16 at 06:38

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.

I'm trying to make a bootstrap menu too through PHP, can you please explain where/how `$level` is set? Is it a row in my DB I have to add? My question is over here but I think this is the solution to my question. https://stackoverflow.com/questions/49732477/php-bootstrap-4-navigation-menu-with-sub-menus EDIT: Nevermind, it's set in the function, I just overreed it! — Alex, Apr 11 '18 at 14:22

Dynamic menu php bootstrap mysql

1 Answers1

Linked