I’m trying to figure out how to find out which player in a game db able I have wont the most games against. I have a db table in which I have 2 rows for each game. One for me and one for the opponent. When a game is finished the player who has won is awarded some +points and the loser is given -points. So as far as i can see I have to go through the table and find all the games I have got +points. But that doesn’t give me the opponents ID and count of how many I have won!
My table looks like this:
id fk_game_id fk_player_id points
So lest say I have player id 1 a won game would look like this:
1 1 1 23
2 1 2 -23
So how do I get to echo out the player I have won most game against? I have tried several of solutions with GROUP and JOINS but I got it all messed up now so I won’t even post this.
SQL’s really not my strength but perhaps something like this will work?
Find all the game_id’s you’ve played in.
Then get the SUM of the scores grouped by player_ids, unique to those games.
select SUM(score), player_id FROM games GROUP BY player_id WHERE id in (
select id FROM games WHERE player_id = 1
)
SELECT opponent.fk_player_id AS opponent_id
, SUM(playa.points) AS playa_points
FROM games AS playa
INNER
JOIN games AS opponent
ON opponent.fk_game_id = playa.fk_game_id
AND opponent.fk_player_id <> playa.fk_player_id
AND opponent.points < playa.points
WHERE playa.fk_player_id = 1 -- you are da playa
GROUP
BY opponent.fk_player_id
ORDER
BY playa_points DESC LIMIT 1
This doesn’t do the trick. It does another trick, but I can see I left out an important info in my description. I want to count how many games I have played against each player, then figure out how many of those I won (when I have +points), and then figure out which player I have won the most games against or which player I have the highest procentage wain against. Sorry for the lack of info.
EDIT:
Actually I need to do this first:
$sql = mysql_query("SELECT *
FROM ".$prefix."_gameplayer
WHERE fk_player_id = 1 AND player_points > 0");
Then find the opponent and then figure out which player I have won most times against.