## display the forums in list/table form
sub main { 
    
    my $self = shift;
    my $q    = $self->query;
    my $tmpl = $self->load_tmpl(
        "main.html",
        associate => $q
    );

    # retrieve all forum categories
    my $categories = $self->dbh->selectall_arrayref(
        q{
    
        SELECT DISTINCT(category) FROM mx_forums
        
        },
        {
        Slice => {}
        }
    
    );
    

    my $sth = $self->dbh->prepare(q[
           SELECT 
            IFNULL(COUNT(mx_threads.forum_id),0) AS thread_count, 
            IFNULL(MAX(mx_threads.id), 0)  AS last_post, 
            IFNULL(mx_threads.is_new, 0) as is_new, mx_forums.id, 
            mx_forums.forum_name, mx_forums.forum_description
           FROM 
            mx_forums
           LEFT JOIN 
            mx_threads
           ON
            mx_threads.forum_id = mx_forums.id
           
           WHERE
            mx_forums.category = ?

           GROUP BY 
            mx_forums.id
           ORDER BY 
            mx_forums.create_date DESC
    ]);
    
    for ( @{ $categories } ) {

    $sth->execute( $_ ) or die $self->dbh->errstr;

    }

    my $forums = $sth->fetchall_arrayref({});

    ## Since we can't retrieve everything in ONE query, we have to bre
+ak it up a bit.    
    # format is: $forums[$loop_count]->{blah}

    #retrieve ids of all existing forums
    my $forum_ids = $self->dbh->selectall_arrayref(
    q{
        SELECT id FROM mx_forums
    }
    );


    
    
    # loop through forum ids and retrieve each forums thread count
    
    # NOT for IDs...for the array index
    my $loop_counter = 0;
    for my $i ( @{ $forum_ids } ) {

    # count the messages

    my $threads = $self->dbh->selectrow_array(
            q{
            SELECT COUNT(*) FROM mx_threads 
            WHERE forum_id=?
            },
            {
            Slice => {}
            },
            $i
    ) ;

    my $replies = $self->dbh->selectrow_array(
            q{
            SELECT COUNT(*) FROM mx_replies 
            WHERE forum_id=?
            },
            {
            Slice => {}
            },
            $i
    );
        # COMMENTED OUT - fetchrow_arrayref not returning anything cur
+rently
    # set the subject
#    @{$forums}[$loop_counter]->{subject} = $self->dbh->selectrow_arra
+y(
#            q{
#            SELECT subject FROM mx_threads
#            WHERE id=? AND forum_id=?
#            },
#            {
#            Slice => {}
#            },
#            @{$forums}[$loop_counter]->{last_post}, $i    
#    );

#    @{$forums}[$loop_counter]->{post_count} = $threads + $replies;
        
        
#    $loop_counter++;

    }
    
    
    
    my $loop_counter = 0;
    for ( @{ $categories } ) {
    @{ $categories }[$loop_counter]->{forums} = $forums;
    $loop_counter++;
    }
    
    $tmpl->param( 
    ## get the thread count on the forums, as well as the forums and t
+heir information    
    
    categories => $categories 
    
    );
    return $frm->build_page( { title => "MX::FORUMS" , output => $tmpl
+->output . Dumper($categories) });# . @{$forums}[0]->{last_post}} );

}
[download]

Table Structure for forums:

/*
SQLyog - Free MySQL GUI v4.1
Host - 4.1.18 : Database - mx
*********************************************************************
Server version : 4.1.18
*/


/*Table structure for table `mx_forums` */

drop table if exists `mx_forums`;

CREATE TABLE `mx_forums` (
  `id` int(11) NOT NULL auto_increment,
  `forum_description` text NOT NULL,
  `forum_name` varchar(50) NOT NULL default '',
  `category` varchar(50) NOT NULL default '',
  `create_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `is_locked` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
[download]