For trivial tree-processing problems, a recursive solution is generally easiest. This might not be the best way if your tree is truly gigantic, but it should work in most cases. This is untested but should get you on the right track:

my $dbh = ... # set up database

sub display_groups { 
    my ( $parent, $level ) = @_;
    
    # get all children of this parent
    my $sth = $dbh->prepare("SELECT id, gname
                             FROM groups WHERE subgroup=?
                             ORDER BY gname");
    $sth->execute( $parent );

    # display each child and its descendants 
    while( my $row = $sth->fetchrow_hashref ) { 
        print " " x $level;  # indent
        print $row->{gname}, "\n";
        display_groups( $row->{id}, $level + 1 );
    }
}

# start with children of group 0
display_groups( 0, 0 );
[download]

Update: Added ORDER BY clause.

Hierarchal data, and relational DBs do not mix well. There are hackish solutions involving adjacency lists and several variations, but they are all inefficient and clumsy at best, and downright friggin' awkward at worst.

See MySQL:Managing hierachal data for the skinny.

It gets easier with Stored Procedures, but it can still get slow and messy. Sometimes, it is worth at least asking the question: is there a better way to persist my data than a RDBMS, and hierarchal data is one such time.

The primary questions are:

• Do you need concurrancy?
• Do you need performance?
• What volumes are you dealing with?

A realistic take on the answers to those questions can go a long way to suggesting other possible alternatives.

I'm not sure I've understand your problem: it seems you want an algorithm for printing your groups tree.

The most obvious way to do so is to write a recursive sub which print all the groups with a given value of subgroups:

You define a sub, let's call it "scan" which takes two arguments "$subGroup" and "level". The sub does a SQL select of all groups with subgroup=$subgroup ordered as you like; for each row of the dataset, it prints gname preceeded by $level ">" and then calls itself passing as arguments the value of id for the row and $level+1.

There is a field called disporder so the main teams (subgroup 0) will always print in the way I want them.

What I need is a way to print all subteams of subteams.. whatever exists for each team. And all the subteams can be a-z order.