Calculate user ranking

oMIKEo · 02-29-2008, 10:37 AM

Hi,

I'm trying to work out how to rank a user based on their progress compared to other users.

Here is a simplified example of the structure:

Users
- id

Log
- id
- user_id
- book_id

Books
- id

A user can have multiple number of logs but duplications of the same book shouldn't be counted.

To calculate a book completion percentage i have

$percentage = round(($total_books / 100) * $total_number_of_unique_logs, 2)

but how would i calculate their progress compared to the percentage of other users?

Any help would be great.
Thanks, Mike

serversphere · 02-29-2008, 01:23 PM

Sounds like a job for a relational database (like MySQL). Is your data stored in a database?

oMIKEo · 02-29-2008, 02:30 PM

Yeah all the info is stored in a mysql database, its just the process of using that information to get the desired results out that i need help with.

serversphere · 02-29-2008, 03:18 PM

I would let mysql do the work for me. But first, I would get my relational data in the database named correctly. Make it like below, so that book_id in your log table relates to book_id in books table, same with user_id... nit picky observation of mine. ;)

Code:

Users
- user_id

Log
- log_id
- user_id
- book_id

Books
- book_id

Now let mysql do the work for you using a query similar to this:

Code:

SELECT user_id,
(COUNT(book_id) FROM books) AS total_books,
(total_books/100)*COUNT(*) AS book_completion_pctg
FROM log
GROUP BY user_id
ORDER BY book_completion_pctg DESC

I haven't tested this, or thought it through much but hopefully that might get you on the right track.