php/sql soting problem melting my brain.. help! (please)

I have one table ‘scores’ that consists of scores that members get in games.

Each game has an id number, 1, 2 etc (at the moment i’m only concerned with 2 games)

The structure of the scores table is as follows:

[CODE]CREATE TABLE `scores` (
`gameID` int(11) NOT NULL,
`memID` int(11) NOT NULL,
`date` date NOT NULL,
`score` int(11) NOT NULL,
PRIMARY KEY (`gameID`,`memID`,`date`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;[/CODE]

What I have been trying to do is get these details from the database and display them in a html table that is sorted by the members total score

A member’s total score is their highest score for each game added together.

I have been able to print out a league table arranged by highest score but only for each game, not with the totals combined. I’m not very experienced when it comes to dealing with arrays and loops which I think is my downfall…

I’ve tried so many approaches at this stage and all are far from elegant (and none of them work properly!)

The SQL statement I used to compile the league for the first game is was as follows:

[CODE]$sql=”SELECT username, country, gender,(YEAR(CURDATE()) – YEAR(dob))-(RIGHT(CURDATE(),5)<RIGHT(dob,5)) AS age, max( score ) as score, memberID FROM members, scores WHERE members.memberID = scores.memID and gameID = 1 GROUP BY scores.memID ORDER BY max(score) DESC LIMIT 30″;

$result=mysql_query($sql);[/CODE]

I hope someone can put me out of my misery and come up with a solution!

Thanks ?

Also in #PHP _↴