CODEWITHSUNDEEP
phpmysqlsql
T1000
T1000

Reputation: 2941

Select all items for category and all from its subcategories

I'm trying to build my own webshop.
I have two tables - products and categories. The structure is like that : 

categories :
id       name       parent_id  

products :
id       title      category_id

At the moment when the user clicks on main category I'm selecting products to display like that :

url : www.mypage.com/?category_id=1
sql : 'SELECT * FROM products WHERE category_id = 1'

The problem is that I'd like to make it when the user clicks on main category to select also all the products from its child categories. For example category Family is sub category of category Cars and in the db looks like that 

categories :
id       name       parent_id  
1        'Cars'     0
2        'Family'   1  
3        'Sport'    1  

products :
id       title      category_id  
1        'Ferrari'  3 
2        'Honda'    2

As you can see my current select will not select Ferrari or Honda because the user is looking at category with id=1... How to modify my select so it will display all products from the child categories of the main category?

Upvotes: 0

Views: 3758

Answers (3)

J A
J A

Reputation: 1766

If you have more than two levels in category tree, you'll need a separate table (sometimes referred as Closure table) which will have reference of each category and it's ancestor / descending categories. Check this link for examples: https://stackoverflow.com/questions/tagged/transitive-closure-table

Upvotes: 2

Spock
Spock

Reputation: 4900

If the hierarchy is only one level deep, you can modify the query like this...

SELECT * FROM Products WHERE Category_id IN (
    SELECT Id 
    FROM Categories 
    WHERE Id = 1 OR parent_id = 1)

Hope that helps

Upvotes: 1

Pupil
Pupil

Reputation: 23948

"SELECT * FROM products WHERE category_id = '$your_category_id'
OR category_id IN (
  SELECT parent_id FROM categories 
    WHERE id = '$your_category_id'
)"

Note: This example is only for two level depth.

e.g.

Ferrari > Cars

Its not for more than two levels.

e.g.

Ferrari Child Category > Ferrari > Cars

Upvotes: 2

Related Questions