%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]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.