Northern Northern - 9 months ago 48
MySQL Question

Multilevel menu in mysql/php

I'm trying to write a php code that would fetch me a multilevel menu, with unlimited depth. I want to be able to do it with just one query and one loop.
Here's what I have:
I have a menu_items table with the following columns:

categories_id (overall menu id)
title (Text title)
link (Link)
ord (the order within the category itself)
parent (Parent item, which is basically another item, different from categories_id)
depth (Menu level. For whatever reason, I think it's going to help me to know the depth of every particular item)

All menu items are linked to a particular menu by categories_id, and all of them are linked to their parent item by "parent" with the top level items having 0 for a parent.

Now, I need mysql to return me the whole list of items (under the same categories_id) in such a way, that one pass of a loop would be able to organize them in a nice multidimensional array.

How would I do that?
One way I thought of doing it, is if I could get MySQL to order the items in such a way that every item is proceeded by all it's children.

For example:
If I have this structure:

Item 1
Item 2
Item 3
Item 4
Item 5
Item 6
Item 7
Item 8

Where items 1,7 and 8 are top level empty items, Item 2 has three children, and subitem 4 has Item 5 as its child

I would need mysql to order the above items in this way:
Item 1, then item 2, then since item 2 has children, it should output item 3 and 4, since 4 has a child, it should be proceeded by item 5, then it's back to item 6... etc....

Is there any way to get MySQL to order it like this? Or, could I achieve it in some other way?

Sorry, if my question is a little confusing.


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].