SQL Query help on Moderator user group

[induslady]

Hello

The code from the file functions_forumlist.php that gets the moderators for a forum and displays it in the Moderator column in forum home page:

$forummoderators = $vbulletin->db->query_read_slave("
SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroupid) AS displaygroupid, infractiongroupid
FROM " . TABLE_PREFIX . "moderator AS moderator
INNER JOIN " . TABLE_PREFIX . "user AS user USING(userid)
" . ($userid != null ? "WHERE moderator.userid = " . intval($userid) : "") . "
");
while ($moderator = $vbulletin->db->fetch_array($forummoderators))
{
fetch_musername($moderator);
$imodcache["$moderator[forumid]"]["$moderator[userid]"] = $moderator;
$mod["$moderator[userid]"] = 1;
}
$vbulletin->db->free_result($forummoderators);
}

I need to get only the moderators with the specific usergroup ( ids: 5,6,7). For example if in a particular forum there are three moderators say x, y, z with usergroupids : 4 , 6, 8 respectively then I have to obtain only the moderator y. Only this moderator y need to be displayed in the Moderator column in the forum home page.

Let me know how the above query could be modified to make this effective.

Thanks for patiently going through the codes and any help is appreciated.

[induslady]

Hello,

I need to give an extra conditional statement with the WHERE clause as follows:

user.usergroupid IN ( 5, 6, 7 ) OR user.displaygroupid IN ( 5, 6, 7 )

When I tried adding the above code in the query, it is throwing sql query error.

Let me know how exactly these code lines could be inserted.
Thanks in advance.

[Eikinskjaldi]

Hello

The code from the file functions_forumlist.php that gets the moderators for a forum and displays it in the Moderator column in forum home page:

$forummoderators = $vbulletin->db->query_read_slave("
SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroupid) AS displaygroupid, infractiongroupid
FROM " . TABLE_PREFIX . "moderator AS moderator
INNER JOIN " . TABLE_PREFIX . "user AS user USING(userid)
" . ($userid != null ? "WHERE moderator.userid = " . intval($userid) : "") . "
");
while ($moderator = $vbulletin->db->fetch_array($forummoderators))
{
fetch_musername($moderator);
$imodcache["$moderator[forumid]"]["$moderator[userid]"] = $moderator;
$mod["$moderator[userid]"] = 1;
}
$vbulletin->db->free_result($forummoderators);
}

I need to get only the moderators with the specific usergroup ( ids: 5,6,7). For example if in a particular forum there are three moderators say x, y, z with usergroupids : 4 , 6, 8 respectively then I have to obtain only the moderator y. Only this moderator y need to be displayed in the Moderator column in the forum home page.

Let me know how the above query could be modified to make this effective.

Thanks for patiently going through the codes and any help is appreciated.

It would help if you included the error.

try this

$forummoderators = $vbulletin->db->query_read_slave("
SELECT moderator.*, user.username,
IF(user.displaygroupid = 0, user.usergroupid, user.displaygroupid) AS displaygroupid, infractiongroupid
FROM " . TABLE_PREFIX . "moderator AS moderator
INNER JOIN " . TABLE_PREFIX . "user AS user USING(userid)
WHERE displaygroupid in (5,6,7)
" . ($userid != null ? "AND moderator.userid = " . intval($userid) : "") . "
");