PHP Some PHP Expert to Help me of This command...

[Engradiel]

SELECT `g`.`id` AS `id`, `g`.`name` AS `name`, COUNT(`g`.`name`) as `frags` FROM `players` p LEFT JOIN `player_deaths` pd ON `pd`.`killed_by` = `p`.`name` LEFT JOIN `guild_membership` gm ON `p`.`id` = `gm`.`player_id` LEFT JOIN `guilds` g ON `gm`.`guild_id` = `g`.`id` WHERE `pd`.`unjustified` = 1 GROUP BY `name` ORDER BY `frags` DESC, `name` ASC LIMIT 0, 4

I want someone to help me to create a command to count all level of players of guild and make a ranking:

Like:
Most Level Guild Ranking 1: 23032 level, 2: 20032 level, 3: 15032 level

Like this:



I am using this command but it's a shity slowly and badly:

$vet = 0;
$list = $SQL->query('SELECT * FROM guilds');
foreach($list as $result) {
    $members = $SQL->query('SELECT * FROM guild_membership where guild_id = '.$result[id].'');
    $force = 0;
    foreach($members as $result2) {
            if (!$result2 or !$result2[player_id] or $result2 == nil or $result2 == null or $result2 == "" or $result2[player_id] <= 0) {
                continue;
            }
                $player = new Player();
                $player->loadById($result2[player_id]);
                
                if (!$player or !$player->isLoaded()) {
                    continue;
                }
            
                if($player->isLoaded()) {
                    //if ($player->getAccountID() == 2) {
                        //$force = 0;
                    //} else {
                        $force += $player->getLevel();
                    //}
                }
                
            
    }
    $lista[$vet] = ''.$force.'-'.$result[name].'-' . $result['id'] . '';
    $vet++;
}


arsort($lista, SORT_NUMERIC);
$vet = 0;
foreach ($lista as $result) {
    $pieces = explode("-", $result);
    
    
    
$main_content .= '<td style="text-align: center;">
<a href="?subtopic=guilds&action=view&GuildName='.$pieces[1].'">
<img src="guild_image.php?id='.$pieces[2].'" width="64" height="64" border="0"/>
<br />'.$pieces[1].'</a><br />'.$pieces[0].'</td>';
                                
    $vet++;
    if ($vet >= 5) {
        break;
    }
}

 

[Roddet]

$query = $SQL->query('SELECT `g`.`id`, `g`.`name`, SUM(`p`.`level`) AS `level`
    FROM `players` p, `guilds` g, `guild_ranks` gr
    WHERE `p`.`rank_id` != 0 AND `p`.`rank_id` = `gr`.`id` AND `g`.`id` = `gr`.`guild_id`
    GROUP BY `g`.`id` ORDER BY `level` DESC LIMIT 5;
');

foreach($query as $i => $guild) {
    echo $guild['name'] . ' - level -> ' . $guild['level'] .'<br>';
}

 

[Engradiel]

I am using tfs 1.3 bro. this not work Coluna 'p.rank_id' desconhecida em 'where clause'

 

[Roddet]

Use this query instead

SELECT `g`.`id`, `g`.`name`, SUM(`p`.`level`) AS `level`
FROM `players` p, `guilds` g, `guild_membership` gm
WHERE `p`.`id` = `gm`.`player_id` AND `g`.`id` = `gm`.`guild_id`
GROUP BY `g`.`id` ORDER BY `level` DESC LIMIT 5;

 

[Engradiel]

Works fine bro! A lot of thanks (I am waiting for this along 5 years..)

 

[Engradiel]

Bro one last help.. .



Can you help me with the command to show a ranking with count of members of the guild?

 

[Roddet]

SELECT `g`.`id`, `g`.`name`, COUNT(`p`.`id`) AS `members`
FROM `players` p, `guilds` g, `guild_membership` gm
WHERE `p`.`id` = `gm`.`player_id` AND `g`.`id` = `gm`.`guild_id`
GROUP BY `g`.`id` ORDER BY `members` DESC LIMIT 5;

 

[Engradiel]

Friend, since you seem like a beast, I want to abuse your knowledge



I use this command to check count of items in player_items, player_inboxitems and player_depotitems.

But it doesn't work well for counting stackable items like money or stamina refills for example. Is it possible to create something that count the itemtype + count of items?