This is a common pattern. The basic problem is "how to I generate a child pointer tree from a parent pointer representation (as would normally be stored in a DB)". The following is a (proven but not debugged) solution that doesnt require multiple queries, can handle the data in any order, and only requires a single forward pass over the data.

sub getCategories {
    my ($dbh)=@_;
    
    my $sql = qq~SELECT category_id, parent_id, category_name FROM ss_
+catalog_categories~;
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    my %nodes;
    my @roots;
    my ($cid,$pid,$name);
    while ( ($cid,$pid,$name) = $sth->fetchrow_array()) {
       my $node=($nodes{$cid}||={});
       $node->{name}=$name;
       $node->{pid}=$pid;
       $node->{cid}=$cid;
       if ($pid) {
         $nodes{$pid}{_kids}{$cid}=$node;
       } else {
         push @roots,$node;
       }
    }
    $sth->finish;    
    
    return (\@roots,\%nodes);    
}
[download]

Roots contains the root nodes of your forest, and %nodes contains a hash keyed by the category id of the nodes. You can traverse each tree in the forest like this:

sub traverse {
  my ($node,$depth)=@_;
  $depth||=0;
  print " " x $depth,$node->{cid}," ",$node->{name},"\n";
  foreach my $id ( sort {$a <=> $b} keys %{$node->{_kids}||{}} ) {
    traverse($node->{_kids}{$id},$depth+1);
  }
}
my ($r,$n)=getCategories($dbh);
traverse($_) for sort { $a->{cid} <=> $b->{cid} } @$r;
[download]

For those that care the above code is a highly simplified form of what is used here for the Recently Active Threads.

Thanks, demerphq. That looks like exactly what I was looking for. I'll give it a try!

# this assumes that you have the top level categories with a
# parent_id that is NULL
my $sql = qq~SELECT category_id, parent_id, category_name 
    FROM ss_catalog_categories~ WHERE parent_id IS NULL;
my $sth = $dbh->prepare($sql);
$sth->execute;
my $results = $sth->fetchall_arrayref({});
my $categories = format_results( $results );

sub format_results {
  my $results = shift;
  foreach my $result (@$results) {
    my $children = get_childrent($result->{category_id});
    my %category = (
      name     => $result->{category_name},
      children => $children,
    );
    push(@$categories, \%category); 
  }
}    

sub get_children {
  my $parent = shift;
  my $sql = qq~SELECT category_id, parent_id, category_name 
    FROM ss_catalog_categories~ WHERE parent_id IS ?;
  my $sth = $dbh->prepare($sql);
  $sth->execute($parent);
  return format_results($sth->fetchall_arrayref({}));
}
[download]

Update: and, further below in the thread for a complete solution.

In your getCategories function, you can do some optimizations. fetchrow_hashref rather than fetchfow_array this will give you a hash by column so you don't have to. Use push which will get rid of the $row variable completely.

while (my $rh = $sth->fetchrow_hashref()) {
        push(@{$results_data}, $rh);
    }
[download]

Unless there is a good reason to loop, you could replace loop in getCategories and just call selectall_hashref

Thanks for the suggestions.

Yes, holli, the parent category should always be listed before the corresponding sub-categories.

I know this may not be possible, but my goal is to do only one query on the database to grab all of the categories and then be able to sort them properly from there. Otherwise, if I had 100 or so categories I would need to do a ton of database queries and I would love to avoid that if possible.