Wow, what a great post, thank you for taking the time to write that.
I'm going to take some time to absorb it, and I'll get back to you.
-Brian
"Unleaded" <unleaded@unleadedonline.net.n0spam> wrote in message
news:3F94B937.2000804@unleadedonline.net.n0spam...[color=blue]
> Brian wrote:
>[color=green]
> > I hate this.. Just as I give up and go begging for help, I find[/color][/color]
something:[color=blue][color=green]
> >
> >[/color][/color]
http://groups.google.com/groups?hl=e...aud.ifi.uio.no[color=blue][color=green]
> >
> > "Brian" <cpnmscg02 (@) sneakemail.com> wrote in message
> > news:cZ1lb.598452$Oz4.601263@rwcrnsc54...
> >[color=darkred]
> >>Greetings,
> >>
> >>I'm working on a project that involved that has need of a categorization
> >>system. Logically speaking, the system will have elements, these[/color][/color][/color]
elements[color=blue][color=green][color=darkred]
> >>will belong to at least a single category, sometimes more, additionally,
> >>categories may also be members of any number of categories.
> >>
> >>Well -- Thats the vision at least. I'm looking for some pointers,[/color]
> >
> > tutorials,
> >[color=darkred]
> >>white paper, napkins, whatever explaining how this can be done.
> >>
> >>I'm working on currently having a database entry:
> >>
> >>category
> >>cat_id | cat_name
> >>
> >>category_map
> >>map_id | cat_id | child_of
> >>
> >>Category map would have a many to 1 relationship with category, I think.[/color][/color][/color]
1[color=blue][color=green][color=darkred]
> >>entry in category will point to multiple entries in category_map.
> >>
> >>To build the category list, I would have to walk all the category[/color]
> >
> > elements,
> >[color=darkred]
> >>search for any children, then search each of those children for child
> >>matches. Could get ugly with all the lookups, caching the result would[/color]
> >
> > help,
> >[color=darkred]
> >>but it would be nice if there was some way of doing this that I'm just[/color][/color][/color]
not[color=blue][color=green][color=darkred]
> >>seeing. ;)
> >>
> >>If anyone has worked on something like this before, I would really[/color][/color][/color]
really[color=blue][color=green][color=darkred]
> >>appreciate some pointers on how to structure this.
> >>
> >>Thanks much,
> >>
> >>--Brian
> >>
> >>[/color]
> >
> >
> >[/color]
>
> Hi Brian,
>
> I'll give my AU 2c anyway.
>
> Some people like to say that the simple 'parent_id, child_id' type
> relationship isn't an intelligent enough solution, and is quite
> inffefficent.
>
> While this may be true for some cases, the fact is all the methods of
> tree storage, you either shift the processing time to tree
> generation-time, or shift it too creation time, and in the end if you
> are using PHP to generate a listing, you still need to do some
> processing on it!
>
> In most of the programming that I've needed to do, creation/modification
> times [of a tree] is essential, so doing complex rebuilds for the sake
> of generation-time efficiency, isn't an option. A content management
> system I've created has over 100,000 documents, each document
> categorised by a large number of categories, which uses the simple
> 'parent, child' table relationship!
>
> That said, for some of the 'solutions' stated in many of the alternative
> tree storage documents, they are difficult to implement in simpler
> DBMS's (such as MySQL) and generally just a waste of human time, even
> though technically cool :).
>
> The biggest issue with simple tree traversal is the DBMS quieries, as
> you stated earlier, as you step down the tree you would need to do a
> query to find children etc... Resulting in a minimum of one query per
> category! Not very efficent.
>
> The solution? Do it in PHP with ONE function (as I said before, if your
> going to generate the tree in HTML or whatever, you will still need to
> do some PHP anyway, so why not just trade the load from the DBMS, and
> leave it up to the PHP thread), do the tree in ONE query.
>
> Benifits:
> - You can store a tree simply. Good old 'parent, child' thing.
> - You only do one query.
> - You leave the dirty work for PHP, and don't thrash the DBMS.
> - You can modify the tree easily, relates to the first point.
>
>
> For my example, let's say we have two tables.
> - 'hierarchy' (category_id, child_category_id) and
> - 'category' (category_id, title)
>
> The 'hierarchy' has a crap load of rows, and there is equally as many
> categories.
>
> -So to create a tree, first do a simply query, say.
>
> $sql = "SELECT c.category_id, h.category_id AS parent_id, c.title
> FROM heirarchy h, category c
> WHERE h.child_category_id = c.category_id";
>
>
> - This makes a rather large list, but who really cares.
> - Now Stick it in an array.
>
> $listing = Array ();
> while($row = getrow($result))
> {
>
> $listing[] = array(
> 'parent_id' => $row['parent_id'],
> 'category_id' => $row['category_id'],
> 'category_title' => $row['title']
> );
> }
>
> - Done the first part. Your DBMS loves you, all in one query.
> - Time to generate the 'tree', you need a recursive PHP function for this.
>
>
> // Create a function
> function display_tree($parent, $level, $array)
> {
>
> foreach ($array AS $node)
> {
> // Indent
> for ($i = 0; $i < $level; $i++) $html .= ' '
>
> if ($parent == $node['parent_id'])
> {
>
> $html .= $row['category_title'].'<br />';
>
> // Get the children of this node
> $html .= display_tree($node['category_id'], ($level + 1), $array);
>
>
> } // end parent
>
> } // end foreach
>
> return $html;
>
> } // end display list
>
>
> // Now use it
> echo display_tree(0, 0, $listing);
>
>
> Done!
>
>
> That's my 2c. Hope it helps, just another solution to the problem. IMHO
> it does the job really well, at least for me. I use this approach on a
> tree with around ~3000 nodes and it only takes a few milliseconds to[/color]
create.[color=blue]
>
>[/color]
