Sql Query

[Calon]

Hello im using gesior aac, so im trying to exchange this top guilds query from calculate and count the members frags to count the members storage value for example 1111.

 $guilds = $SQL->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, 10;');

 

[Cronox]

Wanna example better? i cant' understand you

 

[WibbenZ]

Are you using the newer gesior? Gesior has made a new script that workes, since elf's script is using the old queries.
WibbenZ

 

[Calon]

Wanna example better? i cant' understand you

an example:

if there is a 3 guilds in my ot,

1-the first guild got 5 members, each member have storage 1111 value = 2, so there is 5 members so the total of the guild members of storage 1111 values (5 members * 2 value = 10 total) then the total=10

2-the second guild members got total of storage 1111 value total = 8

3-the third guild members got total of storage 1111 value total = 5


so it shown in page like

TOP 3 guilds wins the event

1- first guild with total wins = 10
2- second guild total wins = 8
3- third guild total wins = 5

------------------

so here in this query an example for highscore but for the guild members frags, i want a help to exchange it to highscore by storage value 1111

$guilds = $SQL->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, 10;');

 

[Cronox]

$guilds = $SQL->query('SELECT COUNT(`value`) AS val
	FROM `player_storage`     
	WHERE `key`= 1111
	LIMIT 1;')->fetch();

$guilds2 = $SQL->query('SELECT COUNT(`value`) AS val
	FROM `player_storage`     
	WHERE `key`= 1112
	LIMIT 1;')->fetch();

-.^?

 

[Calon]

No :/

another example:

here in picture count every member frags (xxxx kills)



i want to make the same but count every member storage 1111 value same ^

:$

 

[Cronox]

You wanna edit
kills for number members guild storages 1111 ?

 

[Calon]

yessss

 

[Cronox]

look this and say me what error get you
made by u have 1 error.. try
[

guilds = $SQL->query('SELECT `g`.`id` AS `id`, `g`.`name` AS `name`, 
       `g`.`logo_gfx_name` AS `logo`, COUNT(`gr`.`id`) as `total` FROM `players` AS `p` 
       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` 
       FROM `player_storage`     
       WHERE `key`= 1111 
       GROUP BY `name` 
       ORDER BY `total` DESC, `name` ASC 
       LIMIT 1, 10;');

 

[Calon]

1. 7 7 kills
2. 7 7 kills

-.^

 

[WibbenZ]

Are you using the newer gesior? Gesior has made a new script that workes, since elf's script is using the old queries.
WibbenZ

What gesior are you using.. ?

 

[Calon]

newest gesior 2012

 

[Cronox]

yes chriso 2. 7.7 ,etc
just i need loking picture for see where i need added number player guilds

 

[Calon]

- - - Updated - - -

bump