I solved this problem when I wrote Cascade, which uses a Postgres backend to manage an index of resources. The issue is the same-- you have a tree of categories that you want to organize. You can download the source code here. I recommend version 1.3.7 at the moment for your purposes. (although it's not to be considered "stable").

In short, I used a different data model which made things easier for me. It's basically a adaptation of what Phillip Greenspun describes here. It allows you to select all your messages in threaded ordered with one select statement, which I like.

To save you a bit of looking, here's my routine that creates a list much like what you are doing above. This of course depends on having your data model set up as mine is. 

# used for selecting a new category to attach this item to. 
sub _new_category_id_box {
    my $item = shift;
    my $name = shift;

    my $tbl = $Cascade::DBH->selectall_arrayref("
        SELECT id, name, (length(sort_key)-2) as level 
            FROM category 
            ORDER BY sort_key");

    my %parents;    
    my $q = new CGI;
    my @p_ids;
    foreach my $row (@$tbl) {
        my ($id,$name,$level) = @$row;
        $parents{$id} = ' 'x$level.CGI::escapeHTML($name);
        push @p_ids, $id; # we keep the ids in a seperate array to pre
+serve sorting
    }        

    $q->autoEscape(undef); # we need to turn this off for the spaces i
+n the list to show up correctly. 
    
    return $q->scrolling_list(
        -name=>$name,
        -values=>[@p_ids],
        -labels=>\%parents,
        -default=>$item->category_id,
        -multiple=>0,
        -size=>1,
    );
}

[download]
-mark

In reply to Re: Graphical Hierarchical Tree by markjugg
in thread Graphical Hierarchical Tree by yojimbo

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.