First, I would suggest that you separate your functions so you're not trying to mix db queries with HTML presentation. I'd probably fetch the data from the db, munge the structure as needed, and then pass it to a templating system (such as HTML::Template) for display.

Recently I read a couple of articles about how to store tree structures in relational databases. Based on my browser history, they included Managing Hierarchical Data in MySQL and Storing Hierarchical Data in a Database. The latter explains both the adjacency list model (a recursion method) and the modified preorder tree traversal method. Examples of code to retrieve the tree are given for both types (but they're in php).

Finally, Wikipedia has an entry for hierarchical data model. While the content may be nothing new to you, there are several links to other resources that might be helpful.

In reply to Re: Database categories by bobf
in thread Database categories by coldfingertips

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.