Hi there!
I'm new here on devnet, but i've been trying to improve my PHP from a while now.
Right now i am making a small quiz and in this quiz i'm trying to make a leaderboard.
In this quiz, users have to answer questions, and i list all the answers in a table.
When i want to see how many right answers a specific users has, i pull out it out this way:
SELECT title, answer
FROM `answers`
JOIN question USING (question_id)
JOIN user USING (user_id)
WHERE answers.answer = question.title
AND user.user_id = answers.user_id
AND user_id = $user_id
AND quiz_id = $quiz_id
I check the num_rows and then i get the exact number of right answers the user has.
This is what i need for the specific user to see when he/she logs in. Oh yeah and there's more than one quiz, that's why the $quiz_id is there.
Now what i need is a leaderboard where the user with most right answers would go to the top etc.
I figured it was easy cause i already had a request (the one above) where i could get the right answers per user.
But now i'm stuck.
First of all, there's nowhere in my database where i save the amount of correct answers (i figured that i could just pull the amount out with the SQL above everytime i need it).
If i had the correct answers saved in the database i could just select correct answers, username, and ORDER BY highest...
I don't do that, but should i?
And how do i order something like SQL does it, if it's already stored in variables?
Hope this makes sence, i'm alittle confused myself.
Lastly i want to thank you if you can help me out with this, or had the patience to read this far.
Regards,
Fyrsten
Leaderboard problem
Moderator: General Moderators
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: Leaderboard problem
If 2 questions are answered correctly, will two rows be added to the database? You could count the rows like that; or you can have a seperate table where correct answers are added up; the table could have fields : id, userId, correctAnswers. Each time a question is answered correctly correctAnswers will be increased.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Re: Leaderboard problem
As a rule I avoid using variables to store data which is related to other data or has more than one value. This is something you want to avoid:
That will be very difficult to work with. Instead if you store your data in an array (or object) it will be easier to work with. To start arrange your array in a way that makes sense. In your case you're describing users and their scores. Perhaps an array such as:
Naturally you wouldn't hard code the array. Instead you'd use mysql_fetch_array() to fill in your array.
Try searching php.net for "array." You'll get a whole list of functions to sort, merge, and manage arrays.
When you're ready for output it's very easy with foreach(). You can do something like:
Code: Select all
$firstname1 = "John";
$firstname2 = "Steve";Code: Select all
$users[0]['username'] = "jdoe";
$users[0]['highscore'] = "1000";Try searching php.net for "array." You'll get a whole list of functions to sort, merge, and manage arrays.
When you're ready for output it's very easy with foreach(). You can do something like:
Code: Select all
foreach($users as $user){
print $user['username'];
print "<br>";
}Re: Leaderboard problem
social_experiment: the table could have fields : id, userId, correctAnswers <- how would you increase it? It must not increase by more than 40 in each quiz.. Is it just a variable that does ++ every time you answer correct?
Gooney: But putting them in an array, how will i be able to order them by for instance time and date? I thought maybe SQL would be easiest with the ORDER BY function.
Gooney: But putting them in an array, how will i be able to order them by for instance time and date? I thought maybe SQL would be easiest with the ORDER BY function.
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: Leaderboard problem
You can use something like that, place the current amount of questions answered correctly in a session variable and while the value of that variable isn't 40 keep asking questions.fyrsten wrote:Is it just a variable that does ++ every time you answer correct?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering