0

I am working on an e-commerce site from scratch using PHP and MYSQL. I have one table for categories, with a column for ID and a column for Parent_ID.

When displaying the products on categories.php, I have it set to display products where the Parent_ID OR the ID equals the $_GET['id'] field. But now I've run into a problem.

In my "Groceries" category, I have the "Cleaning & Home" subcategory, and under "Cleaning & Home" I have several categories like "Laundry", "Bathroom Supplies", etc.

My problem is that products in the third level don't display in the "Groceries" category, because technically the parent ID of "Laundry" is "Cleaning & Home". There will never be more than three levels (Parent, child, grandchild), but I would like categories in the grandchild level to also display in the parent level.

I've tried looking through MYSQL documentation and on other forums but so far no luck.

Can
  • 8,502
  • 48
  • 57
DecimalCampaign
  • 35
  • 1
  • 2
  • 9
  • Do you know the number of potential levels? If so, you can self the join the table on itself over and over. If not, you'll need to look into using dynamic sql. Lots of examples on SO about it. On a side note, if you're creating it from scratch, consider researching the `Nested Set Model` -- much easier for this type of thing. – sgeddes Jul 21 '13 at 19:03

1 Answers1

0

This requires a couple of joins to get to the top parent:

select c.*,
       coalesce(cp2.id, cp.id, p.id) as MostParentId
from categories c left outer join
     categories cp
     on c.parent_Id = cp.id left outer join
     categories cp2
     on cp.parent_id = cp2.parent_id
where c.id = $_GET['id'] or cp.id = $_GET['id'] or cp2.id = $_GET['id'];

You can then compare the ids using or for a match to the parent category, subcategory, or whatever.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Nested sets can be very useful for storing hierarchies in SQL: http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree – user4035 Jul 21 '13 at 19:03
  • 1
    @user4035 . . . Nested sets/closure tables are good to know about. However, the OP explicitly makes the point that there are at most three levels of categories. This does not require a general solution, especially one that requires a complex set of triggers to maintain data integrity. – Gordon Linoff Jul 21 '13 at 19:07