|
|
Posted by hornedw on 09/03/06 03:43
onembk wrote:
> On 2006-09-01 22:48:44 -0600, "hornedw" <hornedw@yahoo.com> said:
>
> >
> > mootmail-googlegroups@yahoo.com wrote:
> >> Jerry Stuckle wrote:
> >>>
> >>> If you can do recursive queries, it works quite well. And this was with
> >>> DB2, which handles recursive queries quite well.
> >>>
> >>
> >> Hmm. The DB we were using didn't support recursive queries, so I never
> >> got to explore that path, unfortunately. I suspect that, as you say,
> >> it would handle well enough performance-wise, seeing that all the
> >> recursing is done on the DB itself rather than via recursive queries
> >> coming from the code. Still, though, even the most efficient recursion
> >> carries along with it some degree of overhead. That's just the nature
> >> of recursion.
> >>
> >> If I had the time spare time, I would love to implement a number of
> >> tree algorithms and see how they scale in performance as the size of
> >> the tree increases. That is the kind of assignment I WISH they had
> >> given back in college. Practical and educational.
> >
> > Thanks everyone for your help. I probably will either use Javascript or
> > CSS in displaying the child categories or set it up similar to
> > Amazon.com's categories(main categories display on one page, and the
> > category selected and its subcategories on the second page. Thanks
> > mootmail--I will look back at the link I provided earlier as to display
> > the number of items.
> >
> > David
>
> What if your result set is in the thousands? Tens of thousands?
> JavaScript probably wouldn't do so well with that.
>
> Here's a way to do it, ugly proof of concept but it works while letting
> you control how much data is loaded into memory:
>
> Tables:
> ================================
>
> Table 1: categories
> cat_id cat_parent cat_name
> 1 0 books
> 2 1 fiction
> 3 2 classic
> 4 2 sci-fi
> 5 1 non-fiction
>
> Table 2: Items
> item_id item_cat item_name item_description
> 1 3 'hamlet' 'shakespeare'
> 2 3 'romeo & juliet' 'shakespeare'
> 3 4 'episode 1' 'star wars'
> 4 4 'episode 2' 'star wars'
>
> PHP:
> ================================
>
> <pre>
> <?php
>
> //
> // usage: array load_tree(int cat_id, int child depth);
> //
> function load_tree($cat_id, $depth, $cur_level = 0) {
> if ($cur_level > $depth) {return;}
>
> $ret = array();
> $cats = mysql_fetch_assoc(mysql_query("SELECT * FROM categories WHERE
> cat_id='$cat_id'"));
> $ret[$cats['cat_name']] = array();
>
> $sub_cats = mysql_query("SELECT * FROM categories WHERE
> cat_parent='".$cats['cat_id']."'");
>
> $cur_level++;
> while ($cats2 = mysql_fetch_assoc($sub_cats)) {
> $ret[$cats['cat_name']][] = load_tree($cats2['cat_id'], $depth, $cur_level);
> }
> $cur_level--;
>
> $items = mysql_query("SELECT * FROM items WHERE item_cat='$cat_id'");
> while ($item = mysql_fetch_assoc($items)) {
> $ret[$cats['cat_name']][] = $item;
> }
> return $ret;
> }
>
> function print_tree($tree, $indent = ' ') {
> if (!is_array($tree)) {return;}
> if (isset($tree['item_name'])) {
> echo $indent.$tree['item_name'].' '.$tree['item_description'].'<br />';
> } else {
> foreach ($tree as $key => $val) {
> echo $indent.$key.'<br />';
> if (count($val) > 0) {
> foreach ($val as $val2) {
> print_tree($val2, $indent.$indent);
> }
> }
> }
> }
> }
>
> $tree = load_tree(1, 3);
>
> //print_r($tree);
> echo "\n\n";
> print_tree($tree);
> ?>
>
> Output:
> ================================
>
> books
> fiction
> classic
> hamlet (shakespeare)
> romeo & juliet (shakespeare)
> sci-fi
> episode 1 (star wars)
> episode 2 (star wars)
> non-fiction
> ================================
>
> You could do the same thing in reverse, recursively querying for the
> parent instead of the children, but you would only end up with a single
> main category (categories other than 'books' would be ignored).
>
> Hope this gives you another idea. I've used this method many times to
> create download pages and other things, works quite well (even though
> the '$tree' array is ugly).
Thanks onembk, I will give that a try.
Navigation:
[Reply to this message]
|