CODEWITHSUNDEEP
phpmysqlmysqli
Linus Karlsson
Linus Karlsson

Reputation: 35

Menu from database with unlimited sub-menus/items?

I'm trying to build this function that display a menu with sub menus from a database. Something that can show like sub-sub-sub-menus and so on... If that's ever something I need.

enter image description here

In the database i have 2 tables, menu and menuItem.

menu

enter image description here

menuItem

enter image description here

php file

function get_menu($id) {

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

// main base
$menu = "   SELECT id, menu_name, menu_data, menu_order
            FROM mos_menuItem 
            WHERE menu_parent_id='$id' 
            AND menu_sub='0' 
            ORDER BY menu_order";
$menuResult = mysqli_query($conn, $menu);

echo '<ul>';

while ($menuRow = mysqli_fetch_row($menuResult)) {
    // main menu
    echo '<li><a href="'. $menuRow[2] .'" class="menu-item parent-menu">'. $menuRow[1] .'</a></li>';
    
        // sub base
        $sub = "SELECT id, menu_name, menu_data, menu_order, menu_sub 
                    FROM mos_menuItem 
                    WHERE menu_parent_id='$menuRow[0]' 
                    AND menu_sub>='1' 
                    ORDER BY menu_order";
        $subResult = mysqli_query($conn, $sub);
        
        echo '<ul>';
        
        while ($subRow = mysqli_fetch_row($subResult)) {
            echo '<li><a href="'. $subRow[2] .'" class="menu-item sub-menu">'. $subRow[1] .'</a></li>';
        }   

        echo '</ul>';
}
echo '</ul>';

mysqli_close($conn);
}

so I need to loop something inside itself? I'm not sure where to go from here.

Upvotes: 0

Views: 1103

Answers (2)

Linus Karlsson
Linus Karlsson

Reputation: 35

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);
}

Upvotes: 0

Sheikh Azad
Sheikh Azad

Reputation: 353

Recursion is your best friend here.

I am not sure what purpose is served by the menu_sub field. I assume it tells you at what level it is in the menu hierarchy. If that is the case, then you can safely ignore it (menu_parent_id would suffice) to create this multi-level menu hierarchy.

function getMenus($menu_id, $is_sub=false){
   $sql = "SELECT id, menu_name, menu_data, menu_order
        FROM mos_menuItem 
        WHERE menu_parent_id='$menu_id' 
        ORDER BY menu_order";
   $mResult = mysqli_query($conn, $sub);
   echo '<ul>';

   while ($mRow = mysqli_fetch_row($mResult)) {
       echo '<li><a href="'. $mRow[2] .'" class="menu-item '.($is_sub ? 'sub-menu' : '').'">'. $mRow[1] .'</a>';
        getMenus($mRow[0], true);
        echo '</li>';
    }  
    echo '</ul>';
}

This does have one drawback where if you want the hierarchy to start from a parent other than 0 (I assume your root menu_id is 0), that particular menu would not show up in the hierarchy.

Upvotes: 1

Related Questions