[Modern AAC] Searching for a Query

[Kavvson]

I have that query

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

And i want to change the script that it shows the highest skill (f.e sword) in the guild returns name and Skill level

(Modern AAC) Script

 

[qban]

$allM4 = $connection->query ('SELECT `name` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') ORDER BY `[COLOR="red"]sword[/COLOR]` DESC LIMIT 1')->fetch();

It should work, but I'm not sure...
Regards, Qban

 

[MiPo91]

This one will work, but you need to convert it to work in PHP

SELECT p.name, ps.value as Sword 
FROM players as p JOIN player_skills as ps
WHERE ps.player_id = p.id and ps.skillid = 2 AND rank_id IN (SELECT id FROM guild_ranks WHERE guild_id = 5) ORDER by Sword DESC Limit 1

I changed it to PHP(not 100% sure if this version works)

$allM4 = $connection->query ('SELECT `p`.`name`, `ps`.`value` AS `Sword` FROM `players` AS `p` JOIN `player_skills` AS `ps` WHERE `ps`.`player_id` = `p`.`id` AND `ps`.`skillid` = `2` AND `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') ORDER BY `Sword` DESC Limit 1')->fetch();

Rep++

 

[Kavvson]

Doesn't work

 

[MiPo91]

Well that one works for me(not sure about the php version), but the sql version works fine when I tested it in phpmyadmin...

 

[MiPo91]

Okey I edited this little bit and have now tested and this fully works with your Modern AAC Injection - Guild statistics V2! .

$allM5 = $connection->query ('SELECT `p`.`name` AS `Name`, `ps`.`value` AS `Sword` FROM `players` AS `p` JOIN `player_skills` AS `ps` WHERE `ps`.`player_id` = `p`.`id` AND `ps`.`skillid` = 2 AND `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') ORDER BY `Sword` DESC Limit 1')->fetch();
echo "".$allM5[0]." has sword fighting lvl ".$allM5[1]." and its biggest in our guild.";

 

[Kavvson]

Brilliant work; )

 

[Diazapam]

Now make it cache so you don't overload your mysql server

 

[Kavvson]

im not that experienced i wanted make a CASE system with all skills but i really hardly see it.. Array + case? or how? Small example please/

 

[Kavvson]

Can be?

$skills = array(
0 => "Fist Fighting",
1 => "Club Fighting",
2 => "Sword Fighting",
3 => "Axe Fighting",
4 => "Distance Fighting",
5 => "Shielding",
6 => "Fishing"
);


foreach($skills as $key => $value)
{ 
$allM5 = $connection->query ('SELECT `p`.`name` AS `Name`, `ps`.`value` AS `Sword` FROM `players` AS `p` JOIN `player_skills` AS `ps` WHERE `ps`.`player_id` = `p`.`id` AND `ps`.`skillid` = '.$key.' AND `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') ORDER BY `Sword` DESC Limit 1')->fetch();
echo "".$allM5[0]." has ".$allM5[1]." : ".$value."<br>";
}

Output .
Tester has 10 : Fist Fighting
Tester has 10 : Club Fighting
Tester has 33 : Sword Fighting
Tester has 12 : Axe Fighting
Tester has 41 : Distance Fighting
Tester has 10 : Shielding
Tester has 10 : Fishing