PHP Ranking Algorithm [closed]

Question

I have a website that is going to rank users based on points they have earned for doing certain activities. This is my database structure:

pointsentered
1. ID
2. Email
3. Code

pointvalues
1. ID
2. Code
3. Title
4. Value

I have a file that connects to pointsentered and submits the user-selected to it. The "Code" field corresponds to the pointvalues.

In pointvalues, the "Title" corresponds to the name of the event they completed. The code is a simple alpha-numeric code that corresponds to that specific event and the value it holds. "Value" is fairly self explanatory, as it holds the point value that the event relates to.

Essentially, I want to have a script that
1. Ranks all of the users (who have different emails) based on entered points in pointsentered (but grabs points from pointvalues based on the "Code") 2. Shows how many points the user has attained. It relates to the user's email.

Obviously, I am not looking for someone to do this for me, I am just looking for some guidance on how to get started, as I am lost.

Thanks in advance.

Answer 1

This should be fairly easy to achieve with a simple query along the lines of:

SELECT pe.Email, SUM(pv.Value) as 'total_points' FROM pointsentered pe
JOIN pointvalues pv ON pe.Code = pv.Code
GROUP BY pe.Email
ORDER BY total_points DESC

What's going here is you have two tables pointsentered and pointvalues. The pointsentered table contains the Email (pe.Email) of the person who was awarded points. Also in that table is a Code (pe.Code) which corresponds to a row in the pointvalues table. So we can query against the pointsentered and JOIN the pointvalues table to get a list of all awarded points, the email of the person who was awarded, and the number of points awarded. We can also apply an aggregate function to the point values (SUM) and group by the persons Email, yielding a list of all unique Emails along with the total number of points awarded to that Email, starting with the highest number of total points (DESC).