Grouping search results when storing different values in one column

I have to build a report from some data stored on a MySQL table and everything was going smoothly until I noticed that we are storing multiple values in one column. This is what I mean:

FIELDS: ID | CHOICES | AGE
VALUES: 1 | apple|orange|pear | 23
VALUES: 2 | peach|orange|pineapple|pear | 29

I need to search that table and find out how man开发者_Go百科y users like pear, how many like orange, etc. It would be a GROUP BY on that value but how do I break the contents of the CHOICE column into individual fields so I can search and group them like that? This code is executed from a PHP file, so I could also pre-process the data if needed, although I would prefer to do everything in the query if possible.

Thanks a lot in advance.

If you can't change the database design I would recommend doing this in PHP.

Loop over the result:

foreach $result
    foreach $fruits
        $fruit_array[$fruit] = $id

Then you have an array indexed by fruit with an array of ids as a value

BUT change the database design if possible

Preface: I'm a better HBase table designer.

Following NullUserException's comment, you'd definitely benefit from a better table schema. Something like:

Choices
USER_ID | CHOICE

Users
USER_ID | AGE

From here you could do a join to associate a user with his/her age and choice, or do a count to see how many people of age X chose fruit Y.

However, to solve your problem at hand you would have to select * and then manually count each user's preference. Using a radix-like approach wouldn't make this too bad. (Edit: See Galen's answer for the PHP code.)