Categorizing using DBI

stonecolddevin has asked for the wisdom of the Perl Monks concerning the following question:

Hey all.

I'm at my wit's end with this web application/forum.

I'm attempting to accomplish something like vBulletin or phpbb where each category is shown and below it all of it's forums. I'm using CGI::Application with the DBH plugin, DBI and HTML::Template.

Simple enough, no?

Well obviously as you can tell, i'm having issues with this.

At first i was able to retrieve the categories and show the forums below it, however i wasn't able to restrict the forums to their respective categories.

Currently, I'm attempting to use a WHERE clause to provide some restriction/categorization, but to no avail.

Here is the main sub that retrieves the categories and their respective forums:

  ## 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]

Any and all help is much-ly appreciated.

meh.

Comment on Categorizing using DBI Select or Download Code

Replies are listed 'Best First'.
Re: Categorizing using DBI by rhesa (Vicar) on Mar 06, 2006 at 00:25 UTC
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.	[reply] [d/l] [select]
Re^2: Categorizing using DBI by stonecolddevin (Parson) on Mar 06, 2006 at 00:34 UTC
Ahhh what a refreshing read! You successfully broke down my code into manageable bits with fresh eyes that i needed, thanks! :-) meh.	[reply]