• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!
  • If you're using Gesior 2012 or MyAAC, please review this thread for information about a serious security vulnerability and a fix.

AAC Gesior Guild Statistics Queries [TFS 1.X]

Extrodus

|| Blazera.net ||
Premium User
Joined
Dec 22, 2008
Messages
2,563
Solutions
6
Reaction score
406
Location
Canada
Hey there everyone and @Gesior.pl / @slaw - I am having some troubles with the following queries due to TFS 1.X creating "guild_memberships" table to store information rather than having rank_id/guild_nick in players table. I've tried to replace players with guild_memberships and guild_ranks with rank_id as that is how it is shown in the TFS 1.x db. But no luck - any help is greatly appreciated!

Query Section 1
Code:
        $guild_id = (int)$_GET['guild'];
        $guildMembers = $SQL->query( 'SELECT COUNT(`gr`.`id`) AS `total` FROM `players` AS `p` LEFT JOIN `guild_ranks` AS `gr` ON `gr`.`id` = `p`.`rank_id` WHERE `gr`.`guild_id` = '.$guild_id )->fetch( );
        $allM = $SQL->query ('SELECT COUNT(1) as `people` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') AND online = 1')->fetch();
        $sumav = $SQL->query ('SELECT SUM(`level`) as `level_sum`,AVG(`level`) as `level_avg` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.')  ')->fetch();
        $allM3 = $SQL->query ('SELECT `name` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') ORDER BY `level` ASC LIMIT 1')->fetch();
        $allM4 = $SQL->query ('SELECT `name` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') ORDER BY `level` DESC LIMIT 1')->fetch();
        $invite = $SQL->query( 'SELECT COUNT(`guild_id`) FROM `guild_invites` WHERE `guild_id` = '.$guild_id.'')->fetch( );    
        $vocations = array();
            foreach($SQL->query('SELECT `vocation`, COUNT(`vocation`) AS `voc_count` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = ' . $guild_id . ') GROUP BY `vocation`')  as $voc) {
        $vocations[$voc['vocation']] = $voc['voc_count'];     
            }
        $point = $SQL->query('
        SELECT
            `g`.`id` AS `id`,
            `g`.`name` AS `name`,
            SUM(`p`.`level`) AS `level`,
            COUNT(`p`.`name`) AS `count`,
            AVG(`p`.`level`) AS `average`,
            MIN(`p`.`level`) AS `min`,
            MAX(`p`.`level`) AS `max`
        FROM `players` p
            LEFT JOIN `guild_ranks` gr ON `p`.`rank_id` = `gr`.`id`
            LEFT JOIN `guilds` g ON `gr`.`guild_id` = `g`.`id`
        WHERE `guild_id` = '.$guild_id.'
    ')->fetch();

Query Section 2
Code:
$allM5 = $SQL->query('
        SELECT `p`.`name` AS `Name`,
               `ps`.`value` AS `Sword`,
               `p`.`maglevel`,
               `p`.`experience`
        FROM `players` AS `p`
        JOIN `player_skills` AS `ps`
        WHERE `ps`.`player_id` = `p`.`id`
        AND `ps`.`skillid` = ' . $key . '
        AND `rank_id`
        IN(SELECT `id` FROM `guild_ranks` WHERE `guild_id` = ' . $guild_id . ') ORDER BY `Sword` DESC Limit 1 ')->fetch();

Whole Guilds.php [Gesior 2012 TFS 1.X]
<?phpif(!defined('INITIALIZED')) exit;if($action == 'login'){ if(che - Pastebin.com (https://pastebin.com/0My3uewi) (Too long for OTLand)
 
Last edited:

Gesior.pl

Mega Noob&LOL 2012
Senator
Premium User
Joined
Sep 18, 2007
Messages
2,742
Solutions
86
Reaction score
2,730
Location
Poland
GitHub
gesior
@Extrodus
These queries execute without error. I'm not sure, if all results are fine, as in test database I have 1 guild and 1 player.
PHP:
    $guildMembers = $SQL->query( 'SELECT COUNT(1) AS `total` FROM `guild_membership` AS `gm` WHERE `gm`.`guild_id` = '.$guild_id )->fetch( );
    $allM = $SQL->query ('SELECT COUNT(1) AS `total` FROM `guild_membership` AS `gm` INNER JOIN players_online po ON po.player_id = gm.player_id WHERE `gm`.`guild_id` =  '.$guild_id)->fetch();
    $sumav = $SQL->query ('SELECT SUM(`level`) as `level_sum`, AVG(`level`) as `level_avg` FROM `guild_membership` AS `gm` INNER JOIN players p ON p.id = gm.player_id WHERE `gm`.`guild_id` =  '.$guild_id)->fetch();
    $allM3 = $SQL->query ('SELECT p.name FROM `guild_membership` AS `gm` INNER JOIN players p ON p.id = gm.player_id WHERE `gm`.`guild_id` = '.$guild_id.' ORDER BY p.level ASC LIMIT 1')->fetch();
    $allM4 = $SQL->query ('SELECT p.name FROM `guild_membership` AS `gm` INNER JOIN players p ON p.id = gm.player_id WHERE `gm`.`guild_id` = '.$guild_id.' ORDER BY p.level DESC LIMIT 1')->fetch();
    $invite = $SQL->query( 'SELECT COUNT(`guild_id`) FROM `guild_invites` WHERE `guild_id` = '.$guild_id)->fetch( );
    $vocations = array();
    foreach($SQL->query('SELECT `vocation`, COUNT(`vocation`) AS `voc_count` FROM `guild_membership` AS `gm` INNER JOIN players p ON p.id = gm.player_id WHERE `gm`.`guild_id` = '.$guild_id.' GROUP BY `vocation`')  as $voc) {
        $vocations[$voc['vocation']] = $voc['voc_count'];
    }
    $point = $SQL->query('
    SELECT
        `g`.`id` AS `id`,
        `g`.`name` AS `name`,
        SUM(`p`.`level`) AS `level`,
        COUNT(`p`.`name`) AS `count`,
        AVG(`p`.`level`) AS `average`,
        MIN(`p`.`level`) AS `min`,
        MAX(`p`.`level`) AS `max`
    FROM `guilds` g
        INNER JOIN `guild_membership` AS `gm` ON `gm`.`guild_id` = `g`.`id`
        INNER JOIN players p ON p.id = gm.player_id
    WHERE `guild_id` = '.$guild_id.'
')->fetch();
 
Top