Counting across multiple columns

[joe7410]

What I'm trying to accomplish is to count the number of entries in a sql db across 5 columns and return the 5 entries that have the most occurrences. Basically create a TOP 5 list.

A simple example of what I want to do would be to find out the two most favorite fruit out of a group of people. I ask them to list their three most favorite fruits. They input that data into a form and it ends up in a sql database looking like this:

ID name fruit1 fruit2 fruit3 fruit4 fruit5
1 bob apples grapes bananas strawberries melons
2 sam grapes mangos apples blueberries cherries
3 amy melons apples grapes oranges grapefruit
4 jon apples bananas oranges pinapple strawberries

I want to calculate which fruit in all five columns has the most number of occurrences, to where my output is the following:

The most popular fruits are apples(4) and grapes(3).

Here is some code that I cobbled together which will list the occurrences of the fruit in the fruit1 column. But I'm not sure how to:

1. expand that to count 5 columns worth of entires.
2. only return the top 5 occurrences.
3. or deal with the event that some entries may have the same number occurrences, which may make the top 5 list grow to 6 or 7 entries.

$query = "SELECT *, COUNT(id) FROM fruits GROUP BY fruit1";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
	echo $row['fruit1'] . "(". $row['COUNT(id)'] . ")";
	echo "<br />";
}

Any help you can give would be greatly appreciated.

[cgraz]

If you design a table and have fields labled something like: field1, field2, field3, field4, then there is a problem. The best way to tackle your problem is to normalize your tables first. Something like this perhaps.

users(UserID, Name)
fruits(FruitID, Fruit)
users_fruits(FruitID, UserID)

and then your query would look like this

select f.Fruit,
  , count(f.FruitID) as cnt 
 from fruits as f 
inner
   join users_fruits as uf
      on uf.FruitID = f.FruitID 
group
   by f.FruitID 
order
   by cnt desc 
limit 5

[joe7410]

That's what I was afraid of. I am new to sql and have been reading up on the normalization of databases, but have never tried implementing it.

So I should have three databases that look like this:

User Db:

ID Name
1 Joe
2 John
3 Amy
4 Bill

A Fruit DB:

FID Fruit
1 Apples
2 Oranges
3 Grapes
4 Bananas

And then a User/Fruit Db:
FID ID
1 1
2 1
3 1
4 2
3 2
2 2

[cgraz]

Yes, although i don't personally like ID as a primary/foreign key. Personal preference, but I'd call it UserID (or atleast UID)