# # # # # #  
#'  .     .  `#
#      i      #     making an ass out of u and me since 1975
# assumptions #
#+_.       ._+#


my %sql;    # is filled w/ names of tables that hold category data
my $dbh;    # database handle


=head2 The Structure of a Node

    {
      id       => $number,
      name     => $string,
      children => $array_ref_of_more_nodes,
    }

=head2 Usage

To get the whole tree:

    my $tree = GetCategories(); 

To get a partial tree, pass in a hashref that
has an C<id> key representing the parent C<category_id>
to start with:

    my $subtree = GetCategories($starting_node);
   
=cut


sub GetCategories {
    my $node            = shift || undef;
    my $select_children =
        "SELECT category_id,name FROM $sql{categories} ";

    # Create the root node only on the 1st time through,
    # and complete the SQL statement for 
    # selecting all the nodes children.
    if (not defined $node) {
        $node = {
            id       =>   0,
            name     => '/',
            children => [ ],
        };
        $select_children .= "WHERE parent IS NULL";
    } else {
        $select_children .= "WHERE parent=$node->{id}";
    }

    # Find all the children of the current node.
    my $all_cats = $dbh->selectall_arrayref($select_children);
    my $c;

    # Add each child to $node->{children}, and
    # then recursively add get each child's children
    foreach $c (sort { $a->[1] cmp $b->[1] } @{$all_cats}) {
        my $child_node = {
            id       => $c->[0],
            name     => $c->[1],
            children => [ ],
        };

        # ............. depth-first style .....

        push @{$node->{children}}, $child_node;
        GetCategories($child_node);
    }
        
    return $node;
}
[download]