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

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)
commented: working +0

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

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

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

See above.

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