Re: Category and sub-category logic

Wouldn¹t it just be easier to do a relationship table as in Personnel emp ID parent ======================== 'Albert' 1 0 'Bert' 2 1 'Chuck' 3 1 'Donna' 4 0 'Eddie' 5 4 'Fred' 6 0 'Joe' 7 6 This way you know the ID of the person and the parentID if they have one, otherwise they are a 0 which means they are the parent.. Seems like this would be a much easier way of doing it doesn¹t it? And only one SQL query... Rick "The only way to have a friend is to be one." - Ralph Waldo Emerson > From: Mark <mark_weinstock@yahoo.com> > Date: Fri, 15 Aug 2003 07:18:41 -0700 (PDT) > To: "Chris W. Parker" <cparker@swatgear.com> > Cc: php-general@lists.php.net > Subject: RE: [PHP] Category and sub-category logic > > I'm trying to get a handle on this algorithm as well. If you add > additional children to a parent record, doesn't that require > renumbering all the rgt and lft values in the lineage? For example > (table shamelessly stolen from a website): > > Personnel > emp lft rgt > ====================== > 'Albert' 1 12 > 'Bert' 2 3 > 'Chuck' 4 11 > 'Donna' 5 6 > 'Eddie' 7 8 > 'Fred' 9 10 > > If I want to add 'Joe' as a subordinate to 'Fred', doesn't that mean > I have to change the lft values for Fred, Chuck, and Albert? Yes, > deleting definitely seems easier. I'm curious about adding... > > Mark > > > > --- "Chris W. Parker" <cparker@swatgear.com> wrote: >> David Otton <mailto:phpmail@jawbone.freeserve.co.uk> >> on Thursday, August 14, 2003 4:58 PM said: >> >>> The advantage of doing it this way is that your tree structure is >>> generic and can have many levels. The disadvantage is that you >> may >>> need many SQL queries to fully traverse the tree (though people >>> rarely want to do this, and sub-selects, clever joins or >> post-query >>> processing can reduce the overhead). >> >> Which is why you should use the Modified Preorder Tree Traversal >> method >> instead. With that method you've got one table that defines the >> categories AND their relationships to each other. >> >> Your SQL queries end up looking something like this. >> >> SELECT name >> FROM categories >> WHERE lft >= x >> AND rgt <= y >> >> >> >> Chris. >> >> -- >> PHP General Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> > > > ===== > Mark Weinstock > mark_weinstock@yahoo.com > *************************************** > You can't demand something as a "right" unless you are willing to fight to > death to defend everyone else's right to the same thing. > *************************************** > > __________________________________ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php >