Upcomming new Guild Statistic for Gesior ^.^

[Kavvson]

​

Comment.

 

[Wezza]

Amazing.

 

[Aleh]

15 minutes work but nice idea ;p

 

[Kavvson]

Well with mine skill bit longer But nice. I might add sth new and release it tomorrow / today.

Looking forward for query optimalizations

$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(); 
		$allM1 = $SQL->query ('SELECT SUM(`level`) as `level` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') ')->fetch(); 
		$allM2 = $SQL->query ('SELECT AVG(`level`) as `level` 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(*) FROM `guild_invites` WHERE `guild_id` = '.$guild_id.'')->fetch( );     
		$s = $SQL->query('SELECT COUNT(`vocation`) FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') AND vocation = 1')->fetch();       
		$d = $SQL->query('SELECT COUNT(`vocation`) FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') AND vocation = 2')->fetch();
		$p = $SQL->query('SELECT COUNT(`vocation`) FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') AND vocation = 3')->fetch();    
		$k = $SQL->query('SELECT COUNT(`vocation`) FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') AND vocation = 4')->fetch();     
		$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();

 

[Kavvson]

Thanks to Gesior the querys has been improved. Tomorrow i release it

 

[Kavvson]

http://otland.net/f118/gesior-acc-guild-statistics-new-version-138315/