Jump to content

Scoreboard Output from MySQL Help.


johnslater

Recommended Posts

I have a question, something i SHOULD be able to do but i seem to be having a lapse in capabilities right now.

 

Basically i have a MySQL database with a number of tables. One table called 'Users' and one called 'Scores'. Basically Scores are linked to Users by a UserID because a user may have multiple scores for different games and levels.

 

Basically i want to output an overall scoreboard which adds up all the scores for each users and then outputs them in a big scoreboard.

 

So for example

 

USER - SCORE

John - 20

Dave - 140

John - 70

John - 200

Dave - 200

 

Would output on the scoreboard as...

Dave - 340

John - 290

etc..

 

Thanks guys.

Link to comment
https://forums.phpfreaks.com/topic/225320-scoreboard-output-from-mysql-help/
Share on other sites

SELECT player, score
FROM (
  SELECT concat(users.first_name, ' ', users.last_name) AS player, sum(scores.score) AS score
  FROM users
  JOIN scores ON users.id = scores.user_id
  GROUP BY player
) AS scoreboard
ORDER BY score DESC

 

Not the most efficient way as MySQL will use a temporary table for the sorting, but it will point you in the right direction.

 

I wonder if the below would work:

 

SELECT concat(users.first_name, ' ', users.last_name) AS player, sum(scores.score) AS score
FROM users
JOIN scores ON users.id = scores.user_id
GROUP BY player
ORDER BY sum(scores.score) DESC

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.