MySQL - count where id contained in a string

Question

My table is called "asd"

I have this schema:

id        |  date     |  content |
AUTO_INC    DATETIME    LONGTEXT

now assuming content = 45,67,89,3,45,5

how do i search COUNT() in this table WHERE content CONTAINS 89 for example ?

i tryed SELECT COUNT() FROM asd WHERE content IN(89); but i got no results.

score 6 · Accepted Answer · answered Jul 10 '13 at 07:30

6

You can just use FIND_IN_SET

SELECT COUNT(*) FROM `table` WHERE FIND_IN_SET(89,`content`);

P.S. You need to read about many-to-many relations.

answered Jul 10 '13 at 07:30

sectus

this is exactly what i need i think, need just to test it +1 – itsme Jul 10 '13 at 07:32

score 1 · Answer 2 · answered Jul 10 '13 at 07:19

1

Try this

SELECT COUNT(*)
FROM   ASD
WHERE ',' + content  + ','  like '%,89,%'

answered Jul 10 '13 at 07:19

bvr

BhavikKama · Answer 3 · 2013-07-10T07:26:55.600

1

can you please try like this....i have seen something you want in this link http://www.randomsnippets.com/2008/10/05/how-to-count-values-with-mysql-queries/ ..hope it helps

  SELECT SUM(IF(content = 89, 1,0)) AS `MATCHED VALUE`,
    COUNT(content) AS `total`
    FROM asd

edited Jul 10 '13 at 07:26

answered Jul 10 '13 at 07:21

BhavikKama

mishik · Answer 4 · 2013-07-10T07:31:48.857

0

SELECT COUNT(*)
FROM   ASD
WHERE  content LIKE '%,89,%' OR
       content LIKE '%,89' OR
       content LIKE '89,%'

%,89,% - to match 89 in the middle of the content

%,89 - at the end of the content

89,% - at the beginning of the content

edited Jul 10 '13 at 07:31

answered Jul 10 '13 at 07:18

mishik

no this will count **,890** too, i need to count where it is exactly 89 – itsme Jul 10 '13 at 07:18
No, it will not, I have `,`. 890 does not match any of `,89,` or `,89` or `89,` – mishik Jul 10 '13 at 07:19
uhmmmm not sure about mmmm – itsme Jul 10 '13 at 07:19
890 can be matched by `89%` or `,89%` or `%89%` for example, but not with `,` specified. – mishik Jul 10 '13 at 07:20
will this work also with my set 45,67,89,3,45,5 ? what if need 5 instead of 89 it's same query ? – itsme Jul 10 '13 at 07:22
Yes, it will. To match `5` use: `%,5,%`, `%,5` and `5,%` – mishik Jul 10 '13 at 07:24
if no other solution i'll accept your answer thx! – itsme Jul 10 '13 at 07:24

score 0 · Answer 5 · answered Jul 10 '13 at 07:19

0

SELECT COUNT(*) as cnt FROM `asd` WHERE `content` LIKE '%,89,%' OR  `content` LIKE '89,%' OR `content` LIKE '%,89';

answered Jul 10 '13 at 07:19

Goutam Pal

5 Answers5