Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

GROUP BY problems

Quote Reply
GROUP BY problems
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
Quote Reply
Re: GROUP BY problems In reply to
Change:
Code:
GROUP BY Forum_Cats.ForumCatName
ORDER BY Forum_Topics.ForumName
To:
Code:
GROUP BY Forum_Cats.ForumCatName, Forum_Topics.ForumName
--Drew
Links 2.0 stuff
http://www.camelsoup.com
Quote Reply
Re: GROUP BY problems In reply to
Thank you very much, Drew! I appreciate the suggestion!

It worked sort of...I believe the problem is with the loop codes, which need to be edited...since when I used your codes, duplicate forums showed with UNKNOWN TAG errors.

Thanks again! You at least provided a good direction to persue! Smile

Regards,

Eliot Lee
Quote Reply
Re: GROUP BY problems In reply to
Hm... worked fine from mysqlman. I tried anyway. What I'm doing for my forum program is running multiple queries. The first query gets all the group data. Then I loop through that and do a query to get the categories for each group.

--Drew
Links 2.0 stuff
http://www.camelsoup.com
Quote Reply
Re: GROUP BY problems In reply to
Well, I found out that the UNKNOWN TAGS were actually for the forum categories....

Now, my forum looks like:

Category1
Category2
Category3
-------------
Forum1
Forum2
Forum3
Forum4
Forum5
Forum6
Forum7
Forum8

etc...

And the forums are "grouped"/"sorted" by forum category, but the categories all print at the top of the forum page, with the forums at the bottom.

You can see at:

http://www.anthrotech.com/...forum/testindex2.cgi

I did rewrite the queries to look like the following:

Code:

