Leaderboard problem
Posted: Fri Jan 06, 2012 9:59 am
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
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