SQL SELECT query needed

I need a SELECT query please.

PHP shopping website

Here's the db schema that you need:

Table PRODUCTS
Product_ID PK
Category_ID FK to Category_ID in CATEGORIES TABLE

Table CATEGORIES
Category_ID PK
Parent_ID

Hmm, how to explain?  Categories can be many levels deep or only one level deep like this
Hats (Category_ID #1, Parent_ID #0 (0 means top level))
-Rimmed Hats (Category_#2, Parent_ID #1 (meaing sub of Hats)
---Straw Rimmed Hats (Category_ID #3, Parent_ID #2 (meaning sub of Rimmed Hats)
-----=Large Rimmed Hat with Ribbon (Product_ID #1, Category_ID #3)
---More Straw Rimmed Hats (Category_ID #4, Parent_ID #3 (meaning sub of Straw Rimmed Hats)
------Dyed Hats (Product_ID #5, Category_ID #4)
------------= Red Dyed Hat (Product_ID #2, Category_ID #5)

Glasses (Category_ID #6, Parent_ID #0)
--= DG Gold Rimmed Glasses (Product_ID #3, Category_ID #6)
--= Plastic Revlon Glasses (Product_ID #4, Category_ID #6)

I need a SELECT query that will select all the products in all the subcategories of a category no matter what level of category i'm in.  If it was two levels only I could do something like:

SELECT p.products_id  FROM PRODUCTS  p
        WHERE p.categories_id = '" . (int)$current_category_id . "'
OR p.categories_id in  (select categories_id from categories where parent_id =  '" . (int)$current_category_id . "

It doesn't have to be only one query and feel free to incorporate any php.  Just something that works and is efficient.

thanks!
Christa