php - How to combine several tables in a mysql query and rank based on total of a column

Solution:

I guess each id column in each of your tables is unique, that is each table has no repeated id values. That's definitely the case in your fiddle.

And, I guess no user makes repeated attempts to answer a particular question in a particular event.

Here's a pure SQL query to get what you want. (https://www.db-fiddle.com/f/kRKsYAg954msn7bebtgUpg/2)

SELECT question_attempts.event_id,
       users.id user_id, users.first_name, users.last_name,
       SUM(questions.points) points
  FROM question_attempts
  JOIN questions   ON question_attempts.question_id = questions.id
  JOIN answers     ON question_attempts.answer_id = answers.id
  JOIN users       ON question_attempts.user_id = users.id
 WHERE answers.is_correct = 1
 GROUP BY question_attempts.event_id,
         users.id, users.first_name, users.last_name

Why does this work? Your question_attempts table ties all the other tables together.

• JOIN questions ON question_attempts.question_id = questions.id pulls in the question for each answer attempt. We need that because it says how many points the question is worth.
• JOIN answers ON question_attempts.answer_id = answers.id pulls in the answer for each attempt. We need that because we need to know whether the particular answer is_correct.
• JOIN users ON question_attempts.user_id = users.id gets us the users' names.

Then, the SUM / GROUP BY adds up the points for correct answer for each event and user.

This query gets back all your events and users. You can add

WHERE ...  AND question_attempts.event_id = 6

or some such thing to get the results for just one event.

And you can use

ORDER BY question_attempts.event_id,
         SUM(questions.points) DESC,
         users.id

to get your output in descending order of score (highest first) for each event.

Solution:

This is example where triggers would come in handy. Use them to calculate Points directly into a table column. One you will have milions of rows and each time you gonna calculate this... over time it will get worst and worst.

Everytime you update answers you will update user row to update points.

Solution:

Because your problem seems to be in houw to use ranking, I created an example on your questions table, and added a RANK on the points:

It is an example how to do RANKing (actually DENSE_RANK) in MySQL 5.6. When you are using MySQL 8.0 then you should user RANK or DENSE_RANK

SET @R:=0; 
SET @P:=0;
SELECT 
   id,
   description,
   points,
   CASE WHEN @P=points THEN @R ELSE @R:=@R+1 END as `RANK`,
   @P:=points
FROM questions
ORDER BY points DESC

For output, see fiddle, or: