my ($rv,$sth) = $S->db_select({
FROM => 'stories, viewed_stories',
WHAT => 'stories.sid, title, COUNT(viewed_stories.sid) AS total',
WHERE => 'viewed_stories.sid = stories.sid AND hotlisted = 1',
GROUP_BY => 'viewed_stories.sid',
ORDER_BY => 'total DESC',
LIMIT => '10'
});

my @data;
my $where;
while (my @s = $sth->fetchrow_array) {
push(@data, \@s);
$where .= ' OR ' if $where;
$where .= 'sid = ' . $S->{DBH}->quote($s[0]);
}
$sth->finish;

($rv,$sth) = $S->db_select({
FROM => 'comments',
WHAT => 'sid, COUNT(sid)',
WHERE => $where,
GROUP_BY => 'sid'
});

my %counts;
while (my $s = $sth->fetchrow_arrayref) {
$counts{ $s->[0] } = $s->[1];
my $c = $counts{ $s->[0] };
}
$sth->finish;

my $out;
foreach my $d (@data) {
my $up = ($d->[2] == 1) ? '' : 's';
my $c = $counts{$d->[0]} || 0;
my $cp = ($c == 1) ? '' : 's';
$out .= qq~%%dot%% <a href="%%rootdir%%/story/$d->[0]">$d->[1]</a> ($c comment$cp) by $d->[2] user$up<br>~;
}

return $out;

Post A Comment