Categorizing using DBI
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.
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 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
);
# 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}} );
}
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;
Any and all help is much-ly appreciated.
meh.
Re: Categorizing using DBI
created: 2006-03-05 19:25:21
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.
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,
},
...
],
},
...
);
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
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, };
}
And I think that's it! Now we pass this @all_categories to the template:
$tmpl->param( categories => \@all_categories );Your template should look like this, roughly:
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.
: threads: last post: replies: