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.
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.
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.