Query Question
Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=15925
Printed Date: 13 April 2026 at 1:47pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Query Question
Posted By: felix.akinyemi
Subject: Query Question
Date Posted: 21 July 2005 at 9:57am
Question:
Is there a way to get all the moderators for a forum with just one query,
i have tried all the ways i thought might work but still no luck!!
Has anyone got any other ways!!
|
Replies:
Posted By: dj air
Date Posted: 21 July 2005 at 10:02am
SELECT tblAuthor.Username FROM tblAuthor WHERE tblAuthor.Group_ID = tblGroup.Group_ID AND tblGroup.Group_type = 3
on those lines should work
it says get the username from the author table where the Author group
ID = the groups table ID, and where the group type = 3
i think 3 is Moderator
that should help
|
Posted By: felix.akinyemi
Date Posted: 21 July 2005 at 10:07am
|
Haven't tried that yet but it looks like its only getting authors, i
meant for the default.asp page, where it displays the list of moderators
under the forum, i need to get all the groups and authors assign to the
forum to moderate it!!
|
Posted By: dj air
Date Posted: 21 July 2005 at 10:43am
SELECT tblAuthor.Username FROM tblAuthor WHERE tblAuthor.Group_ID =
tblGroup.Group_ID AND (( tblPermissions.Moderate = True AND tblGroupID
= tblPermssions.Group_ID) OR (tblPermissions.Moderate = True AND
tblGroupID = tblPermssions.Group_ID AND tblPermssions.Author_ID =
lngLoggedInUserID)) AND tblPermissions.Forum_ID=" & intForumID
& "
on those lines i think is what you want, the field names im not sure on, but something like that.
|
Posted By: dj air
Date Posted: 21 July 2005 at 10:57am
|
you may want to add tblAuthor.Author_ID within the select part so you can have a pop up profile
|
Posted By: felix.akinyemi
Date Posted: 21 July 2005 at 10:58am
oh i see what you mean but... OK this is wat i mean, should have done this first, ma bad!!
'Get the List of Group Moderators for the Forum
If blnShowMod Then
'Initalise the strSQL variable with an SQL statement
to query the database to get the moderators for this forum
If strDatabaseType = "SQLServer" Then
strSQL = "EXECUTE " & strDbProc & "ModeratorGroup @intForumID = " & intForumID
Else
strSQL = "SELECT " &
strDbTable & "Group.Group_ID, " & strDbTable & "Group.Name "
strSQL = strSQL & "FROM "
& strDbTable & "Group, " & strDbTable & "Permissions "
strSQL = strSQL & "WHERE "
& strDbTable & "Group.Group_ID = " & strDbTable &
"Permissions.Group_ID AND " & strDbTable &
"Permissions.Moderate = True AND " & strDbTable &
"Permissions.Forum_ID = " & intForumID & ";"
End If
'Query the database
rsCommon.Open strSQL, adoCon
'Initlaise the Moderators List varible if there are records returned for the forum
If NOT rsCommon.EOF Then strModeratorsList = "<br
/><span class=""smText"">" & strTxtModerators &
":</span>"
'Loop round to build a list of moderators, if there are any
Do While NOT rsCommon.EOF
'Place the moderators username into the string
strModeratorsList =
strModeratorsList & " <a href=""members.asp?GID=" &
rsCommon("Group_ID") & """ class=""smLink"">" &
rsCommon("Name") & "</a>"
'Move to the next record
rsCommon.MoveNext
Loop
'Close the recordset
rsCommon.Close
'Initalise the strSQL variable with an SQL statement
to query the database to get the moderators for this forum
If strDatabaseType = "SQLServer" Then
strSQL = "EXECUTE " & strDbProc & "Moderators @intForumID = " & intForumID
Else
strSQL = "SELECT " &
strDbTable & "Author.Author_ID, " & strDbTable &
"Author.Username "
strSQL = strSQL & "FROM "
& strDbTable & "Permissions, " & strDbTable & "Author "
strSQL = strSQL & "WHERE "
& strDbTable & "Author.Author_ID = " & strDbTable &
"Permissions.Author_ID AND " & strDbTable &
"Permissions.Moderate = True AND " & strDbTable &
"Permissions.Forum_ID = " & intForumID & ";"
End If
'Query the database
rsCommon.Open strSQL, adoCon
'Initlaise the Moderators List varible if there are records returned for the forum
If NOT rsCommon.EOF AND strModeratorsList = "" Then
strModeratorsList = "<br /><span class=""smText"">" &
strTxtModerators & ":</span>"
'Loop round to build a list of moderators, if there are any
Do While NOT rsCommon.EOF
'Place the moderators username into the string
strModeratorsList =
strModeratorsList & " <a
href=""JavaScript:openWin('pop_up_profile.asp?PF=" &
rsCommon("Author_ID") &
"','profile','toolbar=0,location=0,status=0,menubar=0,scrollbars=1,resizable=1,width=590,heig ht=425')""
class=""smLink"">" & rsCommon("Username") & "</a>"
'Move to the next record
rsCommon.MoveNext
Loop
'Close the recordset
rsCommon.Close
End If
|
need to compress both queries into one query!!
|
Posted By: felix.akinyemi
Date Posted: 21 July 2005 at 11:08am
strSQL = "SELECT " & strDbTable & "Group.Group_ID, " & strDbTable & "Group.Name "
strSQL = strSQL & "FROM " & strDbTable & "Group, " & strDbTable & "Permissions "
strSQL = strSQL & "WHERE " & strDbTable & "Group.Group_ID =
" & strDbTable & "Permissions.Group_ID AND " & strDbTable
& "Permissions.Moderate = True AND " & strDbTable &
"Permissions.Forum_ID = " & intForumID & ";"
|
AND
strSQL = "SELECT " & strDbTable & "Author.Author_ID, " & strDbTable & "Author.Username "
strSQL = strSQL & "FROM " & strDbTable & "Permissions, " & strDbTable & "Author "
strSQL = strSQL & "WHERE " & strDbTable & "Author.Author_ID
= " & strDbTable & "Permissions.Author_ID AND " &
strDbTable & "Permissions.Moderate = True AND " & strDbTable
& "Permissions.Forum_ID = " & intForumID & ";"
|
Then 2
|
Posted By: dj air
Date Posted: 21 July 2005 at 11:40am
strSQL = "SELECT " & strDbTable & "Group.Group_ID, " & strDbTable & "Group.Name, " & strDbTable & "Author.Author_ID, " & strDbTable & "Author.Username "
strSQL = strSQL & "FROM " & strDbTable & "Permissions, " & strDbTable & "Group, " & strDbTable & "Author "
strSQL = strSQL & "WHERE " & strDbTable & "Author.Author_ID
= " & strDbTable & "Permissions.Author_ID AND " &
strDbTable & "Permissions.Moderate = True AND " & strDbTable
& "Permissions.Forum_ID = " & intForumID & " AND " & strDbTable & "Group.Group_ID = " & strDbTable & "Author.Author_ID ORDER BY " & strDbTable & "Author.Username;"
dim blnFirst
dim intMODGroupID
blnFirst = True
then you need ot do a check
do while NOT rsCommon.eof
intMODGroupID = rsCommon("Group_ID")
if rsCommon("Group_ID") <> intMODGroupID OR blnFirst= True then
strModeratorsList = "<a href=""members.asp?GID=" & rsCommon("Group_ID") & """>" & rsCommon("Group_name") & "</a> :"
blnFirst = False
END IF
strModeratorsList =
strModeratorsList & " <a
href=""JavaScript:openWin('pop_up_profile.asp?PF=" &
rsCommon("Author_ID") &
"','profile','toolbar=0,location=0,status=0,menubar=0,scrollbars=1,resizable=1,width=590,heig ht=425')""
class=""smLink"">" & rsCommon("Username") & "</a>"
rsCommon.movenext
loop
of course you need to do a check before thats done making sure there are records to prevent errors.
that will give a list like
GroupName : username username Group Name : userame username
i aven't tested it in any way apart from in my head but should help
|
Posted By: felix.akinyemi
Date Posted: 21 July 2005 at 12:37pm
Yeah, had to make a few changes but it still getting the same error im
getting... the error is that is displays all the groups and all the
authors multi times instead of only the author and groups for the specific forum!!
This is the initial code i tried it with as well but no luck!! 
strSQL = "SELECT " & strDbTable & "Author.Author_ID, " &
strDbTable & "Author.Username, " & strDbTable &
"Group.Group_ID, " & strDbTable & "Group.Name "
strSQL = strSQL & "FROM " & strDbTable & "Author, " &
strDbTable & "Group, " & strDbTable & "Permissions "
strSQL = strSQL & "WHERE ((" & strDbTable & "Group.Group_ID
= " & strDbTable & "Permissions.Group_ID) OR (" &
strDbTable & "Author.Author_ID = " & strDbTable &
"Permissions.Author_ID)) AND " & strDbTable &
"Permissions.Moderate = " & blnSQLBool & " AND " &
strDbTable & "Permissions.Forum_ID = " & intForumID & ";"
|
|
Posted By: dj air
Date Posted: 21 July 2005 at 2:08pm
not sure on this now.
have you a link to the page that shows your out put, and with the query
you are using now. did you try mine, as i think that should have worked.
|
Posted By: felix.akinyemi
Date Posted: 22 July 2005 at 5:43am
The site are:
http://forums.fgcportal.com/test/default.asp - My Query - http://forums.fgcportal.com/test/default2.asp - Your Query but its ment to look like this http://forums.fgcportal.com/test/default3.asp - This
|
Posted By: dj air
Date Posted: 22 July 2005 at 7:18am
|
is default 3 a staic page or using a query, coz you could copy and paste that.
|
Posted By: dj air
Date Posted: 22 July 2005 at 7:21am
right looking at what you want.
you dont want to show the users within that groupo.
what you show in default 3 is what is within the default WWF Version i belive
|
Posted By: felix.akinyemi
Date Posted: 22 July 2005 at 7:12pm
default3.asp is the one built in WWF, but i need to get them in one
query to lessen DB calls cuz i need to add it to an external forum page
which already has enough DB calls!!
|
Posted By: dj air
Date Posted: 22 July 2005 at 7:23pm
try this
strSQL = "SELECT " & strDbTable & "Author.Author_ID, " &
strDbTable & "Author.Username, " & strDbTable &
"Group.Group_ID, " & strDbTable & "Group.Name "
strSQL = strSQL & "FROM " & strDbTable & "Author, " &
strDbTable & "Group, " & strDbTable & "Permissions "
strSQL = strSQL & "WHERE ((" & strDbTable & "Group.Group_ID
= " & strDbTable & "Permissions.Group_ID) OR (" &
strDbTable & "Author.Author_ID = " & strDbTable &
"Permissions.Author_ID AND NOT (" & strDbTable &
"Permssions.Group_ID = " & strDbTable &"Author_Group_ID AND " & strDbTable &
"Permssions.Moderate = True)) AND " & strDbTable &
"Permissions.Moderate = " & blnSQLBool & " AND " &
strDbTable & "Permissions.Forum_ID = " & intForumID & ";"
thats a weild guess
|
Posted By: felix.akinyemi
Date Posted: 22 July 2005 at 7:43pm
|
Somethings wrong http://forums.fgcportal.com/test/default.asp - Click Here
|
Posted By: felix.akinyemi
Date Posted: 22 July 2005 at 8:04pm
|
U know what, you can leave it out, no worries, ill find a way, thanks anyway!!
|
|