[ic] TIPS AND TRICKS: Tree example BUG FIX

[Nathaniel Scott]

> 3. In the example, we will run some code to build a tree database based
> on the contents of comb_category in the products table. The example
deletes
> all rows from "tree" every time that is done, so beware. Place the code
> below in pages/tree_build.html:
>
> [perl tables="products tree"]
[SNIP]

We have a couple of sites using Interchange 4.83 (Perl 5.6) and the latest
project we took on is an art store and they have many products that need sub
trees (Paint>Acrylic>Blue) and found the TREE example that Mike gave should
be a perfect fit (which is some awesome code for a solution to multiple sub
categories).  After getting quite a few products in the SQL database
(>1,000) we discovered that we couldn't have multiple sub categories with
the same name (Paint:Latex:Blue vs. Paint:Acrylic:Blue) that existed
elsewhere in the tree.  In this example, the following "Blue" sub-sub
categories would not end up in it's unique location under Latex.  We're by
no means a pro like Mike in perl but here's our solution for tree_build.html
that we found:

[perl tables="products tree"]

    my $db = $Db{products};
    my $ary = $db->query('select sku,comb_category from products');
    my $out = '';
    my $code = '00000';
    my $cc  = '0000';
    my %done;
    my @levels;
    foreach $row ( @$ary) {
        my ($sku, $cat) = @$row;
        next if $done{$cat};
        my $parent = 'TOP';
        my @parts = split /:/, $cat;
        my $combname = '';
        for( my $i = 0; $i < @parts; $i++) {
            my $name = $parts[$i];
            my $comb = join ":", @parts[0 .. $i];
            my $level = $levels[$i] ||= {};
            if(! $level->{$comb}) {
                               #^^^ Changed from $name to $comb
                $level->{$comb} = [ ++$code, $parent, ++$cc, $name, $comb ];
                              #^^^ Changed $name to $comb
                $parent = $cc;
            }
            else {
                $parent = $level->{$comb}->[2];
            }
        }
    }
    my $tdb = $Db{tree};
    $tdb->query('delete from tree');
    my @flds = qw/parent_fld child_fld name category/;
    my $count++;
    for(@levels) {
        my $hash = $_;
        for(sort keys %$hash) {
            my $rec = $hash->{$_};
            my $code = shift @$rec;
            $tdb->set_slice($code, \@flds, $rec);
            $count++;
        }
    }
    return $count;
[/perl]


The problem appeared to be generating the hash based on the name (which may
not be unique) and not the complete comb_category  (Paint:Latex:Blue).  The
original post by Mike Heins is available at
http://interchange.redhat.com/archive/interchange-users/2001/msg09061.html.
Thank you again Mike for a very nice solution and your detailed explanation
on TREEs.

Thanks,

Nathaniel Scott

Mobile Tech Support, Inc
The Cyber Image:  www.thecyberimage.com
Ph: 623-937-8555   Fax:  623-937-1555