source: guildy.php@ 140

<?
  include('global.php');
  $Database->select_db('characters');
  ShowHeader();
  

$RanksEN = array('Guildmaster', 'Veteran', 'Intendant', 'Support Officer', 'PR Officer', 'Officer', 'Famed Member', 
'Member', 'Rokie', 'Newbie');
$RanksCZ = array('Pán cechu', 'Veterán', 'Vrchní správce', 'Pomocný dùstojník', 'PR dùstojník', 'Dùstojník', 'Proslavený èlen', 'Èlen', 'Zelenáè', 'Nováèek');

$order_by = (isset($_GET['order_by'])) ? $Database->real_escape_string($_GET['order_by']) : "name";

$dir = (isset($_GET['dir'])) ? $Database->real_escape_string($_GET['dir']) : 1;
$order_dir = ($dir) ? "ASC" : "DESC";
$dir = ($dir) ? 0 : 1;
        
function HeaderText($Text, $Column)
{
  global $dir, $order_by, $GuildId;
  echo('<th><a href="?order_by='.$Column.'&amp;dir='.$dir.'">'.($order_by==$Column ? '<img src="images/online_player/arr_'.($dir ? 'up' : 'dw').'.gif" /> ' : '').$Text.'</a></th>');
}
        
function HeaderTextGuild($Text, $Column)
{
  global $dir, $order_by, $GuildId;
  echo('<th><a href="?guild='.$GuildId.'&amp;order_by='.$Column.'&amp;dir='.$dir.'">'.($order_by==$Column ? '<img src="images/online_player/arr_'.($dir ? 'up' : 'dw').'.gif" /> ' : '').$Text.'</a></th>');
}

$Database->select_db('mangos');
if(!array_key_exists('guild', $_GET))
{
  echo('<h3>Seznam guild:</h3><div align="center">');
  echo('<table class="TableGuilda" border="1" cellpadding="3" cellspacing="0"><tr>');
  HeaderText('Název', 'name');
  HeaderText('Poèet èlenù', 'member_count');
  HeaderText('Souèet levelù', 'guild_level');
  HeaderText('Souèet bodù cti', 'guild_honor');
  HeaderText('Webové stránky', 'name');    
  echo('</tr>');
//  DB_Query(' COUNT(guild_member.guildid) as member_count FROM guild JOIN guild_member ON guild_member.guildid = guild.guildid GROUP BY guild.name ORDER BY member_count DESC');
  $Result = $Database->query('SELECT `guild`.name, guild.guildid, COUNT(`guild_member`.`guildid`) as member_count, SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(`character`.`data`, " ", 35), " ", -1)) as guild_level, SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(`character`.`data`, " ", 1421), " ", -1)) as guild_honor FROM `guild` JOIN `guild_member` ON `guild_member`.`guildid` = `guild`.`guildid` JOIN `character` ON `character`.`guid`= `guild_member`.`guid` GROUP BY guild.name ORDER BY '.$order_by.' '.$order_dir);
  while($Guild = $Result->fetch_array())
  {
    echo('<tr><td><a href="?guild='.$Guild['guildid'].'">'.$Guild['name'].'</a></td><td>'.$Guild['member_count'].'</td><td>'.$Guild['guild_level'].'</td><td>'.$Guild['guild_honor'].'</td><td>&nbsp;</td></tr>');
  }
  echo('</table></div>');
} else {
  $GuildId = addslashes($_GET['guild']);
  $Result = $Database->query('SELECT `guild`.name, guild.guildid, guild.leaderguid, COUNT(`guild_member`.`guildid`) as member_count, SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(`character`.`data`, " ", 35), " ", -1)) as guild_level, SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(`character`.`data`, " ", 1421), " ", -1)) as guild_honor FROM `guild` JOIN `guild_member` ON `guild_member`.`guildid` = `guild`.`guildid` JOIN `character` ON `character`.`guid`= `guild_member`.`guid` WHERE guild.guildid='.$GuildId.' GROUP BY guild.name');
  if($Result->num_rows > 0)
  {
    $Guild = $Result->fetch_array();
    //  if($Guild['homepage'] != '') $Guild['name'] = $Guild['name'].' <a href="'.$Guild['homepage'].'">WWW</a>';
    //if($Guild['info'] != '') $Guild['info'] = ''.$Guild['info'].'</i>';
    echo('<h3>Seznam èlenù guildy '.$Guild['name'].'</h3><div align="center">');
    $Result = $Database->select('character', 'name', $Guild['leaderguid'].'=guid');
    $GuildMaster = $Result->fetch_array();
    echo('Vedoucí guildy: '.$GuildMaster['name'].'<br>');
    echo('<table class="TableGuilda" border="1" cellpadding="1" cellspacing="0">');
    echo('<tr>');
    HeaderTextGuild('Jméno', 'name');
    HeaderTextGuild('Úroveò', 'level');
    HeaderTextGuild('Hodnost', 'rank');
    HeaderTextGuild('Body cti', 'honor');    
    echo('</tr>');
    //echo('<tr><td colspan="2" Class="TdGuilda">Informace: '.$Guild['public_info'].'</td></tr>');
   
    $GuildRanks = array();
    $Result = $Database->select('guild_rank', '*', 'guildid='.$Guild['guildid']);
    while($GuildRank = $Result->fetch_array()) $GuildRanks[$GuildRank['rid']] = $GuildRank;
  
    $Result = $Database->query('SELECT guild_member.rank, guild_member.guid, SUBSTRING_INDEX(SUBSTRING_INDEX(character.data," ", 35), " ", -1)*1 as level, SUBSTRING_INDEX(SUBSTRING_INDEX(character.data," ", 1421), " ", -1)*1 as honor, character.name FROM guild_member JOIN `character` ON guild_member.guid = character.guid AND guild_member.guildid='.$Guild['guildid'].' ORDER BY '.$order_by.' '.$order_dir);
    while($Member = $Result->fetch_array())
    {
      if(array_key_exists($Member['rank']+1, $GuildRanks)) $GuildRank = $GuildRanks[$Member['rank']+1]['rname']; else $GuildRank = '&nbsp;';
//    $DataParts = explode(' ', $Member['data']);
//    $Member['level'] .= ','.$DataParts[34];
      echo('<tr><td width="30%">'.$Member['name'].'</td><td>'.$Member['level'].'</td><td>'.$GuildRank.'</td><td>'.$Member['honor'].'</td></tr>');
    }
    echo('</table>');
    echo('<a href="guildy.php">Zpìt na seznam guild</a></div>');
  } else echo('Guilda nenalezena!');
}


ShowFooter();
?>