如何将循环中的这两个查询转换为单个JOINed查询？

Question

I am currently trying to get data from my table (mostKills by Weapon in a table with over 300 kills). Initially I did a normal query

$q = $mysql->query("SELECT * FROM `kills`") or die($mysql->error);

but when I tried to

            $query2 = $mysql->query("SELECT `killerID`, COUNT(`killerID`) AS tot_kills FROM `kills` WHERE `killText` LIKE '%$gun%' GROUP BY `killerID` ORDER BY `tot_kills` DESC;") or die($mysql->error);
            $kData = $query2->fetch_assoc();

            $query3 = $mysql->query("SELECT `Username` FROM `players` WHERE `ID` = '" . $kData['killerID'] . "'") or die($mysql->error);
            $uData = $query3->fetch_assoc();

            $array[$gun]['Kills']++;
            $array[$gun]['Gun'] = $gun;
            $array[$gun]['BestKiller'] = $uData['Username'];
            $array[$gun]['killAmount'] = $kData['tot_kills'];

            function sortByKills($a, $b) {
                return  $b['Kills'] - $a['Kills'];
            }

            usort($array, 'sortByKills');

            foreach($array as $i => $value) 
            {
                // table here
            }

I had to do it in a while loop, which caused there to be around 600 queries, and that is obviously not acceptable. Do you have any tips on how I can optimize this, or even turn this into a single query?

I heared JOIN is good for this, but I don't know much about it, and was wondering if you guys could help me

Answer 1

Try this...

I added a inner join and added a username to your select clause. The MIN() is just a way to include the username column in the select and will not have an impact on you result as long as you have just 1 username for every Killerid

SELECT `killerID`
    , COUNT(`killerID`) AS tot_kills
    , MIN(`Username`) AS username
FROM `kills`
INNER JOIN `players`
    ON `players`.`id` = `kills`.`killerid`
WHERE `killText` LIKE '%$gun%'
GROUP BY `killerID`
ORDER BY `tot_kills` DESC