%title_hash = ();
%index_hash = ();

$dbh = <your db connection handle>;
$sql = qq[select category_id, category_name, main_category from test];
$sth = $dbh->prepare($sql);
$sth->execute;
while ( ( $child, $name, $parent ) = $sth->fetchrow ) {
  # pad the category id and main category numbers with leading zeros
  # (so we can use the numbers as keys of a hash and sort on the keys)
  $child = &pad($child);
  $parent = &pad($parent);
  # save the category name for this category id
  $title_hash{$child} = $name;
  # add the category id to an array in the hash
  # for the main category
  push(@{$index_hash{$parent}},$child); # hash of arrays
}
$sth->finish;
$dbh->disconnect;

# so now we should have the parent/child
# relationships of all the categories
# in our %index_hash hash...
#
# @{$index_hash{<main_category number>}} is
# an array of the child categories
# associated with each main_category

# set the initial recursion level
$level = 0;
# what is the top-most main category?
$topkey = (sort keys %index_hash)[0];
# start the recursive loop
&show_children($topkey);


##############

sub show_children {

  # go down one level
  $level++;
  # set some 'my' variables
  my $parent_key = $_[0];
  my $child_key = 0;
  my $dash = '';
  # what are the child categories of this parent?
  my @children = sort @{$index_hash{$parent_key}};
  # are there any children of this parent?
  if ( 0 < scalar(@children) ) {
    # yes there are, so let's display each of them
    foreach $child_key (@children) {
      # if level 1, dash is '--' ...
      # if level 2, dash is '----' ... etc
      $dash = '-' x $level;
      $dash .= $dash;
      print qq[$dash $title_hash{$child_key}\n];
      # treat this child as a parent and see if
      # it has any children to display
      &show_children($child_key);
    }
  }
  # come back up one level
  $level--;

}

##############

sub pad {

  # turns '4' into '0004'
  # ...good for when you want to use numbers
  # as hash keys so you can sort the keys
  # in numerical order
  # ... otherwise perl sorts 1 through 10 as
  # 1, 10, 2, 3, 4, 5, 6, 7, 8, 9
  # (this is because hash keys are always seen as
  # strings by the perl interpreter)
  my $v = $_[0];
  return substr( ('0000'.$v), -4, 4 );

}
[download]