0

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

3
Contributors
5
Replies
16
Views
3 Years
Discussion Span
Last Post by thilipdilip
1

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)

Edited by pritaeas

Votes + Comments
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

Edited by minitauros

0

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

0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.