php - Trying to get the Parent ID of the Parent ID -

i working on e-commerce site scratch using php , mysql. have 1 table categories, column id , column parent_id.

when displaying products on categories.php, have set display products parent_id or id equals $_get['id'] field. i've run problem.

in "groceries" category, have "cleaning & home" subcategory, , under "cleaning & home" have several categories "laundry", "bathroom supplies", etc.

my problem products in third level don't display in "groceries" category, because technically parent id of "laundry" "cleaning & home". there never more 3 levels (parent, child, grandchild), categories in grandchild level display in parent level.

i've tried looking through mysql documentation , on other forums far no luck.

this requires couple of joins top parent:

select c.*,        coalesce(cp2.id, cp.id, p.id) mostparentid 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 c.id = $_get['id'] or cp.id = $_get['id'] or cp2.id = $_get['id']; 

you can compare ids using or match parent category, subcategory, or whatever.