How to Build Unlimited Level of Menu Through PHP and MySQL

How to build unlimited level of menu through PHP and mysql

You need to use recursive functions for this. Technically, there's a few ways to do it, but recursion is really the best option here.

Here's the basic gist of how it would work:

function drawMenu ($listOfItems) {
    echo "<ul>";
    foreach ($listOfItems as $item) {
        echo "<li>" . $item->name;
        if ($item->hasChildren()) {
            drawMenu($item->getChildren()); // here is the recursion
        }
        echo "</li>";
    }
    echo "</ul>";
}

The properties and methods of $item are just examples, and I'll leave it up to you to implement these however you need to, but I think it gets the message across.

Menu from database with unlimited sub-menus/items?

So after some trial and error i figure it out. It really helped to look at Sheikh Azad code.
The only difference is in my function i have AND menu_sub='$sub' to make it not going loop the main menu item if the id is the same.. If that make sense.

The function

function mos_get_menu_editor($menuId, $sub) {

    require(dirname(__FILE__) . '/config.php');

    $item = "   SELECT id, menu_name, menu_data, menu_order, menu_sub
                FROM mos_menuItem 
                WHERE menu_parent_id='$menuId' 
                AND menu_sub='$sub' 
                ORDER BY menu_order";
    $itemResult = mysqli_query($conn, $item);

        echo '<ul>';
        
        while ($itemRow = mysqli_fetch_row($itemResult)) {

            echo '<li><a href="'. $itemRow[2] .'" class="menu-item ' .($sub ? 'sub-menu' : '').'">'. $itemRow[1] .'</a></li>';

                mos_get_menu_editor($itemRow[0], 1);

        }
        echo '</ul>';

    mysqli_close($conn);
}

PHP/MySQL - building a nav menu hierarchy

Dealing with the data structure as you have it will often involve recursion or multiple queries to build the tree.

Have you considered other ways of storing a hierarchy? Check out modified pre-order traversal - here's a nice PHP based article about this.

Multilevel menu in mysql/php

If you want to load everything with one query, what makes sense because most likely you will not have thousands of items in the menu, then you can simplify your task and do more with PHP instead of overcomplicating the SQL query.

So first you just load everything with the simplest possible query:

$items = query('SELECT * FROM `menu_items` WHERE categories_id = ? ORDER BY `ord` asc');

In that way the order will be preserved after dividing all items into submenus. Then you just need one loop to build menu structure.

$itemsById = [];
$children = [];
foreach ($items as $item) {
    $itemsById[$item['id']] = $item;
    $children[$item['parent']][] = $item['id'];
}

And you can print it recursively starting from $children[0].

Dynamic multi level Menu and submenu php mysql

As your menu and sub-menu are in different table you can select menu first and then select sub-menu depending on the menu selected .i.e :

 <?php
//getting menu first
$sql  = 'SELECT id,intitule FROM rubriques';
$stmt = $conn->query($sql);
if ($stmt->num_rows > 0) {
   while($row = $stmt->fetch_assoc()){
       //getting values  
        $intitule = $row['intitule '];
        $idr = $row['id']; ?>
       <!--your menu-->
    <li class="active"><a href="index.html"><?php
        echo $intitule;
     ?></a>
  <?php
   //passing the id from first to action table for compare and retrieve that rows only
        $sql1  = 'SELECT  * FROM actions where idr= ' . $idr;
        $stmt1 = $conn->query($sql1);
   ?>
     <ul class="dropdown">
    <?php
        while ($row1 = $stmt->fetch_assoc()) {
            $lien  = $row1['lien'];
            $intitulee = $row1['intitulee'];
    ?>

        <!--your submenu-->
  <li><a href="<?php echo $lien;?>"><?php echo $intitulee; ?></a></li>

     <?php

        }
    ?>
       </ul> <!--closing ul -->

     </li>
<?php
  }//closing while
} //closing if
?> 

Generate One Level Menu From MySQL Table

First select all top menu in the first query and then create a loop the will retrieve it all..Inside the first loop create another query that will retrieve all the child menu of the selected menu, if there is any..That's why we also have the if statement..Inside the if statement create the loop to retrieve all the child menu...Here's the code:

    $sql = "SELECT * FROM `table` WHERE `Parent` = '0' ORDER BY `Order` ASC";
    $result = mysql_query($sql) or die ("Error: Query Failed! " .mysql_error());
    $output = "<ul>";
    while ($rs = mysql_fetch_array($result)) {
       $output .= "<li><a href='".$rs['Link']."'>".$rs['Name']."</a></li>";
       $sql2 = "SELECT * FROM `table` WHERE `Parent` = '".$rs['ID']."' ORDER BY `Order` ASC";
       $result2 = mysql_query($sql2) or die ("Error: Query Failed! " .mysql_error());
       if (mysql_num_rows($result2) != 0) {
          $output .= "<ul>";
          while ($rs2 = mysql_fetch_array($result2)){
             $output .= "<li><a href='".$rs2['Link']."'>".$rs2['Name']."</a></li>"
          }
          $output .= "</ul>";
       }
    }
    $output .="</ul>";
    echo $output;

Haven't tested it but this should do it.

---

Related Topics