I need a mysql statement written to handle categories for me. here is my db layout and what i need to happen
products table – id, category, name
1, 1, product 1
2, 5, product 2
3, 6, product 3
category table = id, parent, name
1, 0, category 1
2, 0, category 2
3, 0, category 4
4, 0, category 5
5, 2, category 6
6, 2, category 7
7, 4, category 8
8, 4, category 9
i need to be able to show categories on the first page that have products in it or that has sub categories that have products in them.
so example.
category 1 has a product in it then it would show
category 2 has sub categories in it and 1 of the sub category has a product in it so it would show
category 3 has nothing in it so it does not show
category 4 has sub categories in it but no products in the sub categories or the category so it would not show
