0

I have a database of 30000 products and all products has assigned different categories. There is a separate table for all categories & sub-categories are also stored into same table. The table structure is something like this

Category_mst

category_id_pk, category_name, parent_id

Product_mst

product_id_pk, product_name, category_id_fk (reference to Category_mst->category_id_pk)

Categories are stored upto any number of sub levels. So there can be categories like below

Clothes > Shirts > Kids > Red Color

Products are stored on any level of Category. I want to make a query or a php script that can find out all the Categories that have no Products under it(upto any sub level).

How can I do this ?

Thanks in advance.

aslamdoctor
  • 3,753
  • 11
  • 53
  • 95
  • If you can get `category_id_pk` for each sub category, use `OR` statement in your SQL ("select product_id_pk from product_mst where category_id_pk=X OR category_id_pk=Y OR category_id_pk=Z"). Then use mysql_num_rows(); – Zul Dec 19 '11 at 07:32
  • 1
    I'd suggest you to change your database design to make your life easier. Check this article http://www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql – Vagelis Ouranos Dec 19 '11 at 07:34

1 Answers1

1

You are trying to store hierarchical data in a database.
The naive approach to this is a very poor one, because MySQL does not support recursive queries (unlike Oracle or SQL-server).
Change your database design.

See: The Nested Set Model hierarchical data
Implementing a hierarchical data structure in a database

Community
  • 1
  • 1
Johan
  • 74,508
  • 24
  • 191
  • 319