Page 1 of 1

Need help at php+mysql highscores

Posted: Thu Sep 02, 2010 10:50 am
by tonchily
I can code PHP, but I actually don't have an idea on how to do the following idea;

I want to make a website where users can, after they register and login, guess the outcome of football matches, for example the match result, and after the match is over and I update the matchresult in the mysql (thats how I imagined it to be - in the mysql), I want to make it possible that all those users who guessed the match outcome right to get the certain amount of points, for example 10 points for a correct match result and I want to make a highscore table upon those points they get.
I actually don't have an idea on how to do it.

Thanks for your help

Re: Need help at php+mysql highscores

Posted: Thu Sep 02, 2010 1:31 pm
by infolock
You'll need a few things to make it happen...


1) a table to hold your guesses per user_id (probably something like user_score_submission with a primary key, user_id, score, and a game_id), a game table (game_id is pk, game name, game_start_timestamp, game_end_timestamp, game_final_home_score, game_final_visitor_score)

2) query the game to check the final score when it ends, and compare with the user_score_sumission data that has records of a given game_id that equals the score you want.

3) if you find a match, add points to the user table (probably have a field for user_points or something).

would be how i'd do it if i want something simple. more elegant solutions exists but that should get you on your way.

Re: Need help at php+mysql highscores

Posted: Thu Sep 02, 2010 5:07 pm
by tonchily
infolock wrote:You'll need a few things to make it happen...


1) a table to hold your guesses per user_id (probably something like user_score_submission with a primary key, user_id, score, and a game_id), a game table (game_id is pk, game name, game_start_timestamp, game_end_timestamp, game_final_home_score, game_final_visitor_score)
I don't think I would really need a game name, game_start_timestamp and game_end_timestamp fields in my game_table. Think game_id, game_home_goals, game_away_goals would do good.
infolock wrote: 2) query the game to check the final score when it ends, and compare with the user_score_sumission data that has records of a given game_id that equals the score you want.
Yeah, that'll do.
infolock wrote: 3) if you find a match, add points to the user table (probably have a field for user_points or something).

would be how i'd do it if i want something simple. more elegant solutions exists but that should get you on your way.
So, basically, the highscore function/script/php page has to check on every load for every user game he tried to guess, and give the amount of points he "deserves"? Wouldn't that be too much for the server to handle? Because I'd like to have a highscore list on my index page, so basically it'll load for every user that visits my site.



Thanks for your help so far infolock, it really got me on my way.

Re: Need help at php+mysql highscores

Posted: Fri Sep 03, 2010 9:44 am
by tonchily
Bump.

Cmon, I really need a hand on this.

Re: Need help at php+mysql highscores

Posted: Fri Sep 03, 2010 3:10 pm
by Jonah Bron
tonchily wrote:So, basically, the highscore function/script/php page has to check on every load for every user game he tried to guess, and give the amount of points he "deserves"? Wouldn't that be too much for the server to handle? Because I'd like to have a highscore list on my index page, so basically it'll load for every user that visits my site.
No. You would add a column to the users table that holds their score. Whenever they guess something right, increment that column by the number of points they earned.

A side note: you can't just list their score by the points they got: you need to integrate the number of wrong guesses into the score. Perhaps let them choose the points? Like say, a user selects 100 points. If he's right, he get's 100 points; otherwise he loses 100. This would mean that a person could have a negative score.

Re: Need help at php+mysql highscores

Posted: Fri Sep 03, 2010 4:36 pm
by tonchily
Jonah Bron wrote:
tonchily wrote:So, basically, the highscore function/script/php page has to check on every load for every user game he tried to guess, and give the amount of points he "deserves"? Wouldn't that be too much for the server to handle? Because I'd like to have a highscore list on my index page, so basically it'll load for every user that visits my site.
No. You would add a column to the users table that holds their score. Whenever they guess something right, increment that column by the number of points they earned.

A side note: you can't just list their score by the points they got: you need to integrate the number of wrong guesses into the score. Perhaps let them choose the points? Like say, a user selects 100 points. If he's right, he get's 100 points; otherwise he loses 100. This would mean that a person could have a negative score.
Yeah, I got it that I must add a field in users table that holds their score(points), lets name it user_points . No, I don't want user to have negative points. I want to give user options to choose the outcome of the game as in home win/draw/visitor win, or try to guess the correct result, without taking away any points in case he misses. So, I know how to create a script that will upload the score to games table by the gameid etc (actually I can do it myself anyway), but I need a help at updating users points. I don't have an idea on how to it - how to compare what-the-user-entered with the real match outcome and after that to update his score.

Can you help at that?

Re: Need help at php+mysql highscores

Posted: Fri Sep 03, 2010 5:06 pm
by Jonah Bron
When you submit the actual results of the game, run a script that compares all guesses for that game to the actual results of that game, and updates that user's points if he's correct.

I'm no SQL pro, but I'll wager that somebody here could do that in one SQL command.

Re: Need help at php+mysql highscores

Posted: Fri Sep 03, 2010 5:15 pm
by Benjamin
What's the schema?

Re: Need help at php+mysql highscores

Posted: Fri Sep 03, 2010 5:51 pm
by tonchily
Jonah Bron wrote:When you submit the actual results of the game, run a script that compares all guesses for that game to the actual results of that game, and updates that user's points if he's correct.

I'm no SQL pro, but I'll wager that somebody here could do that in one SQL command.
Uhm, right. Guess that would do the trick.

Re: Need help at php+mysql highscores

Posted: Fri Sep 03, 2010 6:14 pm
by Jonah Bron
tonchily wrote:Uhm, right. Guess that would do the trick.
I know that you know that that is what you want already, I was just putting it up as an opportunity for somebody that's better at SQL to do it. I put it in the challenge index
Benjamin wrote:What's the schema?
@tonchilly list all your tables and all columns in those tables.

Re: Need help at php+mysql highscores

Posted: Fri Sep 03, 2010 6:47 pm
by tonchily
Jonah Bron wrote:
tonchily wrote:Uhm, right. Guess that would do the trick.
I know that you know that that is what you want already, I was just putting it up as an opportunity for somebody that's better at SQL to do it. I put it in the challenge index
Thanks.

Jonah Bron wrote: @tonchilly list all your tables and all columns in those tables.
I have a table called users with fields id, username, password, email, points
I have a table called match with fields matchid, hometeamname, visitorteamname, hometeamgoals, visitorteamgoals
I have a table called match_guesses with fields userid, matchid, hometeamgoals, visitorteamgoals

Not sure if more tables/fields are needed in any of those? Feel free to add if needed.