PHP/MySQL Problems with multiple select statements in one query

I'm having a problem trying to get some data out of a table.

It contains results of fixtures, and I am trying to query it to output a statistics page displaying players with the highest win ratio.

The fields are as follows:

• id (unique id for each match)
• fid (fixture id, as one fixture may have upto 13 matches)
• hpid (home player id)
• hscore (home player score)
• apid (away player id)
• ascore (away player score)

Player ids can appear in the home or away fields so i need to get the stats for both fields

So for example:

id  fid  hpid  hscore  apid  ascore  
-----------------------------------
1   1    1     1       2     0 
2   1    13    0       4     1 
3   1    5     1       6     0
4   1    7     0       8     1
5   1    9     1       10    0 
6   2    11    0       1     1 

And the query I have so far works fine for getting the info from the home players, but it does not evaluate the away players at the same time.

Heres my code:

$sql = mysql_query("SELECT hpid, SUM(hscore) AS won, COUNT(hpid) 
                      FROM tbl_matches 
                  GROUP BY hpid 
                  ORDER BY won DESC");
while ($row = mysql_fetch_array($sql)) {
  echo $row[hpid]." played: ".$row['COUNT(hpid)']." won: ".$row[won]."<br />";
}

What I want to do is use another select to workout the away stats and then add them onto the home stats.

You can try add them in the select statement by using a UNION ALL and then agregate results:

SELECT Id,SUM(Score) Score,SUM(Played) Played
from      
    (SELECT hpid Id,SUM(hscore) Score,COUNT(*) Played
    FROM tbl_matches 
    GROUP BY hpid 
    UNION ALL
    SELECT  apid,SUM(ascore),COUNT(*)
    FROM tbl_matches 
    GROUP BY apid)X
group by Id
order by Score desc

It's not quite clear what you want to do, so there are several possible answers.

First of all, if all you want is to print the away stats after the home stats, you could simply duplicate the entire code you posted except change "hscore" and "hpid" to "ascore" and "apid".

If what you actually want is to sum both home and away scores for each player, then you have several choices. The key thing is that for a particular player, you don't really care if they are the home or the away player, just that they played. So you could try this query instead:

SELECT pid, SUM(score) AS won, COUNT(pid) 
              FROM (
                  SELECT hpid AS pid, hscore AS score FROM tbl_matches 
                  UNION 
                  SELECT apid AS pid, ascore AS score FROM tbl_matches
              ) tbl_matches_union
              GROUP BY pid 
              ORDER BY won DESC