Roster Page

[Hairy]

Did you modify the code at all?

I tested the code in question and it works perfectly for me.

You may want to post the code if you have modified in any way otherwise ask for support in the support area in case it is something else causing this.

Cheers

[kaburan]

Hello folks,

I'm having one hell of a time trying to perform only one thing with the code I am using. I am trying to utilize both Custom Profile Fields Mod with the Roster Script. Note, I am only uzing one custom profile field and thats to allow a member to select which Division they are in. The division in turn needs to be displayed on the roster. Instead, I end up getting a

Unknown column 'CP1' in 'field list'
File: /var/www/vhosts/vx9.com/httpdocs/forums/Sources/Load.php(1750) : eval()'d code(209) : eval()'d code
Line: 52

I have searched for assistance, I have searched the boards, I have been to a breaking point on this yet I wont give up. If I could have any of you take a look at this code and assist me I would highly appreciate it.
So, I have my code here

Code Select Expand



global $db_prefix, $scripturl, $txt, $settings, $options;


// These must be in the order you wish them to print out on your roster.
// VERY IMPORTANT!!!! -- they must be exactly the same as the group names in the database, including
// capitalization and spaces.
$rankorder = array(
  'General',
  'Lieutenant General',
  'Major General',
  'Brigadier General',
  'Colonel',
  'Lieutenant Colonel',
  'Major',
  'Captain',
  '1st Lieutenant',
  '2nd Lieutenant',
  'Sergeant Major',
  'First Sergeant',
  'Sergeant First Class',
  'Staff Sergeant',
  'Sergeant',
  'Corporal',
  'Private First Class',
  'Private',
  'Recruit',
);

