my $cats_sth = $dbh->prepare(<<"EndOfSQL");
  SELECT category_id, name, parent
  FROM   $sql{categories}
  ORDER BY category_id ASC
  -- assumes parent id is always less than child id
EndOfSQL

my @cats;

# build the data structure
while (my($cat_id, $name, $parent_id) = $cats_sth->fetchrow_array()) {
    $cats[$cat_id] = [$name, $parent_id, []];
    if ($cats[$parent_id]) {
        push @{$cats[$parent_id][2]}, $cat_id;
    } elsif ($parent_id) {
        warn "Parent $parent_id not found for category $cat_id '$name'\n";
    }
}

# print the categories
foreach my $cat (@cats) {
    next unless $cat and !$cat->[1];
    print_category($cat, \@cats, '');
}

# recursively print a category and its children
sub print_category {
    my($cat, $cats, $indent) = @_;
    
    print $indent, $cat->[0], "\n";
    foreach my $child_id (@{$cat->[2]}) {
        print_category($cats->[$child_id], $cats, "$indent  ");
    }
}