## 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 break 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 currently
	# set the subject
#	@{$forums}[$loop_counter]->{subject} = $self->dbh->selectrow_array(
#		    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 their information    
	
	categories => $categories 
	
    );
    return $frm->build_page( { title => "MX::FORUMS" , output => $tmpl->output . Dumper($categories) });# . @{$forums}[0]->{last_post}} );

}

  
##</code><code>##

/*
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;