Roster Page

[jacortina]

Yes, you'll need another join. Use the code you show, but try changing the query to:

Code Select Expand

    SELECT mem.ID_GROUP, mem.ID_MEMBER, mem.realName, 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 sail, thm2.value as country
   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 = 'sail')
          LEFT JOIN {$db_prefix}themes AS thm2 ON (thm.ID_MEMBER = mem.ID_MEMBER AND thm.ID_THEME = 1 AND thm.variable = 'country')
       WHERE mem.ID_GROUP = " . $groups[$rank]['id'] . "
   ORDER BY mem.realName", __FILE__, __LINE__);

[TimUSA]

showing as a blank column

[jacortina]

Well, could be (hope it is) my messing up that second JOIN line. All the "thm"s should be "thm2"s in the second JOIN line:

Code Select Expand

          LEFT JOIN {$db_prefix}themes AS thm2 ON (thm2.ID_MEMBER = mem.ID_MEMBER AND thm2.ID_THEME = 1 AND thm2.variable = 'country')


[TimUSA]

ok that is working, but not with the flags.

when i put this line in:

Code Select Expand

        echo '</font></font></a><br><font size="2"> ' . '<i>' . $row2['country'] . '</i>'

i get the country letters

when i put this code in:

Code Select Expand

        echo '</font></font></a><br><font size="2"> ' . '<img src="'.$settings['default_images_url'].'/flags/'.$row['country'].'.png" />';

I still get a blank column

It appears we have the query part solved, now we just need to pull in the image. I could live with out it, but what fun would that be!

Thanks again

[jacortina]

Could you try to display the page source of the result and find that image URL and see what it says (maybe too many slashes, maybe $settings['default_images_url'] isn't set or has a value I'm not expecting).

[TimUSA]

ok i dont know if this helps us, but this is how it is brought in on the profile.template.php

Code Select Expand

</tr><tr>
<td><b>', $txt['country_flag'], ':</b></td>
<td>';

// Did they select a flag?
if(isset($context['member']['options']['country']) && !empty($context['member']['options']['country']))
{
$flags = CountryFlag();
echo
$flags[$context['member']['options']['country']] . ' <img src="', $settings['default_theme_url'], '/images/flags/', $context['member']['options']['country'], '.png" alt="Country" />';
}

echo '
</td>

[jacortina]

Then you can try to use their form (except that I don't believe you can count on the those $context members being present unless/until the Profile page is loaded; so you'd still want to use the value from the dtabase which we DO know you're getting):

Code Select Expand

'<img src="', $settings['default_theme_url'], '/images/flags/', $row2['country'], '.png" alt="Country" />'

-----

Oh, Damn! Before that, you could try simply putting the right array name in (instead of the mistaken one I supplied). It SHOULD be "$row2 " and not "$row":

Code Select Expand

        echo '</font></font></a><br><font size="2"> ' . '<img src="'.$settings['default_images_url'].'/flags/'.$row2['country'].'.png" />';

[TimUSA]

changing to row2 worked...YOUR A HERO!!!!!!!!

Here is the code for anyone else to use. Just change the field names as needed:

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(
  'Administrator',
  'Vice Commodore',
  'Fleet Captain',
  'Board Member',
  'Racing Fleet Member',
);

$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'],
  );
}
mysql_free_result($query);

foreach ($rankorder as $rank)
{
//Now get all the users
   $query2 = db_query("
    SELECT mem.ID_GROUP, mem.ID_MEMBER, mem.realName, 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 sail, thm2.value as country
   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 = 'sail')
          LEFT JOIN {$db_prefix}themes AS thm2 ON (thm2.ID_MEMBER = mem.ID_MEMBER AND thm2.ID_THEME = 1 AND thm2.variable = 'country')
       WHERE mem.ID_GROUP = " . $groups[$rank]['id'] . "
   ORDER BY mem.realName", __FILE__, __LINE__);
  if(db_affected_rows() != 0)
  {
    echo '<table border="0" cellspacing="1" cellpadding="1" align="center" class="bordercolor">
      <tr><td colspan="0" class="catbg"><font size="2" face="verdana"><b>' . $rank . '</b></font></td></tr>
      <tr><td rowspan="100" width="10" class="profilebg" valign="top">
</td>';
      echo '<td class="catinfo" width="270"><b>MEMBER NAME</b></td>';
      echo '<td class="catinfo" width="130"><b>SAIL NUMBER</b></td>';
      echo '<td class="catinfo" width="170"><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="20"><a href="' . $scripturl . '?action=profile;u=' . $row2['ID_MEMBER'] . '"><b><font color="' . $groups[$rank]['color'] . '" size="2" face="verdana">' . $row2['realName'] . '</b>';
echo '</font></font></a><br><font size="1"> ' . $row2['personaltext'] . '</font></a></td><td class="windowbg">' . $row2['sail'] . '</td>';
        echo '<td class="windowbg">' . timeformat($row2['lastLogin']) . '</td>';
        echo '<td class="windowbg2" align="center">';
        echo '</font></font></a><br><font size="2"> ' . '<img src="'.$settings['default_images_url'].'/flags/'.$row2['country'].'.png" />';
        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>';
  }
}

[Hairy]

This is an excellent addition to my roster page, thank you very much.

One addition I think would be nice is to have the Country name displayed when the mouse hovers above (alt= *code to get full Country name*) but I am not sure how to pull that from the database.

Again, very cool!

Cheers

[jacortina]

I don't know where (or even IF) the full country name is stored for all countries with flags. We get the file name for the flag, but that's it; and the file name for the flag of the United States is named 'us' (lower case). Sure, that name could be put in the alt tag (you could even do a blanket strtoupper() to make everything uppercase), but without having the data of full names, we can't provide the full names.