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

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

how can select count of distinct values info stored comma separated values in mysql? i'll using php output info mysql in end.

what's in there, tags each post. in end, i'm trying output info way stackoverflow it's tags, this:

tag-name x 5

this how info in table looks (sorry content, it's site 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 info above pork 4 // occurs 4 time in info above spaghetti 3 // 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 select count of distinct values in there, since it's comma separated data, there appears no way this. select distinct not work.

can think of way in either mysql or using php output way i've done?

solution

i don't know how transform horizontal list of comma-separated values list of rows without creating table containing numbers, many numbers may have comma-separated values. if can create table, here answer:

select substring_index(substring_index(all_tags, ',', num), ',', -1) one_tag, count(*) cnt ( select group_concat(tags separator ',') all_tags, length(group_concat(tags separator ',')) - length(replace(group_concat(tags separator ','), ',', '')) + 1 count_tags test ) t bring together numbers n on n.num <= t.count_tags grouping one_tag order cnt desc;

returns:

+---------------------+-----+ | one_tag | cnt | +---------------------+-----+ | chicken | 5 | | pork | 4 | | spaghetti | 3 | | fried-rice | 2 | | manchurain | 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-and-sour-pork | 1 | | peking-duck | 1 | | duck | 1 | +---------------------+-----+ 17 rows in set (0.01 sec) see sqlfiddle explaination scenario we concatenate tags using comma create 1 list of tags instead of 1 per row we count how many tags have in our list we find how can 1 value in list we find how can values distinct rows we count tags grouped value context

let's build schema:

create table test ( id int primary key, tags varchar(255) ); insert test values ("1", "pho,pork"), ("2", "fried-rice,chicken"), ("3", "fried-rice,pork"), ("4", "chicken-calzone,chicken"), ("5", "fettuccine,chicken"), ("6", "spaghetti,chicken"), ("7", "spaghetti,chorizo"), ("8", "spaghetti,meat-balls"), ("9", "miso-soup"), ("10", "chanko-nabe"), ("11", "chicken-manchurian,chicken,manchurain"), ("12", "pork-manchurian,pork,manchurain"), ("13", "sweet-and-sour-pork,pork"), ("14", "peking-duck,duck"); concatenate list of tags

we work tags in single line, utilize group_concat job:

select group_concat(tags separator ',') test;

returns tags separated comma:

pho,pork,fried-rice,chicken,fried-rice,pork,chicken-calzone,chicken,fettuccine,chicken,spaghetti,chicken,spaghetti,chorizo,spaghetti,meat-balls,miso-soup,chanko-nabe,chicken-manchurian,chicken,manchurain,pork-manchurian,pork,manchurain,sweet-and-sour-pork,pork,peking-duck,duck

count tags

to count tags, length of total list of tags, , remove length of total list of tags after replacing , nothing. add together 1, separator between 2 values.

select length(group_concat(tags separator ',')) - length(replace(group_concat(tags separator ','), ',', '')) + 1 count_tags test;

returns:

+------------+ | count_tags | +------------+ | 28 | +------------+ 1 row in set (0.00 sec) get nth tag in tag list

we utilize substring_index function

-- returns string until 2nd delimiter\'s occurrence left right: a,b select substring_index('a,b,c', ',', 2); -- homecoming string until 1st delimiter, right left: c select substring_index('a,b,c', ',', -1); -- need both get: b (with 2 beingness tag number) select substring_index(substring_index('a,b,c', ',', 2), ',', -1);

with such logic, 3nd tag in our list, use:

select substring_index(substring_index(group_concat(tags separator ','), ',', 3), ',', -1) test;

returns:

+-------------------------------------------------------------------------------------+ | substring_index(substring_index(group_concat(tags separator ','), ',', 3), ',', -1) | +-------------------------------------------------------------------------------------+ | fried-rice | +-------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) get values distinct rows

my thought little tricky:

i know can create rows joining tables i need nth tag in list using request above

so create table containing numbers 1 maximum number of tags may have in list. if can have 1m values, create 1m entries 1 1,000,000. 100 tags, be:

create table numbers ( num int primary key ); insert numbers values ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 ), ( 10 ), ( 11 ), ( 12 ), ( 13 ), ( 14 ), ( 15 ), ( 16 ), ( 17 ), ( 18 ), ( 19 ), ( 20 ), ( 21 ), ( 22 ), ( 23 ), ( 24 ), ( 25 ), ( 26 ), ( 27 ), ( 28 ), ( 29 ), ( 30 ), ( 31 ), ( 32 ), ( 33 ), ( 34 ), ( 35 ), ( 36 ), ( 37 ), ( 38 ), ( 39 ), ( 40 ), ( 41 ), ( 42 ), ( 43 ), ( 44 ), ( 45 ), ( 46 ), ( 47 ), ( 48 ), ( 49 ), ( 50 ), ( 51 ), ( 52 ), ( 53 ), ( 54 ), ( 55 ), ( 56 ), ( 57 ), ( 58 ), ( 59 ), ( 60 ), ( 61 ), ( 62 ), ( 63 ), ( 64 ), ( 65 ), ( 66 ), ( 67 ), ( 68 ), ( 69 ), ( 70 ), ( 71 ), ( 72 ), ( 73 ), ( 74 ), ( 75 ), ( 76 ), ( 77 ), ( 78 ), ( 79 ), ( 80 ), ( 81 ), ( 82 ), ( 83 ), ( 84 ), ( 85 ), ( 86 ), ( 87 ), ( 88 ), ( 89 ), ( 90 ), ( 91 ), ( 92 ), ( 93 ), ( 94 ), ( 95 ), ( 96 ), ( 97 ), ( 98 ), ( 99 ), ( 100 );

now, numth (num beingness row in number) using next query:

select n.num, substring_index(substring_index(all_tags, ',', num), ',', -1) one_tag ( select group_concat(tags separator ',') all_tags, length(group_concat(tags separator ',')) - length(replace(group_concat(tags separator ','), ',', '')) + 1 count_tags test ) t bring together numbers n on n.num <= t.count_tags

returns:

+-----+---------------------+ | num | one_tag | +-----+---------------------+ | 1 | pho | | 2 | pork | | 3 | fried-rice | | 4 | chicken | | 5 | fried-rice | | 6 | pork | | 7 | chicken-calzone | | 8 | chicken | | 9 | fettuccine | | 10 | chicken | | 11 | spaghetti | | 12 | chicken | | 13 | spaghetti | | 14 | chorizo | | 15 | spaghetti | | 16 | meat-balls | | 17 | miso-soup | | 18 | chanko-nabe | | 19 | chicken-manchurian | | 20 | chicken | | 21 | manchurain | | 22 | pork-manchurian | | 23 | pork | | 24 | manchurain | | 25 | sweet-and-sour-pork | | 26 | pork | | 27 | peking-duck | | 28 | duck | +-----+---------------------+ 28 rows in set (0.01 sec) count tags occurences

as have classic rows, can count occurrences of each tags.

see top of answer see request.

php mysql sql