CODEWITHSUNDEEP
phpmysqlsorting
R M
R M

Reputation: 317

how to make a ranking with mysql and php

Me and my friends are choosing a group name, and I'm trying to make this ranking where all of us sort the name order by our preferences. My program should do a calculation based on the order.

Example, if I sort "JAIME" 1 and "CERSEI" 2, "JAIME" gets 10 points and "CERSEI" 8 points. If my friend sort "JAIME" 1 and "CERSEI" 6, "JAIME gets 10 points and "CERSEI" get's no points. So, the rank will be:

1- JAIME with 20 points 2- CERSEI with 8 points

I have a simple table in mysql called "NAMES" with these fields 

"id"
"name"
"riccardo"
"roberto"
"marika"
(and others friend names)

With a Jquery script, every friend can sort the name list. The order number is automatically entered in the mysql db.

So, for example:

"id" --> 1
"name" --> JAIME
"riccardo" --> 1
"roberto" --> 2
"marika" --> 5 
1, 2, 5 are the order list number

Now that I got here, I have no idea how to proceed to obtain a rank based on the orders lists :(

hope my question is understandable

thank you

Upvotes: 1

Views: 379

Answers (1)

Jim
Jim

Reputation: 22656

Firstly I'd consider changing your schema somewhat. Currently adding friends means adding columns (which also means updating your queries). Better to have say a friends table and then a table for friends-names ratings.

However with what you have the calculation for a score given a position is:

10-((score-1)*2)

Given this we can create a query to get scores for names:

SELECT id, name, 10-((riccardo-1)*2) + 10-((roberto-1)*2) as score-- Plus all other names
FROM names
ORDER BY score

Using a more usual schema the query would look something like:

SELECT names.id, names.name, SUM(10-((names_ratings.rating-1)*2)) as score
FROM names
LEFT JOIN names_ratings ON (names.id = names_rating.name_id)
ORDER BY score

The second query means that you can add and remove friends much more easily and this wont change the code or the database schema.

Upvotes: 3

Related Questions