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@xxxxxxxxx]
Sent: Wednesday, June 21, 2006 10:51 AM
To: Philip Thompson; php-general@xxxxxxxxxxxxx
Subject: Re: 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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
