Gossamer Forum: Products: Gossamer Links: Version 1.x: GROUP BY problems (Stealth)

I am writing my own forum add-on for LINKS SQL v.1.3 since the one that widgetz had before was a bit cumbersome to understand...no offense, Jerry. Wink

Anyway...I've designed the appropriate tables and also started writing the forum/topic view scripts. I am having a problem with the FORUM view page:

http://www.anthrotech.com/cgibin/forum/

You'll notice that the only three forums and one category shows up, while there should be 3 categories and 12 forums.

NOTE: Announcements is associated with the Anthro TECH, L.L.C forum category, Linguistic Anthropology is associated with the Anthropological Sub-Fields forum category, and Professional Development is associated with the General forum category.

Here are the relevent codes in the Forum View script:

In Reply To:

sub display {
# --------------------------------------------------------
my ($in, $dynamic) = @_;
my ($author, $forum, $forumcat, $forumdesc, $forumid, $moderator, $output, $posts, $rec, $showforums, $status, $sth, $updated, $userperm);
my $s = $in->param('s') || $in->cookie('s');
$USER = &authenticate ($s);
my $title_linked = &build_linked_forum_title ("Anthro TECH Discussion Forum");
my $query = qq!
SELECT *
FROM Forum_Topics LEFT JOIN Forum_Cats ON Forum_Topics.ForumCatID = Forum_Cats.ForumCatID
WHERE (Forum_Topics.ForumActive = 'Y')
GROUP BY Forum_Cats.ForumCatName
ORDER BY Forum_Topics.ForumName
!;
$sth = $FORUMSDB->prepare($query);
$sth->execute();
while ($rec = $sth->fetchrow_hashref) {
$moderator = $rec->{ModeratorID};
$forumid = $rec->{ForumID};
$forum = $rec->{ForumName};
$forumcat = $rec->{ForumCatName};
$forumdesc = $rec->{ForumDescription};
$status = $rec->{ForumStatus};
$posts = $rec->{NumberPosts};
$updated = $rec->{Last_Updated};
undef $Links::DBSQL::DBH;
my $user = $USERDB->get_record ($moderator, 'HASH');
$author = $user->{Author};
$userperm = $user->{Username_Permission};
$showforums .= &site_html_forum_link ({Author => $author, ForumID => $forumid, ForumName => $forum, FormCatName => $forumcat, ForumDescription => $forumdesc, ForumStatus => $status, Last_Updated => $updated, ModeratorID => $moderator, NumberPosts => $posts, Username_Permission => $userperm});
}
if (defined $USER) {
&site_html_forum_home ({ForumCatName => $forumcat, Forums => $showforums, Username => $USER{Username}, %$USER, title_linked => $title_linked}, $dynamic);
}
else {
&site_html_forum_home ({ForumCatName => $forumcat, Forums => $showforums, title_linked => $title_linked}, $dynamic);
}
}

If I take out the GROUP BY codes, then all the forums are listed in alphabetical order but are not grouped by their category.

Now, I've spent most of the afternoon reading up on GROUP BY functions at http://www.mysql.com and the above codes should work, but I believe that the loop codes may be causing the problem. Yet I also tried running the above SQL statement via the SQL Monitor in MySQLMan and the same results appear as they do in the above linked web page.

I also searched this forum for any ideas, but GROUP BY is not discussed that often...

Just need another pair of eyes to spot any problems.

Any ideas or suggestions are greatly appreciated.

Thanks in advance.

Regards,

Eliot Lee

Subject	Author	Views	Date
GROUP BY problems	Stealth	6056	Jul 8, 2001, 3:30 PM

Re: GROUP BY problems	sponge	5913	Jul 8, 2001, 11:35 PM

Re: GROUP BY problems	Stealth	5909	Jul 10, 2001, 5:44 PM

Re: GROUP BY problems	sponge	5915	Jul 10, 2001, 7:53 PM

Re: GROUP BY problems	Stealth	5905	Jul 10, 2001, 8:12 PM

Re: GROUP BY problems	sponge	5900	Jul 11, 2001, 1:41 AM

Re: GROUP BY problems	Stealth	5891	Jul 11, 2001, 4:54 PM

Re: GROUP BY problems	sponge	5857	Jul 21, 2001, 8:20 PM

Re: GROUP BY problems	Stealth	5858	Jul 22, 2001, 9:41 PM

Re: GROUP BY problems	sponge	5855	Jul 22, 2001, 11:41 PM

Re: GROUP BY problems	Stealth	5836	Jul 23, 2001, 7:41 AM