Originally Answered: MySQL table join (joining a column from one table to the row of another more like)?
Here's a good approach using a one to many relationship between two tables.
Keep the scores out of the User table. Just store user specific information in that table.
Save the scores in a separate table and we will then JOIN them using the unique UserID from the user table.
First here's how the tables would be.
User table with fields:
UID - (Primary Key)
Example data would look like:
1 - Bob Geldof - **** - bobg
2 - Sarah Marshall - **** - sarahm
Scores table with fields:
GID - (Primary Key)
UID - (User table primary key placeholder)
Possible - (Total number of points available for this game)
Achieved - (Score actually achieved during this game)
So as a user finishes a game the scores table will hold that data. Every time a user plays a game a new row is written to the scores table containing the UserID and the Possbile/Achieved amounts.
When you are ready to see all of the games a user has played and show averages you would just construct a JOIN query across the two tables using the UID field as the qualifier.
As an example I'll show some data and then the query to bring it back.
User1 plays four games. Writing his data to the scores table each time, the data in that table looks like this:
GID - UID - Possible - Achieved
1 - 1 - 10 - 8
2 - 1 -10 - 6
3 - 1 -10 - 8
4 - 1 - 10 - 9
(Notice the GID goes up for each game, this is an auto-increment primary key in the scores table.)
(Notice the second column of data, that's the UID (the auto-increment primary key from the users table.) This field tells you what user just played the game.
Third field contains total possible for the game, your example used 10
Fourth field contains the actual score achieved.
Here's the magic... SQL JOIN
To see the name of the player and his total possible as well as total achieved and the average too the query would look like this.
SELECT Name, SUM( Possible ) , SUM( Achieved ) , AVG( Achieved )
LEFT JOIN Scores ON Users.UID = Scores.UID
WHERE Users.UID =1 [(Users.UID 1 is Bob our first user)]
Name SUM( Possible ) SUM( Achieved ) AVG( Achieved )
Bob Geldof - 40 - 31 - 7.7500
Hope this helps, and if this doesn't help just click my profile and send me a message or email.