$groups = '';
//Does all the real work here for showing groups.
$query = db_query("SELECT ID_GROUP, groupName, onlineColor, stars
  FROM {$db_prefix}membergroups", __FILE__, __LINE__);
while ($row = mysql_fetch_assoc($query))
{
  $groups[$row['groupName']]  = array(
    'id' => $row['ID_GROUP'],
    'color' => empty($row['onlineColor']) ? '' : $row['onlineColor'],
    'stars' => empty($row['stars']) ? '' : substr($row['stars'],2),
  );
}
mysql_free_result($query);

foreach ($rankorder as $rank)
{
//Now get all the users
  $query2 = db_query("
     SELECT ID_GROUP, ID_MEMBER, realName, avatar, usertitle,
           emailAddress, hideEmail, lastLogin, location, CP1, personaltext,
           totalTimeLoggedIn, dateRegistered, ICQ, AIM, YIM, MSN,
           hideEmail, emailAddress
    FROM {$db_prefix}members
    WHERE ID_GROUP = '" . $groups[$rank]['id'] . "' ", __FILE__, __LINE__);

  if(db_affected_rows() != 0)
  {
    echo '<table border="0" cellspacing="1" cellpadding="5" align="center" class="bordercolor">
      <tr><td colspan="7" class="catbg"><font size="4" face="verdana"><b>' . $rank . '</b></font></td></tr>
      <tr><td rowspan="70" width="85" class="profilebg" valign="top">
        <center><img src="' . $settings['images_url'] . '/' . $groups[$rank]['stars'] . '"></center></td>';
      echo '<td class="catinfo" width="270"><b>ALIAS</b></td>';
      echo '<td class="catinfo" width="130"><b>DIVISION</b></td>';
      echo '<td class="catinfo" width="175"><b>LAST ONLINE</b></td>';
      echo '<td class="catinfo" width="100"><b>LOCATION</b></td>';
      echo '<td class="catinfo" width="175"><b>CONTACT</b></td></tr><tr>';
      while ($row2 = mysql_fetch_assoc($query2))
      {
echo '<td class="windowbg" height="40"><a href="' . $scripturl . '?action=profile;u=' . $row2['ID_MEMBER'] . '"><b><font color="' . $groups[$rank]['color'] . '" size="3" face="verdana">' . $row2['realName'] . '</b>';

echo '</font></font></a><br><font size="1"> ' . $row2['personaltext'] . '</font></a></td><td class="windowbg">' . $row2['CP1'] . '</td>';
        echo '<td class="windowbg">' . timeformat($row2['lastLogin']) . '</td>';
        echo '<td class="windowbg" align="center">';       
echo '<i>' . $row2['location'] . '</i>';
        echo '</td><td class="windowbg">';
//Send email row
        if($row2['hideEmail'] == 0)
          echo '<a href="mailto:', $row2['emailAddress'], '"><img src="' . $settings['images_url'] . '/email_sm.gif" alt="email" /></a> ';

        if($row2['ICQ'] != '')
          echo '<a href="http://www.icq.com/whitepages/about_me.php?uin=' . $row2['ICQ'] . '" target="_blank"><img src="http://status.icq.com/online.gif?img=5&icq=' . $row2['ICQ'] . '" alt="' . $row2['ICQ'] . '" width="18" height="18" border="0" /></a> ';
        if($row2['AIM'] != '')
          echo '<a href="aim:goim?screenname=' . urlencode(strtr($row2['AIM'], array(' ' => '%20'))) . '&message=' . $txt['aim_default_message'] . '"><img src="' . $settings['images_url'] . '/aim.gif" alt="' . $row2['AIM'] . '" border="0" /></a> ';
        if($row2['YIM'] != '')
          echo '<a href="http://edit.yahoo.com/config/send_webmesg?.target=' . urlencode($row2['YIM']) . '"><img src="http://opi.yahoo.com/online?u=' . urlencode($row2['YIM']) . '&m=g&t=0" alt="' . $row2['YIM'] . '" border="0" /></a> ';
        if($row2['MSN'] != '')
          echo '<a href="http://members.msn.com/' . $row2['MSN'] . '" target="_blank"><img src="' . $settings['images_url'] . '/msntalk.gif" alt="' . $row2['MSN'] . '" border="0" /></a> ';

//Send PM row
        echo '<a href="' . $scripturl . '?action=pm;sa=send;u=' . $row2['ID_MEMBER'] . '"><img src="' . $settings['images_url'] . '/im_on.gif" alt="email" /></a></a>';
        echo '</td>';
        echo '</tr>';
      }
    echo '</table><br>';
  }
}




And here is where I am trying to pull the Division Field from the database. CP1 is the Field ID I had set the field to, and from searching and scouring through both SMF and Tinyportals boards, CP1 is what everyones been using.

Code Select Expand


mysql_free_result($query);

foreach ($rankorder as $rank)
{
//Now get all the users
  $query2 = db_query("
     SELECT ID_GROUP, ID_MEMBER, realName, avatar, usertitle,
           emailAddress, hideEmail, lastLogin, location, CP1, personaltext,
           totalTimeLoggedIn, dateRegistered, ICQ, AIM, YIM, MSN,
           hideEmail, emailAddress
    FROM {$db_prefix}members
    WHERE ID_GROUP = '" . $groups[$rank]['id'] . "' ", __FILE__, __LINE__);

  if(db_affected_rows() != 0)
  {
    echo '<table border="0" cellspacing="1" cellpadding="5" align="center" class="bordercolor">
      <tr><td colspan="7" class="catbg"><font size="4" face="verdana"><b>' . $rank . '</b></font></td></tr>
      <tr><td rowspan="70" width="85" class="profilebg" valign="top">
        <center><img src="' . $settings['images_url'] . '/' . $groups[$rank]['stars'] . '"></center></td>';
      echo '<td class="catinfo" width="270"><b>ALIAS</b></td>';
      echo '<td class="catinfo" width="130"><b>DIVISION</b></td>';
      echo '<td class="catinfo" width="175"><b>LAST ONLINE</b></td>';
      echo '<td class="catinfo" width="100"><b>LOCATION</b></td>';
      echo '<td class="catinfo" width="175"><b>CONTACT</b></td></tr><tr>';
      while ($row2 = mysql_fetch_assoc($query2))
      {
echo '<td class="windowbg" height="40"><a href="' . $scripturl . '?action=profile;u=' . $row2['ID_MEMBER'] . '"><b><font color="' . $groups[$rank]['color'] . '" size="3" face="verdana">' . $row2['realName'] . '</b>';

echo '</font></font></a><br><font size="1"> ' . $row2['personaltext'] . '</font></a></td><td class="windowbg">' . $row2['CP1'] . '</td>';
        echo '<td class="windowbg">' . timeformat($row2['lastLogin']) . '</td>';
        echo '<td class="windowbg" align="center">';       
echo '<i>' . $row2['location'] . '</i>';
        echo '</td><td class="windowbg">';


[jacortina]

Custom Profile Fields aren't stored in the 'members' table, they're stored in the 'themes' table. And even there, that 'CP1' is NOT a column name, it's part of the key value by which entries are referenced.

The 'themes' table has the columns: ID_MEMBER, ID_THEME, variable, value.

I believe that Custom Profile Fields are stored with ID_THEME = 1.

So, to pull the CP1 information for member # 123, you need to"

Code Select Expand

SELECT value FROM {db_prefix}themes
WHERE ID_MEMBER = 123
  AND ID_THEME = 1
  AND variable = "CP1"

[kaburan]

Ok so as I understand, I can only pull it for one person at a time? I'm not sure I follow you but I do understand what you mean by the fact that CP is stored in the themes table. So how could I apply the code you've provided to automatically fill the table value for each member?

You can see what I mean on my site at http://www.vx9.com/forums/index.php?page=Roster

[jacortina]

Well, you should try to avoid too many individual queries (when possible). But then, you have to work on less straightforward queries to get all of the info you want (and none you don't).

In this case, I believe you want to use what's called a "left join".

Try this in place of your $query2:

Code Select Expand

  $query2 = db_query("
     SELECT mem.ID_GROUP, mem.ID_MEMBER, mem.realName, mem.avatar, mem.usertitle,
           mem.emailAddress, mem.hideEmail, mem.lastLogin, mem.location, mem.personaltext,
           mem.totalTimeLoggedIn, mem.dateRegistered, mem.ICQ, mem.AIM,
           mem.YIM, mem.MSN, th.value as CP1
    FROM {$db_prefix}members as mem
    LEFT JOIN {db_prefix}themes as th on (mem.ID_MEMBER = th.ID_MEMBER AND th.ID_THEME = 1 AND th.variable = 'CP1')
    WHERE mem.ID_GROUP = '" . $groups[$rank]['id'] . "'", __FILE__, __LINE__);

Note that I haven't (and really can't) test this query

[kaburan]

I replaced the Query 2 with your suggested code only to end up with.

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}themes as th on (mem.ID_MEMBER = th.ID_MEMBER AND th.ID_THEME = 1 AND th.variab' at line 6"

[Innocenzia]

Is it possible to sort multiple membergroups into the same part. If so, how is it possible? a code-example would be great!

I would like it sorted like this:

Admins
[here goes Administrator & Global Moderator]

Members
[here goes Members]

Newbie
[here goes Members with the post-based level Newbie]

Inactive
[Members who hasn't logged in for the last 10 days]

[jacortina]

Hmm. It appears that "th" was a no-no.

This seems to have it working:

Code Select Expand

  $query2 = db_query("
     SELECT mem.ID_GROUP, mem.ID_MEMBER, mem.realName, mem.avatar, mem.usertitle,
           mem.emailAddress, mem.hideEmail, mem.lastLogin, mem.location, mem.personaltext,
           mem.totalTimeLoggedIn, mem.dateRegistered, mem.ICQ, mem.AIM,
           mem.YIM, mem.MSN, thm.value as CP1
    FROM {$db_prefix}members AS mem
           LEFT JOIN {$db_prefix}themes AS thm ON (thm.ID_MEMBER = mem.ID_MEMBER AND thm.ID_THEME = 1 AND thm.variable = 'CP1')
    WHERE mem.ID_GROUP = " . $groups[$rank]['id'], __FILE__, __LINE__);

[jacortina]

Is it possible to sort multiple membergroups into the same part. If so, how is it possible? a code-example would be great!

I would like it sorted like this:

Admins
[here goes Administrator & Global Moderator]

Members
[here goes Members]

Newbie
[here goes Members with the post-based level Newbie]

Inactive
[Members who hasn't logged in for the last 10 days]

This code won't do that.

You can look at the code in the "Team Page thread" which will do some of that:
http://www.tinyportal.net/index.php?topic=9207.0

But, "inactive" isn't a membergroup and are 'Members" those who aren't in any membergroup or all membegroups EXCEPT Admins and Group Moderators (and not in Post Group Newbie) or what?

[Innocenzia]

I call one of the Membergroups (everyone has a group) for Members, so it's a group on my site. So members of other groups will not show up at all. or possibly in another section called Unsorted, but i'll take a look at Team Page then. Thanks.