MySql PHP select count of distinct values from comma separated data (tags)

Question

How can I select the count of distinct values from data that is stored as comma separated values in MySql? I'll be using PHP to output the data from MySql in the end.

What's in there, are tags for each post. So in the end, I'm trying to output data just like the way stackoverflow does with it's tags, like this:

tag-name x 5

This is how the data in the table looks like (sorry about the content, but it's a site for recipes).

"postId"    "tags"                                  "category-code"
"1"         "pho,pork"                              "1"
"2"         "fried-rice,chicken"                    "1"
"3"         "fried-rice,pork"                       "1"
"4"         "chicken-calzone,chicken"               "1"
"5"         "fettuccine,chicken"                    "1"
"6"         "spaghetti,chicken"                     "1"
"7"         "spaghetti,chorizo"                     "1"
"8"         "spaghetti,meat-balls"                  "1"
"9"         "miso-soup"                             "1"
"10"        "chanko-nabe"                           "1"
"11"        "chicken-manchurian,chicken,manchurain" "1"
"12"        "pork-manchurian,pork,manchurain"       "1"
"13"        "sweet-and-sour-pork,pork"              "1"
"14"        "peking-duck,duck"                      "1"

Output

chicken             5 // occurs 5 time in the data above
pork                4 // occurs 4 time in the data above
spaghetti           3 // an so on
fried-rice          2
manchurian          2
pho                 1
chicken-calzone     1
fettuccine          1
chorizo             1
meat-balls          1
miso-soup           1
chanko-nabe         1
chicken-manchurian  1
pork-manchurian     1
sweet-n-sour-pork   1
peking-duck         1
duck                1

I'm attempting to select count of all distinct values in there, but since it's comma separated data, there appears to be no way to do this. select distinct will not work.

Can you think of a good way in either mysql or using php to get output like the way I've done?

Answer 1

Alain Tiembo has a nice answer which explains a lot of the mechanics underneath. However, his solution requires a temporary table (numbers) to solve the problem. As a follow up answer, I am combining all his steps into one single query (using tablename for your original table):

    SELECT t.tags, count(*) AS occurence FROM
    (SELECT
      tablename.id,
      SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.tags, ',', numbers.n), ',', -1) tags
    FROM
      (SELECT 1 n UNION ALL SELECT 2
       UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN tablename
      ON CHAR_LENGTH(tablename.tags)
         -CHAR_LENGTH(REPLACE(tablename.tags, ',', ''))>=numbers.n-1
    ORDER BY
      id, n) t
    GROUP BY t.tags
    ORDER BY occurence DESC, t.tags ASC

See the SQLFiddle for demonstration purposes.