Query fix - most powerfull guild - frags highscore

[kito2]

This is the query:

SELECT `g`.`id` AS `id`, `g`.`name` AS `name`,
	`g`.`logo_gfx_name` AS `logo`, COUNT(`g`.`name`) as `frags`
FROM `killers` k
	LEFT JOIN `player_killers` pk ON `k`.`id` = `pk`.`kill_id`
	LEFT JOIN `players` p ON `pk`.`player_id` = `p`.`id`
	LEFT JOIN `guild_ranks` gr ON `p`.`rank_id` = `gr`.`id`
	LEFT JOIN `guilds` g ON `gr`.`guild_id` = `g`.`id`
WHERE `k`.`unjustified` = 1 AND `k`.`final_hit` = 1
	GROUP BY `name`
	ORDER BY `frags` DESC, `name` ASC
	LIMIT 0, 4;

It shows any frag, I would like to have frags from level 150+ or higher...

SELECT players.id,players.name,players.online,players.level,players.vocation,players.promotion,player_killers.player_id,COUNT(player_killers.player_id) count FROM players,player_killers WHERE player_killers.player_id = players.id AND players.deleted = 0'.$vocs.' AND players.group_id < '.$config['site']['players_group_id_block'].' AND players.name != "Account Manager" AND players.name != "Rook Sample" AND players.name != "Sorcerer Sample" AND players.name != "Druid Sample" AND players.name != "Paladin Sample" AND players.name != "Knight Sample" GROUP BY player_killers.player_id ORDER BY COUNT(player_killers.player_id) DESC LIMIT 101 OFFSET '.$offset);
	}

Same here...

Repping!

 

[kito2]

Bump!

 

[kito2]

BUMP again!

 

[kito2]

Bump!

 

[kito2]

Bump!

 

[Chris]

Try this.

SELECT
    p.id, p.name, p.online, p.level, p.vocation, p.promotion, pk.player_id, COUNT(pk.player_id) AS count
FROM
    players AS p, player_killers AS pk
LEFT JOIN
    players AS v ON v.id = pk.player_id
WHERE
    p.group_id < '.$config['site']['players_group_id_block'].' AND
    p.name NOT IN('Account Manager', 'Rook Sample', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample') AND
    v.level >= 150
GROUP BY
    pk.player_id
ORDER BY
    COUNT(pk.player_id) DESC LIMIT 101 OFFSET '.$offset.';

 

[kito2]

@Chris

Tried it but getting:
Parse error: syntax error, unexpected T_STRING in /var/www/highscores.php on line 83

Trying to replace here:

elseif($list == "frags")
{
$list_name = 'Frags';
$skills = $SQL->query('SELECT players.id,players.name,players.online,players.level,players.vocation,players.promotion,player_killers.player_id,COUNT(player_killers.player_id) count FROM players,player_killers WHERE player_killers.player_id = players.id AND players.deleted = 0'.$vocs.' AND players.group_id < '.$config['site']['players_group_id_block'].' AND players.name != "Account Manager" AND players.name != "Rook Sample" AND players.name != "Sorcerer Sample" AND players.name != "Druid Sample" AND players.name != "Paladin Sample" AND players.name != "Knight Sample" GROUP BY player_killers.player_id ORDER BY COUNT(player_killers.player_id) DESC LIMIT 101 OFFSET '.$offset);
}

 

[Chris]

elseif($list == "frags") 
	{
		$list_name = 'Frags';
		$skills = $SQL->query('SELECT
    p.id, p.name, p.online, p.level, p.vocation, p.promotion, pk.player_id, COUNT(pk.player_id) AS count
FROM
    players AS p, player_killers AS pk
LEFT JOIN
    players AS v ON v.id = pk.player_id
WHERE
    p.group_id < '.$config['site']['players_group_id_block'].' AND
    p.name NOT IN(\'Account Manager\', \'Rook Sample\', \'Sorcerer Sample\', \'Druid Sample\', \'Paladin Sample\', \'Knight Sample\') AND
    v.level >= 150
GROUP BY
    pk.player_id
ORDER BY
    COUNT(pk.player_id) DESC LIMIT 101 OFFSET '.$offset.';');
	}

 

[kito2]

Tanks it works!

 

[Chris]

You're welcome.

 

[munch]

So this would count frags only from players killed with level greater or equal to 150?

 

[Chris]

That's right.

 

[munch]

Not bad, I can see why.

 

[kito2]

mtibia - Highscores

------
Rank Name
Frags
1. Daoos Druid
94 Elder Druid
2
2. Daoos Druid
94 Elder Druid
2
3. Daoos Druid
94 Elder Druid
2
4. Daoos Druid
94 Elder Druid
2
5. Daoos Druid
94 Elder Druid
2
6. Daoos Druid
94 Elder Druid
2
7. Daoos Druid
94 Elder Druid
2