CODEWITHSUNDEEP
phpmysqlsqlselectpdo
Riski Febriansyah
Riski Febriansyah

Reputation: 437

how to count the words in a sentence in mysql pdo

How would I go about counting the words in sentence in database, example like this:

id words

1  bananas, mango, orange, watermelon

2  bananas, mango, orange, watermelon

3  bananas, mango, orange, watermelon

4  bananas, mango, orange, watermelon

I just want to count the number of mango, if i count and i get the result is : 4

this my query

"SELECT count(words)as total FROM fuit WHERE ?????";

?Which means I do not know how to use it with WHERE..

But how get like this with query in mysql and pdo?

Upvotes: 2

Views: 204

Answers (2)

georg
georg

Reputation: 215019

If there were no spaces in the words field (e.g. bananas,mango,orange,watermelon), you could use FIND_IN_SET:

 SELECT ... WHERE FIND_IN_SET('mango', words)

With spaces, you have to use regexes, for example:

SELECT ... WHERE words RLIKE '(^|, )mango($|,)'

Better yet, consider "normalizing" your data - make a separate words table:

id  word
1   mango
2   orange
etc

and a linkage table:

doc_id    word_id
1         1
1         2
etc

Upvotes: 1

Mureinik
Mureinik

Reputation: 311978

Assuming that "mango" can only appear once in each row, you can use find_in_set to locate it. Note that find_in_set works on comma delimited strings, so you'd have to get rid of the whitespaces:

SELECT COUNT(*)
FROM   fruit
WHERE  FIND_IN_SET ('mango', REPLACE(words, ' ', '')) > 0

Upvotes: 1

Related Questions