can this be done? find cound of keyword occurence over multiple records

[fotobleu]

hi

 

i have a db with a bunch of records, each record has a comma delimited tag str in it

for ex:

TAGS

black, white, green

yellow,white,red

green,black,blue

gray,orange,white

 

what i want to do trough mysql is get the count for each tag occurrence

 

so for the example above i would get

 

TAG COUNT,TAG NAME

3              white

2              green

2              black

1              yellow

1              orange

1              red

1              blue

1              gray

 

they dont have to be sorted

 

i can do this in php, but im trying to put some of the work on the db, has anyone done this before with a sql statement

 

thanks in advance

 

-daniel

[quiettech]

Check mysql documentation on 11.4 String Functions. You'll probably want a LIKE directive along with COUNT, if I understood your question right.

 

SELECT COUNT(*) FROM `somewhere` WHERE `tag` LIKE '%white%';

 

Note however this will not work if 'white' can appear more than once on the same tag field. On that case, you will need to use regular expressions (11.4.2 on the manual). I'm not enirely sure how to do that with REGEXP. But I seem to believe you can get the count of a pattern matching in a string.

[gluck]

hi

 

i have a db with a bunch of records, each record has a comma delimited tag str in it

for ex:

TAGS

black, white, green

yellow,white,red

green,black,blue

gray,orange,white

 

what i want to do trough mysql is get the count for each tag occurrence

 

so for the example above i would get

 

TAG COUNT,TAG NAME

3              white

2              green

2              black

1              yellow

1              orange

1              red

1              blue

1              gray

 

they dont have to be sorted

 

i can do this in php, but im trying to put some of the work on the db, has anyone done this before with a sql statement

 

thanks in advance

 

-daniel

 

DO you mean a field has the value "white,black,red" and you want each color listing? If that is the case I doubt you can do it. You migh want to select all values and then split in php. 

 

[fotobleu]

yeah thats exactly what i mean, each field has a comma delimited keyword string

thats what i thought too, but i wanted to ask to make sure, the problem is how do i differentiate between the keywords in each field and select each one and match each one, i guess you cant do it in mysql, ill do it in php, thanks

[aschk]

Indeed, unfortunately because the data is essentially unnormalised you can't extra this information "on the fly". Normalise this and you won't have a problem.

[fotobleu]

normalize, you mean split the field containing the comma separated keyword list into individual fields for each keyword, if thats what you mean, then i cant do that for my spec because i'm allowing the user to choose the nr of keyword it wants to enter, is this what you meant by normalize?

thanks

[Barand]

Instead of a comma delimited fields in the table, create a second table for the keywords

 

So, currently

[pre]

mytable

---+------------+----------------------+

id |  somedata  |  tags                |

---+------------+----------------------+

1 |  xyz      | black, white, green  |

2 |  abc      | yellow,white,red    |

---+------------+----------------------+

[/pre]

 

normalized

 

[pre]

mytable                      tags

---+------------+            +---+--------+------------+

id |  somedata  |            |id | my_id  |  tag      |

---+------------+            +---+--------+------------+

1 |  xyz      |            | 1 |  1    |  black    |

2 |  abc      |            | 2 |  1    |  white    |

---+------------+            | 3 |  1    |  green    |

                            | 4 |  2    |  yellow    |

                            | 6 |  2    |  white    |

                            | 7 |  2    |  red      |

                            +---+--------+------------+ [/pre]