Create list from comma separated fields

[rythmik1]

I need to create an alphabetized list from multiple rows that contain comma separated tags.

Example:

Row1 -> Lessons, Discussions, Help
Row2 -> Discussions, Chat
Row3 -> Surveys, Evaluations, Discussions

Result needs to be:
Chat, Discussions, Evaluations, Help, Lessons, Surveys

Note that repeated items need to only appear once in the final alphabetized list. I have no idea how to go about making a query that will do this. Please help! :)

[Atli]

Hey.

That is not doable in any *practical* way. - The first rule of relational database design (the first normal form; 1NF) states that each field should only ever contain a single value. Putting a comma-separated list of values into single field violates that. - That rule exists precisely to prevent situations like these; having to manually manipulate a field to extract several values.

To put it simply; MySQL only recognizes each field as a single value. Because you have multiple values inside a single field, MySQL considers all of them to be a single value, and can therefore not alphabetize them correctly. - You will have to manually extract the values from the field, separate them, and then alphabetize them.

Your best option would be to correct the database design. Extract the multiple values from the fields, separate them, put them in a new table and link them to the rows they belong to by a foreign key. Then you can create a simple MySQL query that fetches them and returns them as you want them to be.

The tables should look something like:

+---------+     +------------+
| stuff   |     | tags       |
+---------+     +------------+
| id (PK) |>--  | id (PK)    | 
| etc...  |  |  | value      |
+---------+  -->| owner (FK) |
                +------------+

From which you could get a list of tags by just querying the "tags" table.

And if you need to fetch a comma-separated list of tags with your rows from "stuff", you can generate that by using the GROUP_CONCAT function:

SELECT
    `stuff`.`id`
    GROUP_CONCAT(DISTINCT `tags`.`value`) as 'tags'
FROM    `stuff`
LEFT JOIN `tags`
    ON `tags`.`owner` = `stuff`.`id`
GROUP BY `stuff`.`id`

Your alternative, if you want to stick to your current database structure, is to create a complex query that somehow separates the fields and sorts them (which would no doubt be highly inefficient), create a procedure that programmatically does the same, or just pull the data to your front-end and do it there.