First of all, do you have submenu items in your $menu_items variables? If you posted here a result of print_r($menu_items) it would clear up a lot what structure it has.
Ideally, you need to make it a multi-dimensional array with a structure like this:
Array
(
[0] => Array
(
[name] => Home
[link] => /
)
[1] => Array
(
[name] => Information
[link] => /info
[subitems] => Array
(
[0] => Array
(
[name] => About Us
[link] => /about-us
)
[1] => Array
(
[name] => Contact Info
[link] => /contact
)
[2] => Array
(
[name] => Our shops
[link] => /shops
)
)
)
[2] => Array
(
[name] => Products
[link] => /products
[subitems] => Array
(
[0] => Array
(
[name] => Electric Equipment
[link] => /electric
[subitems] => Array
(
[0] => Array
(
[name] => Light Bulbs
[link] => /electric/light-bulbs
)
[1] => Array
(
[name] => Mowers
[link] => /electric/mowers
)
[2] => Array
(
[name] => Fridges
[link] => /electric/fridges
)
)
)
[1] => Array
(
[name] => Electronic Equipment
[link] => /electronic
[subitems] => Array
(
[0] => Array
(
[name] => Computers
[link] => /electronic/computers
)
[1] => Array
(
[name] => Laptops
[link] => /electronic/laptops
)
)
)
)
)
)
Each menu item has two mandatory keys: name and link. Optionally, there can be subitems, which is an array of items deeper under the branch. As you can see the nesting level can go deeper and deeper indefinitely. In order to achieve this structure you need to query your database table recursively - search for “recursive functions in PHP” and you will know what I mean.
Then in order to display this kind of array in html you need another recursive function - in your template - that will iterate over it and all its branches and output a properly nested set of <ul> and <li> tags.
This may seem too convoluted for some cases and reading recursively from the database is not very efficient because you will have many SELECTs - unless your database supports recursion (MySQL doesn’t). So you can take some shortcuts - to get rid of recursion you can code for, say, maximum of 2 levels deep - then you have a simple non-recursive function but you have 3 foreach loops - each one nested in another. This might be sufficient - however, still it’s inefficient with SELECTs.
There are some workarounds for this problem, one of them is adding another column to your table and apart from parent_id store a full path of row IDs to every category, for example a category Products > Electronic Equipment > Computers will have path 0003/0005/0010/ - supposing each number is an ID of the corresponding category in the path. Fixed-length zero-padded numbers allow you to easily calculate the depth level of each category by dividing the length by 5 in this case.
Then certain queries become much simpler. To get all subitems of Products you can use WHERE path LIKE '0003/%'. The only problem now is correct order. You can do this to select all your menu:
SELECT * FROM site_navigation ORDER BY path
and you’ll get a properly nested result but in the order of ID numbers, which doesn’t make sense. Therefore, you can add another column global_position - which is a number denoting position of each item menu within the whole tree regardless of its depth. So then you can quickly get all your menu like this:
SELECT *, FLOOR(LENGTH(path)/5) AS depth
FROM site_navigation ORDER BY global_postion
and depth will tell you how many levels deep is each menu item, which you can use to properly load this data into your menu variable.
The downside is that your table will become denormalized and on each update you will need to update additional columns: path and global_position, which can be resource hungry but in most ordinary scenarios pretty acceptable for occasional site updates. You can calculate path and global_position at any time (with a recursive function) according to the parent_id column, which is the one that truly defines your tree structure and must be regarded as source data for the denormalized columns.
I implemented a similar solution many years ago in an online shop for the category tree (about 400 items) and it works pretty well and is fast. But it adds some complexity to the system so I would certainly prefer to avoid such workarounds if only possible.