Gesior, showing Total members/AVG lvl in guilds

[Linken93]

Anyone got code for showing total members and their avg lvl like this? Lunera.eu - Guilds

Thanks in advance!

 

[Sorbal]

I need this too ^_^

 

[Linken93]

bump

 

[Gabriel Tibiano]

maybe you could get the code here

 

[Linken93]

maybe you could get the code here

Yes I have tried this one out but it's showing 0

				        $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( );        
			$main_content .= '</TD><TD><TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0><center>'.$guildMembers['total'].'</center></TABLE>
			';
			$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();
			$main_content .= '</TD><TD><TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0><center>'.round($sumav['level_avg']).'</center></TABLE>
			</TD></TR>';

 

[Gabriel Tibiano]

Oh you're right, i've tested this and the whole code. The 'AVG' and others features don't work!
I dont know what we should do

 

[WibbenZ]

Did you run the SQL

You will have too rewrite them. But still.

#################################################################################
## +---------------------------------------------------------------------------
## | Database queries
## +---------------------------------------------------------------------------
#################################################################################
        $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();
###############
## Variables ##
###############
$off = $guildMembers['total'] - $allM[0];
$skills = array(0 => "Fist Fighting", 1 => "Club Fighting", 2 => "Sword Fighting", 3 => "Axe Fighting", 4 => "Distance Fighting", 5 => "Shielding", 6 => "Fishing");
$Points = $point['level'] + $point['count'] + round($point['average']) + $point['min'] + $point['max'];
#################################################################################

These needs too be added in DB incase you wanna the "full script"

WibbenZ

 

[Linken93]

I did not run these in DB, How should these lines look like for being able to run in DB?

$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( );        
        $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()

 

[Linken93]

bump

 

[Linken93]

bump

 

[Linken93]

Anyone who can help me out with this?