Re: Better way of doing this? (menu and submenus)

If you are using PostgreSQL, there is a 'connectby' function (part of contrib) which will recursively join hierarchical data where you have PID --> ID recursive joins. Oracle also has CONNECT BY built into the SQL language. I do not know if MySQL natively supports anything similar but maybe someone on this list knows the answer to that. Having a CONNECT BY clause avoids having to do round trip selects for each of your sub categories and greatly simplifies queries, IMHO. Dante Mark Steudel wrote:

You could use a recursive function and keep it all in one table. My Table is similar to the one below: ID NAME PID DISPLAY_ORDER Here's crude recursive function to display this in a list typical of what you used to style a menu. The great thing about this is that you could have unlimited submenus if you wanted ... function displayMenu( $pid ) { global $db; $res =& $db->query( "SELECT * FROM tblmenu WHERE pid = '".$pid."' ORDER BY display_order" ); mysqlErrorCheck( $res ); while( $res->fetchInto( $objData ) ) { $html .= "<li>".$objData['name']; $html .= "<ul>".displayMenu( $objData['id'] )."</ul>"; $html .= "</li>"; } return $html; } echo "<ul>".displayMenu('0')."</ul>"; -----Original Message----- From: Ryan A [mailto:genphp@yahoo.com] Sent: Wednesday, June 21, 2006 10:51 AM To: Philip Thompson; php-general@lists.php.net Subject: Re: [PHP] Better way of doing this? (menu and submenus)

Ryan, I have run into a similar problem with one of the sites I'm designing. Here's the approach I took. You can see if it fits your needs. Here's an example of what I have:

MID  NAME        PARENT_ID  LOCATION
1    Home        <null>     index.php
2    Products    <null>     products.php
3    Information <null>     info.php
10   Profile     1          profile.php
11   Account     1          account.php
20   Hardware    2          hardware.php
21   Software    2          software.php
210  M$ Word     21         msword.php
211  iLife       21         ilife.php

And I think you get the drift. You can keep getting deeper in the tree structure and not have to worry about adding more tables. Notice that the menu_item_id (MID) actually has some organization to it, but this is completely optional. As the menu items grow, this will probably be more difficult to keep track of. Anyway, hope that helps!

@Phillip, Thanks for replying. Because (like you pointed out) the MID can get a little crazy I was thinking of using two tables, one for parent and the other for the children. Also, I forgot to mention in my first post, I am using a p_position (parent position) and c_position (child) for each of the menu items, that way I can do a ORDER BY in my query and it should still look good and changing positions shouldnt be a problem (ask anyone who's married, being flexable in changing positions is VERY important :-D ) Doing an ORDER BY on two tables in also a bit more simple than one (IMHO, feel free to correct me) I really appreciate your input on this as I am in the theory stage while you have actually made something like this, thanks again. @K. Bear - Thanks for the link, I'll check it out as soon as i get a little time. Cheers! Ryan ------ - The faulty interface lies between the chair and the keyboard. - Creativity is great, but plagiarism is faster! - Smile, everyone loves a moron. :-) __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com