SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Nested (Hierarchical) Menus w/Oracle and PHP

    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:
    Code:
    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:
    Code:
    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):
    Code:
    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 Advanced PHP please feel free to move it there. Thanks!!
    Last edited by Hierophant; Mar 29, 2003 at 18:33.

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Damn it. IT keeps crashing my browser when I try and edit the post to make the CODE tags appear, so I'll attach a text version of my post.

  3. #3
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Second try.
    Attached Files Attached Files

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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:
    Code:
              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):
    Code:
        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!!

  5. #5
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    While we wait for the attachment, don't know if you've seen this thread: http://www.sitepointforums.com/showt...threadid=86795

    In particular Vincents post

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply, Harry. I found a solution here, though.

  7. #7
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Looks like a good way to do it - manipulating the results in PHP's memory. With MySQL I've seen people try this with multiple queries (which grinds to a halt alot faster) - there was an article on devshed about building a tree menu with PHP which used that approach.

    Interesting problem though - with no clauses on the query the bigger the result set as the table grows and so the more memory PHP will need to hold it all in one go (plus the recursion takes longer).

    Wonder if an optimal solution is possible, where the depth of an element is stored alongside it's parent ID at the point it is created?

  8. #8
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by HarryF
    Wonder if an optimal solution is possible, where the depth of an element is stored alongside it's parent ID at the point it is created?
    Like this?: http://www.sqlteam.com/item.asp?ItemID=8866
    (Skip to the last table on the page for the instant visual summary)
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •