Nested (Hierarchical) Menus w/Oracle and PHP

mattr

Hi,

I'm trying to build a hierarchical menu system in PHP4 and Oracle 8i. As such, I have the "CONNECT BY" syntax which will walk the tree -- I'm just having problems taking my result set and making HTML out of it.

Here's the relation:

CREATE TABLE nav(
  nav_id     NUMBER          NOT NULL DEFAULT easit_seq.nextval,
  parent_id  NUMBER          NOT NULL REFERENCES nav( nav_id ),
  link_text  VARCHAR2( 200 ) NOT NULL,
  link_url   VARCHAR2( 200 ) NOT NULL,
  PRIMARY KEY( nav_id )
)

And my query:

          SELECT nav_id,
                 link_text,
                 link_url,
                 parent_id,
                 LEVEL
            FROM nav
CONNECT BY PRIOR nav_id = parent_id
        ORDER BY level     ASC, 
                 link_text ASC

Which gives me a result set like this (I removed the link_text and link_url since they are not really needed here):

    NAV_ID  PARENT_ID      LEVEL
---------- ---------- ----------
         1                     1
         9          8          1
         5          1          1
         3                     1
         6          1          1
         8          1          1
        11          1          1
         7          1          1
         2                     1
        10          8          1
        12          9          1
         4          1          1
         9          8          2
         5          1          2
         6          1          2
         8          1          2
        11          1          2
         7          1          2
        10          8          2
        12          9          2
         4          1          2
         9          8          3
        10          8          3
        12          9          3
        12          9          4

So, I want to take that and then construct a hierarchical menu system. Any hints/help? MODS: If this belongs in PHP please feel free to move it there. Thanks!!

jweissig

hey,

i ran into this a while ago also.. i found three awesome tutorial on how to do this.. i will place then in best to least helpful..

http://www.evolt.org/article/BBS_style_recursion_How_To/17/3962/index.html

http://www.evolt.org/article/Four_ways_to_work_with_hierarchical_data/17/4047/index.html

http://www.evolt.org/article/How_to_create_a_hierarchical_navigation_menu/17/21219/index.html

the last two are really the theory behind menu driven database tree's or whatever.. and the first is the implementation.

cya
justin

mattr

Excellent -- that helped a ton!!