Let's see if we can figure out what you want first. My guess is this:

Category 1
    Forum 1    threads: 10   replies: 20
    Forum 2    threads: 1     replies: 0
Category 2
    Forum 3    threads: 14   replies 42

etc.
[download]

We'll need to set up a proper data structure for that, and I think you'll be needing an array of hashrefs, where each hashref represents one category. Each category-hashref has an array ref containing hashrefs for each forum, and counts. Something like this:

@all_categories = (
   {
      name => 'Category 1',
      forums => [ 
        {
           name => 'Forum 1',
           forum_id => 1,
           thread_count => 10,
           replies  => 20,
        },
     ...
     ],
  },
  ...
);
[download]

Your first query gets the categories. Good, that works.

Your second query looks like it tries to get everything in one go, per category. Maybe that'll work, but I think you need to group by all the columns that aren't aggregates (MAX, COUNT, etc). We might as well get the reply count here at the same time.
By the way, I don't think you can get the mx_thread.is_new here, without ruining the GROUPing. Besides, I don't even think it's relevant to your output.

           SELECT 
            IFNULL(COUNT(mx_replies.forum_id),0) AS reply_count, 
            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
           LEFT JOIN
             mx_replies
           ON
            mx_replies.forum_id = mx_forums.id
           WHERE
            mx_forums.category = ?

           GROUP BY 
            mx_forums.id,forum_name, forum_description
           ORDER BY 
            mx_forums.create_date DESC
[download]

Now I'm beginning to be confused by your code. The following loop doesn't seem to actually fetch any of the data, you're just executing it for each category.

    for ( @{ $categories } ) {

        $sth->execute( $_ ) or die $self->dbh->errstr;
        my $forums = $sth->fetchall_arrayref({}); 
        push @all_categories, { name => $_, forums => $forums, };
    }
[download]

And I think that's it! Now we pass this @all_categories to the template:

$tmpl->param( categories => \@all_categories );
[download]

Your template should look like this, roughly:

<ol>
<tmpl_loop categories>
<li> <tmpl_var name>
       <ol>
       <tmpl_loop forums>
       <li> <tmpl_var id> <tmpl_var forum_name>: <tmpl_var forum_descr
+iption> 
              threads: <tmpl_var thread_count> last post: <tmpl_var la
+st_post>
              replies: <tmpl_var reply_count>
      </li>
      </tmpl_loop>
      </ol>
</li>
</tmpl_loop>
</ol>
[download]

Unless I'm mistaken (and i'd like to stress that i've not tested anything i've written sofar), you don't need any of the other code, or queries. At the very least, see if you can try and figure out what Dumper( \@all_categories ) results in. I believe it's pretty much what you're after.

---

---

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! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

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:
	&		&
	<		&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.