Posted by onembk on 09/02/06 07:54

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).

[Back to original message]