display the subcategory data also on submit main category query in php

Question

I have created a website where i have put category in three levels like Main category -> sub category -> sub sub category

and this category i used in adding product whatever category i selected from the list each product have get in to that category. Now i am trying to display the products category wise on my site there i have put a list buttons where i have display all the category and sub categories now the problem is that when i m going to click on main category it displays only those products which are having that category selected but i want to display the subcategory also if a main category clicks, here is the query:

SELECT * FROM `products` WHERE `product_category`='$_REQUEST[cat]' ORDER BY `product_id` DESC

in this query the request cat is the name of the category which i got on page.

i want to know the query which display the sub category also if a main category is clicked

Answer 1

assuming you have some relations with product_category like parent_category when main category is clicked id=1 you should also search for it's children

like you did:

main category

SELECT * FROM `products` WHERE `product_category`='$_REQUEST[cat]' ORDER BY `product_id` DESC

sub category

SELECT * FROM `products` WHERE `category_parent`='$_REQUEST[cat]' ORDER BY `product_id` DESC

also be aware of your $_REQUEST['cat'] as it's vulnerable and can be exploited. You should use mysql_real_escape_string() or maybe just (int) ($_REQUEST['cat'])

Answer 2

SELECT product_category, (SELECT category_name FROM category WHERE c_id = products.c_id) AS Category, 
(SELECT sub_category_name FROM sub_category  WHERE sub_cat_id = products.sub_cat_id) AS        'Sub Category'
(SELECT sub_sub_category_name FROM sub_sub_category  WHERE sub_sub_cat_id = products.sub_sub_cat_id) AS        'Sub sub Category'
FROM `products` WHERE product_category='1'


store your sub categories and sub sub categories in other tables and in product table just     store their id's and then utilize the above query for the product with cate name and sub name 
and for sub categories of your relevant category you can select from sub cate table 
 SELECT * FROM  subcategory where category_id='$_REQUEST[cat]'