How can I filter this data from my database?

Question

I have a table with a name and type field. The type field contain the musical taste of the person in this way:

"Rock; Pop; Metal; Dance" (varchar)

I need to generate an excel list with this persons grouped by music taste.

For example:

1. One list with everybody in Rock; Metal; Dance
2. Another with everybody in Rock; Metal; Trance
3. Another with Rock (Only Rock)
4. and so on...

Each user can be only in one list. I need this list to import in my newsletter software. Each type is a mailing list.

I need to perform all kind of combinations and then create the list when get results. I was wondering which is the best and faster way to do this. Can be trought a php script or even just SQL.

Ps.: I need this data from an old database created by someone else. I agree, is a terrible way to store.

Answer 1

It seems to me that you could run this simple query:

SELECT * FROM table_name ORDER BY music_taste_field

and then split the result up when the value of music_taste_field changes. You could do that manually or in PHP.

Also, as someone else has commented, that's a terrible way to store music preferences.

Answer 2

And another wrong database design (was a similar question today).

First of all, for any future operations, all enumerations in your database should be separate relational structures (e.g. Tables). That way it would be very easy to create these lists, using indexed fields.

Now you will call string manipulating functions that work slow as hell, forget about indexes, which leaves you to a roughly 100 times slower performing query and less sure data. That's where wrong database design leads.

You could of course use something like find_in_set() after replacing the semicolon with a comma, but this is perversion.
The best solution is to redesign the database if further requests of this type will be needed.