I'm working with a high score list based on Flash, PHP, and a flat file. The back end needs migration to MySQL from flat file. Flash will remain as the front end, and PHP/MySQL will be the new back end.
The high score list was implemented as follows:
PHP reads the flat file and sends to Flash three lists. One list for each game play level (easy, medium and hard.) Each list is sorted by score.
Currently you cannot select different time periods for the lists, such as daily, weekly, monthly ... the served lists are based on all game plays regardless of when each play occurred.
The list for each game play level is sorted by score, and contains:
1) player name
Flash displays one sorted list for each game play level (easy, medium, and hard.)
Flash sends to PHP an encrypted slug consisting of (player name, game level, score, city, and country). The Flash code generates game level and score. The user provides player name, city, and country.
PHP decrypts the slug, adds ip and timestamp, and appends a new record to the flat file.
I need to:
a) Replace the flat file with MySQL
b) Create top score lists for daily, weekly, and monthly plays at each game play level
c) Automatically determine the player's "country" from his ip
Can you help with the following:
1) MySQL table(s) design
2) SQL queries design for daily, weekly and monthly top score lists for each game play level
3) Automatically extracting "country" from ip.