Best way to track users score each day

[v1shwa]

Hi, I'm creating a game where users play daily & earn points. I wanted to record the scores of each user each day for a month. At first, I thought to create 31 columns within the table & store the score in the appropriate date field. But, I think it is a bit messy. Is there any other alternative where I could I achieve the same thing. Any help is appreciated.

[Casperjames]

You are correct, making a table field for each day of the month is messy and is bound to cause issues. You could create a new table called user score that links to the user table and stores the user_id the date and the score for that day. Then you aren't limited to only doing scores on a per month basis with 31 columns but can add scores indefinitely to this new table.

[diafol]

Database Table:

user_id | score | scoredate

What else do you need?

You can draw out any data you want from specific queries using the WHERE clause, ORDER BY and LIMIT clauses.

e.g.

... WHERE scoredate BETWEEN ... AND ... ORDER BY score DESC LIMIT 5

Will give you the top 5 users for the specified date(s), e.g. for today or for one week or for the last month - up to you.

In order to prevent the DB becoming enormous you need to decide on whether to store each result submitted or to update existing records.

[mattster]

I see one way to do this (and someone else might have another idea):

I would create a table that logs the user, the score and the date. Then you can do lots of queries to locate what users scored on certain days, providing a very accurate reading to what they've achieved and when.

To locate what user "1" scored on the 10th October, you'd so an SQL query like:

SELECT `score` FROM `ScoresTBL` WHERE `user_id` = 1 AND `date` LIKE '2014-10-09%'

NB: Assumes date is the MySQL date format (YYYY-MM-DD HH:MM:SS)

This to me is simple enough and should be a step on the right lines, but could be fairly intensive on the database and maybe could be refined.

[v1shwa]

Looks like 3 answers are similar. Thanks all, will go for this. :)