User sumbitted top 5 and sort by popularity

Question

Hi,

Database setup (MySQL)

table: top_fives

id, uid, first,     second, third,   fourth, fifth,   creation_date
1,  1,   cheese,    eggs,   ham,     bacon,  ketchup, 2010-03-17
2,  2,   mayonaise, cheese, ketchup, eggs,   bacon,   2010-03-17

Users can submit their top 5 of a certain subject. Now I would like a summary of the top fives ordered by popularity.

Each column has it's own point value. column 'first' is rewarded 5 points, 'second' four points, 'third' three points, and so on...

So, in my example it should be something like this:

1 Cheese     (9 points = 5 + 4 -> 1 time in 'first' column and 1 time in 'second' column) 
2 Eggs       (6 points) 
3 Mayonaise  (5 points) 
4 Ketchup    (4 points) 
5 Bacon      (3 points) 
6 Ham        (3 points)

What would be the easiest solution (PHP) for this kind of situation?

Thanks in advance

Answer 1

The solution would be to normalize your table (see below).

If you can't, you should be able to do:

Select name, Sum(points) total_points
From (
    Select first name, 5 points
    From top_fives
    Union
    Select second name, 4 points
    From top_fives
    Union
    ...
)
Group By name
Order By total_points Desc

---

Normalized solution:

food

food_id, food_name
1        cheese
2        eggs
3        ham
...

food_rating
------
uid, food_id, points
1    1        5
1    2        4
1    3        3
2    1        4

.

Select f.food_name, Sum(r.points) total_points
From food_rating r
Join food f On ( f.food_id = r.food_id )
Group By food_name
Order By total_points Desc

Answer 2

The best solution would be to have normalized your data in the first place. And the only practical solution is to simulate the behaviour of a properly normalized database. Certainly the solution should not involve any PHP code and should be done on the database:

SELECT type, SUM(score)
FROM
(
(SELECT first as type, COUNT(*)*5 as score
 FROM top_fives
 GROUP BY first
) UNION
(SELECT second AS type, COUNT(*)*4 as score
 FROM top_fives
 GROUP BY second
) UNION
(SELECT third AS type, COUNT(*)*3 as score
 FROM top_fives
 GROUP BY third
) UNION
(SELECT fourth AS type, COUNT(*)*2 as score
 FROM top_fives
 GROUP BY fourth
) UNION
(SELECT fifith AS type, COUNT(*) as score
 FROM top_fives
 GROUP BY fifth
) 
)
GROUP By type
ORDER BY SUM(score) DESC;

C.