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]

Bumping this please!

 

[Ennokas]

U can "bump" 1x in 24 h

 

[kito2]

Bump.

 

[kito2]

Bump again...

 

[kito2]

Bump!

 

[Colandus]

try

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`AND `p`.`level` >= 150
    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;

 

[Cykotitan]

fail

 

[Colandus]

me ??

 

[Cykotitan]

Ye

 

[Colandus]

ok lol i was just guessing, but why wouldn't it work? ^.^

 

[kito2]

Thanks Colandus, repped.

I still need this one:

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)

 

[Colandus]

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.level >= 150 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)

and Cyko, you is fail not me!

 

[kito2]

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Array' in 'where clause'' in /var/www/highscores.php:75 Stack trace: #0 /var/www/highscores.php(75): PDO->query('SELECT players....') #1 /var/www/index.php(336): include('/var/www/highsc...') #2 {main} thrown in /var/www/highscores.php on line 75

 

[Colandus]

wops, copy it again

 

[kito2]

That search frags of players with level 150+, I need that victims level should be 150+.

 

[Colandus]

ahaa okay...... i'll try soon

 

[Demian]

=o!

 

[kito2]

Thanks.