| Author |
 Topic Search  Topic Options
|
felix.akinyemi 
Groupie
Joined: 09 December 2004
Location: United States
Status: Offline
Points: 113
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Query Question
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!!
|
 |
dj air
Senior Member
Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
|
Post Options
Thanks(0)
Quote Reply
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
|
|
felix.akinyemi
Groupie
Joined: 09 December 2004
Location: United States
Status: Offline
Points: 113
|
Post Options
Thanks(0)
Quote Reply
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!!
|
|
dj air
Senior Member
Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
dj air
Senior Member
Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
|
Post Options
Thanks(0)
Quote Reply
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
|
|
felix.akinyemi
Groupie
Joined: 09 December 2004
Location: United States
Status: Offline
Points: 113
|
Post Options
Thanks(0)
Quote Reply
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!!
|
|
felix.akinyemi
Groupie
Joined: 09 December 2004
Location: United States
Status: Offline
Points: 113
|
Post Options
Thanks(0)
Quote Reply
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
|
|
dj air
Senior Member
Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
|
Post Options
Thanks(0)
Quote Reply
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
|
|
