CODEWITHSUNDEEP
phpmysql
EnexoOnoma
EnexoOnoma

Reputation: 8836

How to count how many times a string appears in a field in mySQL separated by comma?

My mySQL is like this, an id, and a texts that have may phrases and some identical as below:

id      texts
2       abc,fd4g,oigdu,abc,abc
3       ccc,fff,fff,ccc,iop

My question is how can I show using PHP/mySQL

  1. per id

2 - abc - 3 times

2 - fd4g - 1 times

2 - oigdu - 1 times

3 - ccc - 2 times

3 - fff - 2 times

3 -iop - 1 times

  1. or searching via the text

    abc - 2 times - 2

    fd4g - 1 times - 2

    oigdu - 1 times - 2

    ccc - 2 times - 3

    fff - 2 times - 3

    iop - 1 times - 3

-

If my mySQL structure was :

id      texts
2       abc
2      fd4g
2      oigdu
2      abc
2      abc
3       ccc
3      fff
3      fff
3      ccc
3      iop

would this be easier?

Upvotes: 0

Views: 96

Answers (2)

CodeSlayer
CodeSlayer

Reputation: 1329

You can get the the count of same value by doing this code

select id, texts, count(id) from table group by texts

Upvotes: 0

tjati
tjati

Reputation: 6079

The second structure would be easier. But you shouldn't name your first column id because the id-field usually has unique key values, that means only auto-incrementing values. You have multiple times the same number, so you it's obviously not unique but something different.

With the other query you could run

SELECT id, texts, COUNT(*) FROM tbl group by id, texts

and your result would be

2  |  abc  |  3
2  | fd4g  |  1

and so on.

Upvotes: 3

Related Questions