First, I would suggest that you separate your functions so you're not trying to mix db queries with HTML presentation. I'd probably fetch the data from the db, munge the structure as needed, and then pass it to a templating system (such as HTML::Template) for display.

Recently I read a couple of articles about how to store tree structures in relational databases. Based on my browser history, they included Managing Hierarchical Data in MySQL and Storing Hierarchical Data in a Database. The latter explains both the adjacency list model (a recursion method) and the modified preorder tree traversal method. Examples of code to retrieve the tree are given for both types (but they're in php).

Finally, Wikipedia has an entry for hierarchical data model. While the content may be nothing new to you, there are several links to other resources that might be helpful.

You could use a recursive function to drive down into your tree structure. You hand it the root node, and it then runs itself on each of the children using them as the root nodes. The function would return a hash of nodes with the keys being the node, and the value being a hashref of the children. Then you just iterate over the final list using the keys, and if there are values follow those as well. In fact i think you could build an iterative solution that just does that last bit.

The typical representation in Perl of a tree like yours would be a hash of arrays. Keys are the category names and values hold the list of corresponding sub-categories. Leaving all database issues aside, I'll assume (a corrected version of) your table is presented as a text file. The following code builds the tree hash %tree from the data. The sub print_tree then prints an indented table of the categories.

my @names;
my %tree;
while ( <DATA> ) {
    chomp;
    my ( $id, $parent, $name) = (split ' ', $_, 5)[ 2, 3, 4];
    $names[ $id] = $name;
    if ( $parent ) {
        push @{ $tree{ $names[ $parent]} }, $name;
    }
}

print_tree( 'Animals', \ %tree, 0);
exit;

use constant INDENT => '    ';

sub print_tree {
    my ( $root, $tree, $level) = @_;
    print INDENT x $level, $root, "\n";
    for ( @{ $tree->{ $root} } ) {
        print_tree( $_, $tree, $level + 1);
    }
}

__DATA__
        Edit    Delete  1       0       Animals
        Edit    Delete  2       1       Mammals
        Edit    Delete  3       1       Fish
        Edit    Delete  4       2       Dog
        Edit    Delete  5       2       Cat
        Edit    Delete  6       3       Fresh Water
        Edit    Delete  7       3       Salt Water
        Edit    Delete  8       7       Sharks
        Edit    Delete  9       7       Small Critters
[download]

Thanks for the information but I can't figure out how to query the MySQL data in such a way I could process the information with your subroutines.

Howdy!

Actually, there are dolphins that are fish as well as dolphins that are mammals.

yours,
Michael