Check if specific value exists in mysql column

Question

I have mysql column called categories. It can contain single or multiple values like this: 1 or 2 or 1,2,3 or 2,12...

I try to get all rows containing value 2.

$query = "SELECT * FROM my_table WHERE categories LIKE '2'";
$rows = mysql_query($query);

This returns row if column only has value 2 but not 1,2,3 or 2,12. How I can get all rows including value 2?

Answer 1

You can use either of the following:

% is a wildcard so it will match 2 or 1,2, etc. Anything on either side of a 2. The problem is it could match 21, 22, etc.

$query = "SELECT * FROM my_table WHERE categories LIKE '%2%'";

Instead you should consider the find_in_set mysql function which expects a comma separated list for the value.

$query = "SELECT * FROM my_table WHERE find_in_set('2', `categories`)";

Answer 2

Like @jitendrapurohut said, you can do it using

$query = "SELECT * FROM my_table WHERE categories LIKE '%2%'";
$rows = mysql_query($query);

But is really bad to store collections like this. A better aproach is as follow:

1. categories(id_c, name) => A table with each category
2. my_table(id_m [, ...])
3. categories_my_table(id_c, id_m)

Then use this query:

SELECT *
FROM my_table m
INNER JOIN categories_my_table cm ON m.id_m = cm.id_m
INNER JOIN categories c ON cm.id_c = c.id_c
WHERE
c.id_c = 2;

EDIT: @e4c5 link explains why it is bad to store collections like this...

Answer 3

SELECT * FROM my_table WHERE categories LIKE '%2%' AND categories!='1,2,3' AND categories!='2,12';