How can i create dynamic menu with sub-menu with php & mysql?

Question

I'm trying to create an dynamic menu from mysql table with php but i don't know how to create the non category menu, here is what i have so far and is working only the categories with submenus but the non categories ones they are not shown on echo.

MySQL category menu Table:

catid        name         group  
1      category name 1      0 -> whithout submenu
2      category name 2      1 -> category
3      category name 3      1 -> category
4      category name 4      0 -> whithout submenu
5      category name 5      0 -> whithout submenu

MySQL sub menus Table:

  id      name      parentid -> catid (category menu table)   
    1      name 1     2  
    2      name 2     3
    3      name 3     3
    4      name 4     3
    5      name 5     2

My Php code:

 <?php 
           
    function loop($array = array(), $parentID = 0) {
        if (!empty($array[$parentID])) {
            echo '<ul>';
            foreach ($array[$parentID] as $items) {
                echo '<li>';
                echo $items['name'];
                loop($array, $items['catid']);
                echo '</li>';
            }
            echo '</ul>';
        }
    }
    
    function  menu() {  
        $query = $db->query("SELECT m.name, m.parentid, c.group FROM `submenus` m LEFT JOIN( SELECT catid, group, parentid, ico, STATUS , MIN(name) AS category FROM `menu_categories` GROUP BY catid ) c ON m.parentid = c.catid");
        $array = array();
    
        if ($db->numRows($query)) {
            while ($rows = $db->fetch($query)) {
                $array[$rows['parentid']][] = $rows;
            }
            loop($array);
        }
    }
    echo menu();
 ?>

I updated my answer with your code and my change. What error(s) do you geht? — JollyRoger
– JollyRoger, Commented Jun 30, 2021 at 21:34
Your Sub-SELECT to the category table contains the parentid, wich according to your description above is within the submenu table. Also DO NOT USE reserved words like 'name' or 'group' etc. for table names or field names. — JollyRoger
– JollyRoger, Commented Jul 1, 2021 at 11:40

JollyRoger · Accepted Answer · 2021-06-30 21:33:44Z

1

Instead of a Left Join, wich leaves out the unpaired records in your category table, try a Full Join

<?php 
           
    function loop($array = array(), $parentID = 0) {
        if (!empty($array[$parentID])) {
            echo '<ul>';
            foreach ($array[$parentID] as $items) {
                echo '<li>';
                echo $items['name'];
                loop($array, $items['catid']);
                echo '</li>';
            }
            echo '</ul>';
        }
    }
    
    function  menu() {  
        $query = $db->query("SELECT m.name, m.parentid, c.group FROM `submenus` m FULL JOIN( SELECT catid, group, parentid, ico, STATUS , MIN(name) AS category FROM `menu_categories` GROUP BY catid ) c ON m.parentid = c.catid");
        $array = array();
    
        if ($db->numRows($query)) {
            while ($rows = $db->fetch($query)) {
                $array[$rows['parentid']][] = $rows;
            }
            loop($array);
        }
    }
    echo menu();
 ?>

edited Jun 30, 2021 at 21:33

answered Jun 30, 2021 at 21:14

JollyRoger

3291 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fido Over a year ago

Ty for the reply, i have replaced with full outer join and error, can you make me an example from my query?

Collectives™ on Stack Overflow

How can i create dynamic menu with sub-menu with php & mysql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related