Mysql query to filter product by categoey

[thilipdilip]

I got a product table like this
id | name | category

1 | name1 | 2,1,5,8,9
2 | name2 | 2,1
3 | name3 | 5,8

And I got a category table like this

id | catname

1 | cat_name1
2 | cat_name2
5 | cat_name3
8 | cat_name3
9 | cat_name3

Each product can have multiple categories. Categories associated with that product are saved in a row named "category" in product table as "1,5,8,7", Where each represent category table's id.

Now I want to display all the products came under a specific category.

I am little bit confused with the query to handle this.

And is this the correct format for saving such type of data ? Please advice

[pritaeas]

And is this the correct format for saving such type of data ?

The usual way is to use a link table.

Now I want to display all the products came under a specific category.

SELECT * FROM product WHERE FIND_IN_SET('1', category)

[minitauros]

PS: A link table is an extra table that stores which products are connected to which categories. E.g.:

Table categories_products
category_id | product_id
1 | 5
1 | 6
1 | 9
2 | 5
2 | 7
2 | 10
3 | 6
3 | 9

Select all products in category 1:

SELECT products.*
    FROM categories_products
    JOIN products
        ON categories_products.product_id = products.id
    WHERE categories_products.id = 1

[thilipdilip]

Hi: minitauros thanks for your valuable reply.It is any possible to get the records without performaing with separate table.

[pritaeas]

It is any possible to get the records without performaing with separate table.

See above.

[thilipdilip]

Hi pritaeas, its working for me. Thanks for your timely and valuable help.