sub display {
# --------------------------------------------------------
my ($in, $dynamic) = @_;
my %OUT;
my ($author, $forum, $forumcat, $forumcatid, $forumdesc, $forumid, $getforum, $moderator, $output, $posts, $rec, $showfcat, $showforums, $status, $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 $sth = $FORUMCATDB->prepare ("SELECT * FROM Forum_Cats GROUP BY ForumCatName");
$sth->execute() or die $DBI::errstr;
while (my $rec = $sth->fetchrow_hashref) {
$OUT{Categories} .= &site_html_forumcat_link ($rec);
my $sti = $FORUMSDB->prepare ("SELECT * FROM Forum_Topics WHERE ForumCatID = ${$rec}{ForumCatID} ORDER BY ForumName");
$sti->execute() or die $DBI::errstr;
while (my $rec = $sti->fetchrow_hashref) {
$moderator = $rec->{ModeratorID};
undef $Links::DBSQL::DBH;
my $user = $USERDB->get_record ($moderator, 'HASH');
foreach (keys %$user) {
$rec->{$_} = $user->{$_};
}
$OUT{Topics} .= &site_html_forum_link ($rec);
}
}
if (defined $USER) {
&site_html_forum_home ({Username => $USER{Username}, title_linked => $title_linked, %$USER, %OUT}, $dynamic);
}
else {
&site_html_forum_home ({title_linked => $title_linked, %OUT}, $dynamic);
}
}


Basically...an extension from what Jerry Su wrote awhile back....

I bet that the problem could be in the template file, which the forum home template file looks like the following:

Code:

<html>
<head>
<title><%site_title%></title>
<%site_header%>
<%title_linked%>
<%if Categories%>
<%if Topics%>

<p>
<span class="header">Welcome
<%if Username%>
<%Username%>!
</span>
<span class="smtext">
If you are not <%Username%>,
<span class="boldtext"><a href="<%build_logout_url%>">Logout</a>!
</span>
<%endif%>
<%ifnot Username%>
Guest User!
</span>
<span class="smboldtext">
<a href="<%build_login_url%>">Login</a> -
<a href="<%build_signup_url%>">Register Account</a>
</span>
<%endif%>
<p>
<div align="center"><center>
<table border="1" width="100%" cellpadding="2" cellspacing="0">
<%Categories%>
<tr bgcolor="00009c">
<td valign="top" width="60%">
<span class="subwhiteheader">Forum</span>
</td>
<td valign="top" width="10%">
<span class="subwhiteheader">Type</span>
</td>
<td valign="top" width="15%">
<span class="subwhiteheader">Moderator</span>
</td>
<td valign="top" width="15%">
<span class="subwhiteheader">Post History</span>
</td></tr>
<%Topics%>
</table>
</div></center>
<%endif%>
<%endif%>

<%site_footer%>


I have two other template files (referenced by <%Categories%> and <%Topics%> tags)....

forumcatlink.html --> Categories

Code:

<tr bgcolor="000000">
<td valign="top" width="100%" colspan="4">
<span class="subwhiteheader"><%ForumCatName%></span>
</td></tr>


forumlink.html --> Topics

Code:

<tr bgcolor="FFFFCC">
<td valign="top" width="60%">
<span class="boldtext">
<a href="<%build_forum_url%>?topics=<%ForumID%>"><%ForumName%></a>
</span>
<%if ForumDescription%>


<span class="smtext"><%ForumDescription%></span>
<%endif%>
</td>
<td valign="top" width="10%">
<span class="text"><%ForumStatus%></span>
</td>
<td valign="top" width="15%">
<span class="text">
<%if Username_Permission eq 'Y'%>
<%if Author%>
<a href="<%build_userdetail_url%>?ID=<%ModeratorID%>"><%Author%></a>
<%endif%>
<%ifnot Author%>
<a href="<%build_userdetail_url%>?ID=<%ModeratorID%>">Not Provided</a>
<%endif%>
<%endif%>
<%if Username_Permission eq 'N'%>
<a href="<%build_userdetail_url%>?ID=<%ModeratorID%>">Anonymous</a>
<%endif%>
</span>
</td>
<td valign="top" width="15%">
<span class="boldtext">
<%if NumberPosts eq '0'%>
<%NumberPosts%> Posts
<%endif%>
<%if NumberPosts eq '1'%>
<%NumberPosts%> Post
<%endif%>
<%if NumberPosts gt '1'%>
<%NumberPosts%> Posts
<%endif%>
</span>
<%if ForumNew eq 'Y'%>
<%nbsp%><%new1img%>
<%endif%>


<span class="smtext">
<span class="smboldtext">Last Updated:</span> <%Last_Updated%>
</span>
</td></tr>


Regards,

Eliot Lee
Quote Reply
Re: GROUP BY problems In reply to
It looks like you got part of your templates/codes mixed up. Try something along the lines of:

Code:
my $categories = '';
my $groups = $dbh->prepare(qq(select * from ForumGroups order by Title));
$groups->execute() or die $DBI::errstr;
while (my $g_rec = $groups->fetchrow_hashref()) {
my $subcats = '';
my $cats = $dbh->prepare(qq(select * from ForumCats where GroupID = "$g_rec->{'ID'}" order by Title));
$cats->execute() or die $DBI::errstr;
while (my $c_rec = $cats->fetchrow_hashref()) {
$subcats .= &site_html_forum_cat ( $c_rec );
}
$categories .= &site_html_forum_group ( $g_rec, $subcats );
}
my $index = &site_html_forum_home ( $categories );
It doesn't look like you should be displaying both the 'Categories' and the 'Topics' this way... The topics should be passed into the 'Categories' template, then to the index page.

--Drew
Links 2.0 stuff
http://www.camelsoup.com
Quote Reply
Re: GROUP BY problems In reply to
Right on, Drew!! Thank you very much for pointing me in the right direction...I had to adjust the codes slightly to work with LINKS SQL....

index.cgi ==> Forum Display Sub

In Reply To:

sub display {
# --------------------------------------------------------
my ($in, $dynamic) = @_;
my %OUT;
my ($forumcatrec, $forums, $forum_list, $moderator, $output);
my $s = $in->param('s') || $in->cookie('s');
$USER = &authenticate ($s);
my $title_linked = &build_linked_forum_title ("Anthro TECH Discussion Forum");

my $forumcats = '';
my $sth = $FORUMCATDB->prepare ("SELECT * FROM Forum_Cats ORDER BY ForumCatName");
$sth->execute() or die $DBI::errstr;
while (my $forumcatrec = $sth->fetchrow_hashref) {
my $forumcatname = $forumcatrec->{ForumCatName};
my $forum_list = '';
my $sti = $FORUMSDB->prepare ("SELECT * FROM Forum_Topics WHERE ForumCatID = ${$forumcatrec}{ForumCatID} ORDER BY ForumName");
$sti->execute() or die $DBI::errstr;
while (my $forums = $sti->fetchrow_hashref) {
$moderator = $forums->{ModeratorID};
undef $Links::DBSQL::DBH;
my $user = $USERDB->get_record ($moderator, 'HASH');
foreach (keys %$user) {
$forums->{$_} = $user->{$_};
}
$forum_list .= &site_html_forum_link ($forums);
}
$forumcats .= &site_html_forumcat_link ({ForumCatName => $forumcatname, Forum_List => $forum_list});
}
if (defined $USER) {
&site_html_forum_home ({Forums => $forumcats, Username => $USER{Username}, title_linked => $title_linked, %$USER});
}
else {
&site_html_forum_home ({Forums => $forumcats, title_linked => $title_linked});
}
}


And in the forumcatlinks.html file, I added <%Forum_List%> and it worked!

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

Now, I have to address some efficiency problems since I noticed that when I executed the script, the mysqld was running at 90% WCPU and 90% CPU.

Regards,

Eliot Lee
Quote Reply
Re: GROUP BY problems In reply to
you know I was just thinking about this again and have come up with a simple way to avoid doing multiple queries.

Basicly, all you have to do is to do two ORDER BYs, first by forum group, second by forum category. Then you loop through the results. Make a hash of forum group names as you go. Check if the group name has been added to the hash already, and if not, then print the group heading.

Happy coding,

--Drew
http://www.camelsoup.com
ftp://ftp.camelsoup.com
Quote Reply
Re: GROUP BY problems In reply to
Great advice, Drew!

However, if you notice in my first post, I did put it all in one query and the results were not sorted properly. But I'll play around with the double ORDER BY function and see what I come up with.

Regards,

Eliot Lee Wink
http://anthrotech.com/
Quote Reply
Re: GROUP BY problems In reply to
yeah I know... it'd be nice if Alex could enlighten us on why that doesn't work.

Happy coding,

--Drew
http://www.camelsoup.com
ftp://ftp.camelsoup.com
Quote Reply
Re: GROUP BY problems In reply to
Yes...it is weird...since I am able to emulate the same output (as the double query) with one query within Cold Fusion (using a similar GROUP function) and SQL Server database. I know that each database application has its own set of protocals and functions...it would be nice if they all were standardized to use the same version of SQL.

Regards,

Eliot Lee Wink
http://anthrotech.com/