Mephisteus
09-01-2004, 02:21 PM
Alright, I am using my forum for a clan, and we have about 15 different ranks. Which I want ordered from high to low on the memberlist and who is online.
In order to do this I made an orderid in the usergroup table and changed the query in memberlist.php
From:
$users = $DB_site->query("
SELECT user.*,usertextfield.*,userfield.*, user.userid, options,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
" . iif($show['reputationcol'], ",IF((options & $_USEROPTIONS[showreputation]), reputation, 0) AS reputationscore,level") . "
" . iif($show['avatarcol'], ',avatar.avatarpath,NOT ISNULL(customavatar.avatardata) AS hascustomavatar,customavatar.dateline AS avatardateline') ."
" . iif($show['profilepiccol'], ',customprofilepic.userid AS profilepic, customprofilepic.dateline AS profilepicdateline') . "
" . iif($sortfield=='lastvisit', " , IF((options & $_USEROPTIONS[invisible]), joindate, lastactivity) AS lastvisittime ") . "
" . iif($usergroupid, ", NOT ISNULL(usergroupleader.usergroupid) AS isleader") . "
FROM " . TABLE_PREFIX . "user AS user
LEFT JOIN " . TABLE_PREFIX . "usertextfield AS usertextfield ON(usertextfield.userid=user.userid)
LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON(userfield.userid=user.userid)
" . iif($show['reputationcol'], "LEFT JOIN " . TABLE_PREFIX . "reputationlevel AS reputationlevel ON(user.reputationlevelid=reputationlevel.reputati onlevelid) ") . "
" . iif($show['avatarcol'], "LEFT JOIN " . TABLE_PREFIX . "avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN " . TABLE_PREFIX . "customavatar AS customavatar ON(customavatar.userid = user.userid)") . "
" . iif($show['profilepiccol'], "LEFT JOIN " . TABLE_PREFIX . "customprofilepic AS customprofilepic ON (user.userid = customprofilepic.userid) ") . "
" . iif($usergroupid, "LEFT JOIN " . TABLE_PREFIX . "usergroupleader AS usergroupleader ON (user.userid = usergroupleader.userid AND usergroupleader.usergroupid=$usergroupid) ") . "
WHERE $condition
AND user.usergroupid IN ($ids)
ORDER BY $sqlsort $sortorder $secondarysortsql
LIMIT " . ($limitlower-1) . ", $perpage
");
To:
$users = $DB_site->query("
SELECT user.*,usertextfield.*,userfield.*,usergroup.*, user.userid, options,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
" . iif($show['reputationcol'], ",IF((options & $_USEROPTIONS[showreputation]), reputation, 0) AS reputationscore,level") . "
" . iif($show['avatarcol'], ',avatar.avatarpath,NOT ISNULL(customavatar.avatardata) AS hascustomavatar,customavatar.dateline AS avatardateline') ."
" . iif($show['profilepiccol'], ',customprofilepic.userid AS profilepic, customprofilepic.dateline AS profilepicdateline') . "
" . iif($sortfield=='lastvisit', " , IF((options & $_USEROPTIONS[invisible]), joindate, lastactivity) AS lastvisittime ") . "
" . iif($usergroupid, ", NOT ISNULL(usergroupleader.usergroupid) AS isleader") . "
FROM " . TABLE_PREFIX . "user AS user
LEFT JOIN " . TABLE_PREFIX . "usertextfield AS usertextfield ON(usertextfield.userid=user.userid)
LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON(userfield.userid=user.userid)
LEFT JOIN " . TABLE_PREFIX . "usergroup AS usergroup ON(usergroup.usergroupid=user.usergroupid)
" . iif($show['reputationcol'], "LEFT JOIN " . TABLE_PREFIX . "reputationlevel AS reputationlevel ON(user.reputationlevelid=reputationlevel.reputati onlevelid) ") . "
" . iif($show['avatarcol'], "LEFT JOIN " . TABLE_PREFIX . "avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN " . TABLE_PREFIX . "customavatar AS customavatar ON(customavatar.userid = user.userid)") . "
" . iif($show['profilepiccol'], "LEFT JOIN " . TABLE_PREFIX . "customprofilepic AS customprofilepic ON (user.userid = customprofilepic.userid) ") . "
" . iif($usergroupid, "LEFT JOIN " . TABLE_PREFIX . "usergroupleader AS usergroupleader ON (user.userid = usergroupleader.userid AND usergroupleader.usergroupid=$usergroupid) ") . "
WHERE $condition
AND user.usergroupid IN ($ids)
ORDER BY usergroup.orderid DESC, $sqlsort $sortorder $secondarysortsql
LIMIT " . ($limitlower-1) . ", $perpage
");
This works fine for most part, however. I have 3 administrators (me and the two highest ranking members). The administrator is our primary group and the displaygroup is our rank (which we want it ordered by). Every person that has his rank as primary group gets ordered correctly while me and the admins get the title admin on memberlist.php and are ordered with orderid 0, so we show up among the regular members.
If anyone has any idea whats wrong or how to fix it, thanks :)
In order to do this I made an orderid in the usergroup table and changed the query in memberlist.php
From:
$users = $DB_site->query("
SELECT user.*,usertextfield.*,userfield.*, user.userid, options,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
" . iif($show['reputationcol'], ",IF((options & $_USEROPTIONS[showreputation]), reputation, 0) AS reputationscore,level") . "
" . iif($show['avatarcol'], ',avatar.avatarpath,NOT ISNULL(customavatar.avatardata) AS hascustomavatar,customavatar.dateline AS avatardateline') ."
" . iif($show['profilepiccol'], ',customprofilepic.userid AS profilepic, customprofilepic.dateline AS profilepicdateline') . "
" . iif($sortfield=='lastvisit', " , IF((options & $_USEROPTIONS[invisible]), joindate, lastactivity) AS lastvisittime ") . "
" . iif($usergroupid, ", NOT ISNULL(usergroupleader.usergroupid) AS isleader") . "
FROM " . TABLE_PREFIX . "user AS user
LEFT JOIN " . TABLE_PREFIX . "usertextfield AS usertextfield ON(usertextfield.userid=user.userid)
LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON(userfield.userid=user.userid)
" . iif($show['reputationcol'], "LEFT JOIN " . TABLE_PREFIX . "reputationlevel AS reputationlevel ON(user.reputationlevelid=reputationlevel.reputati onlevelid) ") . "
" . iif($show['avatarcol'], "LEFT JOIN " . TABLE_PREFIX . "avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN " . TABLE_PREFIX . "customavatar AS customavatar ON(customavatar.userid = user.userid)") . "
" . iif($show['profilepiccol'], "LEFT JOIN " . TABLE_PREFIX . "customprofilepic AS customprofilepic ON (user.userid = customprofilepic.userid) ") . "
" . iif($usergroupid, "LEFT JOIN " . TABLE_PREFIX . "usergroupleader AS usergroupleader ON (user.userid = usergroupleader.userid AND usergroupleader.usergroupid=$usergroupid) ") . "
WHERE $condition
AND user.usergroupid IN ($ids)
ORDER BY $sqlsort $sortorder $secondarysortsql
LIMIT " . ($limitlower-1) . ", $perpage
");
To:
$users = $DB_site->query("
SELECT user.*,usertextfield.*,userfield.*,usergroup.*, user.userid, options,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
" . iif($show['reputationcol'], ",IF((options & $_USEROPTIONS[showreputation]), reputation, 0) AS reputationscore,level") . "
" . iif($show['avatarcol'], ',avatar.avatarpath,NOT ISNULL(customavatar.avatardata) AS hascustomavatar,customavatar.dateline AS avatardateline') ."
" . iif($show['profilepiccol'], ',customprofilepic.userid AS profilepic, customprofilepic.dateline AS profilepicdateline') . "
" . iif($sortfield=='lastvisit', " , IF((options & $_USEROPTIONS[invisible]), joindate, lastactivity) AS lastvisittime ") . "
" . iif($usergroupid, ", NOT ISNULL(usergroupleader.usergroupid) AS isleader") . "
FROM " . TABLE_PREFIX . "user AS user
LEFT JOIN " . TABLE_PREFIX . "usertextfield AS usertextfield ON(usertextfield.userid=user.userid)
LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON(userfield.userid=user.userid)
LEFT JOIN " . TABLE_PREFIX . "usergroup AS usergroup ON(usergroup.usergroupid=user.usergroupid)
" . iif($show['reputationcol'], "LEFT JOIN " . TABLE_PREFIX . "reputationlevel AS reputationlevel ON(user.reputationlevelid=reputationlevel.reputati onlevelid) ") . "
" . iif($show['avatarcol'], "LEFT JOIN " . TABLE_PREFIX . "avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN " . TABLE_PREFIX . "customavatar AS customavatar ON(customavatar.userid = user.userid)") . "
" . iif($show['profilepiccol'], "LEFT JOIN " . TABLE_PREFIX . "customprofilepic AS customprofilepic ON (user.userid = customprofilepic.userid) ") . "
" . iif($usergroupid, "LEFT JOIN " . TABLE_PREFIX . "usergroupleader AS usergroupleader ON (user.userid = usergroupleader.userid AND usergroupleader.usergroupid=$usergroupid) ") . "
WHERE $condition
AND user.usergroupid IN ($ids)
ORDER BY usergroup.orderid DESC, $sqlsort $sortorder $secondarysortsql
LIMIT " . ($limitlower-1) . ", $perpage
");
This works fine for most part, however. I have 3 administrators (me and the two highest ranking members). The administrator is our primary group and the displaygroup is our rank (which we want it ordered by). Every person that has his rank as primary group gets ordered correctly while me and the admins get the title admin on memberlist.php and are ordered with orderid 0, so we show up among the regular members.
If anyone has any idea whats wrong or how to fix it, thanks :)