mysql creating a menu with submenus ordered

Question

I'm trying to create a system of menus for a website where you'll be able to create menus with the option of submenus. In my mysql 'menu_item' table, there's a 'hierarchy' column which will hold either a 0 if it's a topl-evel menu, or the id of the menu item under which it's located within the menu hierarchy. Now, what I'm trying to do is to come up with an SQL query that will return me an array that will already have the order of it taken care of in such a way that after each row, it will lay out all the rows that are under it heirarchichly. For example, if I have this menu:

menu1 - menu1.1, menu1.2 menu2 - menu 2.1, menu 2.2 menu 3 - menu 3.1

the result should be like this:

menu 1 menu 1.1 menu 1.2 menu 2 menu 2.1

and so on.

I can't put my mind into how to go about it. Is this even possible to do in MySQL, or am I just going to have to return the entire list of menu items and order them in PHP?

Thanks.

Is it really so hard to try to search even a little before writing a question? For example: stackoverflow.com/questions/11404468/… — MSadura
– MSadura, Commented Mar 16, 2014 at 18:21
Done my research. I know I can do it in PHP. I was specifically asking if I could get an ordered mysql result. — William Northern
– William Northern, Commented Mar 16, 2014 at 18:27

zugzwang · Accepted Answer · 2014-03-17 04:37:46Z

0

I assume you have a column in the menu_item table that holds menu item names such as "menu 1", "menu 2" and so on. I'll call that column menu_item_name since it's not specified in your question.

I think the way to solve this problem is to append the returned column with nothing if hierarchy is zero, otherwise append a '.' and the hierarchy number. Further, sort by whatever you are already sorting by (perhaps menu_item_name) and THEN by hierarchy.

SELECT 
    menu_item_name + CASE WHEN hierarchy <> 0 THEN '.' + hierarchy ELSE '' END
FROM
    menu_item
ORDER BY
    menu_item_name, hierarchy

Try this out and let me know if it works.

Cheers,

Z

edited Mar 17, 2014 at 4:37

answered Mar 17, 2014 at 4:27

zugzwang

1045 bronze badges

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

Collectives™ on Stack Overflow

mysql creating a menu with submenus ordered

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related