php flash highscore system for a games site

Question

Hi,

I am currently working on a php/flash/mysql based high score system for a games website. The main obejctive of this system is to submit the scores in the database using flash game. Whenever the user makes the score it will be reflected in the database. While in testing evironment when i submit the scores from flash file the scores are submitting in the database.

Note: Here in this system both the registered and guest users can submit their scores.

Case 1: if the registered user submits the score then it will be submitted in to the database Provided if the user is already logged in if he's not logged in but hez a registered user and made a highest score and wants to submit the score then after this i want to submit their score in the database. Here how can i achieve this. Case 2: if the user is not a registerd user and wants to submit as a registered user then how can i achieve this.

Answer 1

I am thinking this if you got 2 seperate tables and I am sure its not a perfect method but I think it does the trick:

users: user_id, session_id, username, etc, etc scores: scores_id, user_id, score, etc, etc

add an additional column to table users called session_id When you are about to save the score, create a user row and save the session_id() into column session_id (you now got a reference to go back to for that current user).

if they register after saving the score, get the user_id based on the current session_id and update the relevent fields that they entered.

If they log in after saving the score, get the ID of the row that has their session_id() against it (for references in this comment ive called it unregistered_user_id). Get the ID of the successully authed user (for references in this comment ive called it loggedin_user_id).

Now in table scores, update scores set user_id = loggedin_user_id where user_id = unregistered_user_id. so know we know that this score is of the logged in user and that is in their history or what ever.

then delete the row from users where it was temporary: delete from users where user_id = unregistered_user_id

//optimize the tables, so everythings is indexed, etc, etc.