posted 20 years ago
Hi All,
I am facing a peculiar problem (due to bad database design, I assume) and need some help in logic building.
We are making a web application where the menu's to be dislayed on screen are generated dynamically based on the menu details stored in a table "MENU". The fields in this table are menu id (primary key, we are using timestamp), menu level and parent id.
Menu ID is the primary key and is generated whenever a new menu is created. We are using DB2 UDB's TIMESTAMP for this.
Menu Level describes what level of menu is it. For top level menu its value is '0' (zero). For 1st level submenu, its value will be '1' and soon.
(Example: We have a menu called Master Tables, which is a list of all master tables, hence its menu level is '0'. This master table has 3 submenu's "User", "Admin" & "Client", so these sub menus have menu level '1'. If we add a sub menu for "User" master, it will become a level '2'.
The parent id field has the menu id of its corresponding parent. For menu level '0' this field is null as it is the top level menu. Foe menu level '1' this field will have the menu id of its parent menu ie one of the '0' level menu's. Similarly, a menu level'2' will have the corresponding menu level '1' id in its parent id field.
I need to display this menu in the proper parent-child hierarchy.
ie, 0 level - its corresponing 1 level - its corresponing 2 level... and so on.. this should repeat for all 0 levels having their respective tree of 1 level's and 2 levels inside (can go upto any level)...
I need a solution for this problem either programatically or at the database level. I strongly believe programatically this requires some kind of recursive loop where it should iterate through the entire list and find all childrens of a top level menu, again and again to generate the sorted list of menus in the order in which it has to be displayed...
Awaiting response,
Thanks in Advance...
Yogesh