mysql query question.

Question

Hello I have a table with articles and the articles have a column category. The categories are filled like 1,4,6,8

How can i check if there is a product which has in it for example category 5

I tried something like

select * from article where category in(5);

But that doesn't work. If I use like than i will have a problem with for example 1 and 10. So how can I do this in one mysql query?

Answer 1

try this select * from article where category like '%,5,%' or category like '5,%' or category like '%,5'

Answer 2

1. Storing CSV in a column you need to query is a bad idea - you should use a separate table.
2. IN is not for CSVs - it's for listing values for a single column
3. Those arguments aside, you can use FIND_IN_SET()

For example:

SELECT * FROM article WHERE FIND_IN_SET('5', category) != 0;

Answer 3

You could do it with select * from article where category='5' or category like '5,%' or category like '%,5' or category like '%,5,%'

But you really don't want to do that.

Instead, what you're after is

create table article (
  id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
  headline VARCHAR(50) NOT NULL,
  body VARCHAR(50) NOT NULL
);

create table articlecategory (
  article_id INTEGER NOT NULL,
  category_id INTEGER NOT NULL,
  PRIMARY KEY (article_id, category_id)
);

And then

SELECT 
  article.* 
FROM 
  article,articlecategory 
WHERE 
  articlecategory.article_id=article.id AND 
  articlecategory.category_id=5;

Answer 4

well, that is not the best solution database wise, to have a column with comma separated values. Instead you could have a separate table with two columns, one for the category and one with a article id. Then you could do:

select * from article, category_articles where category_article.category_id = category.id and category_article.category = 5

Take a look at this for more on database normalization, which this is kinda related to

But if that is not an option you could try using a delimiter and store the data like this: C1C10C, then you could use:

select * from article where category like '%C1C%'

which would not mach 10, but will match the 1.