I'm posting this anonymously mostly because I'm ashamed to ask for help for something that is probably terribly stupid and rudimentary. I have been tooling over this and similar routines for more than two weeks now and most of my time is spent staring blankly at the screen in completely stupification. The completion of this routine is imperetive to the next blocks of my project however and until I complete it I can do little else. I know that once I see how to do it, I'll find the solution applicable in numerous other areas as I grow but it's impeding me so at this point that I'm almost too frustrated to see the issue clearly anymore.

This routine pulls data from a table in a PostgreSQL database like the following:

 cat_id |     name       | parent 
--------+----------------+--------
      1 | Books          |        
      4 | Comics         |      1 
      5 | Graphic Novels |      4 
      6 | Music          |        
      7 | Sheet Music    |      6 
      8 | Instruments    |      6 
      9 | Guitar Tabs    |      7

And produces a nested list of results of these categories as in the below example: 

Books
  Comics
    Graphic Novels
Music
  Instruments
  Sheet Music
    Guitar Tabs

The problem is that I implement HTML::Template with most of my projects and in this case, I can't figure out how to properly 'catch' the data instead of outputting it to the browser directly from the code. That is, I need a $cat_output or a @cat_output that contains the entire sorted and nested results as displayed above and as would otherwise have been output to the browser. Once I have a scalar or an array, I can feed it to HTML::Template. But because of the recursiveness of this routine, I can't figure out how to do this without ending up with completely mangled, partial or repetitive results.

Here is the code that simply print()'s out the categories to the browser as they are processed. I need help taking it from this into a routine that can store all of the data in a variable that I can then use. 

sub ListCategories {

    my (%cat,
        $sql_query,
    $already_run,
    $parent_cat_id,
    $indent,
    $all_cats
    );

    $indent = $_[0];
    $parent_cat_id = $_[1];
    $already_run = $_[2];

    print $query->header if ($already_run ne 'y');

    if ($parent_cat_id >= 0) {

    if ($parent_cat_id == 0) {
            $all_cats = $dbh->selectall_arrayref("SELECT category_id,n
+ame FROM $sql{categories} WHERE parent IS NULL");
        }

        elsif ($parent_cat_id > 0) {
            $all_cats = $dbh->selectall_arrayref("SELECT category_id,n
+ame FROM $sql{categories} WHERE parent=$parent_cat_id");
            $indent .= "     ";        
        }

        foreach my $current_cat (sort { $a->[1] cmp $b->[1] } @{$all_c
+ats}) {
            ($cat{id}, $cat{name}) = @$current_cat;
            print "$indent $cat{name} ($cat{id})<br>";
            &ListCategories($indent, $cat{id}, 'y');
        }
    }
}

[download]

In reply to Collecting data in a recursive routine. by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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! —
  • 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:
    & &amp;
    < &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.