0

Im trying to rank players according to how many battles they won. It's a 2 player game and the winner is the player with a playerscore of 10.

In my players DB I have the following:

playerID | name
1          Lasse
2          Kevin
4          Brian

My playersfight DB:

fightID | playerID | playerScore
1          1          10
1          2          4
2          4          6
2          2          10
etc..

I can not figure out how to loop the different playerIDs through this query. Right now the playerID 2 is used, but this shall change according my player database active ids. 

$query =    "SELECT name, 
(SELECT DISTINCT count(playerScore) FROM playersfight WHERE playerScore='10' and playerID='2') As Wins, 
(SELECT DISTINCT count(playerScore) FROM playersfight WHERE playerScore<'10' and playerID='2') As Loses
FROM players WHERE playerID='2'";

$result = $conn->query($query);

The result i'm looking for should be like:

 Name  | Win | Loses
 Lasse    5      1
 Brian    3      2
 Kevin    2      2

Thank you in advance

Lasse
  • 3
  • 4
  • @Barmar: thanks for the link to the another question. I dont really get it, because im not summarize the results, and I have serveral WHERE causes, not only two. Will you help regarding my question being a duplicate? Maybe im just lost in this new world of programming. Thanks :) – Lasse Mar 07 '15 at 18:24
  • Since you're doing a per-player count, you ARE summarizing the results. `SELECT name, SUM(playerScore = 10) wins, SUM(playerScore < 10) losses FROM playersfight JOIN players USING (playerID) GROUP BY playerID`. – Barmar Mar 08 '15 at 15:46
  • Thanks a lot Barmar! Great respond - I learned three new thing regarding MySQL. – Lasse Mar 08 '15 at 16:44

0 Answers0