Best Way to Add Cateogry Name as a Product field?

[dailce]

I trying to figure out the best way to add a Category Name and Subcategory Name to a product.

 

Basically my products are in

 

CAT1--->SUBCAT1-->Prod1

CAT1--->SUBCAT1-->Prod2

CAT1--->SUBCAT1-->Prod3

CAT1--->SUBCAT2-->Prod2

etc......

 

If a buyer adds say Prod2, then I want to have CAT1 and SUBCAT1 stored in the database, or maybe there is another way? So that, when they check their shopping cart or order history I can show what cat and subcat the product came from.

 

Any ideas? Maybe I can pull the subcat and cat using the product id? How?

Thanks.

[dailce]

Just thought I'd share some of the code I came up with for shoppingcart.php changing function get_products() a bit. Seems to be working fine like this. Basically I did some table joins like this.

 

    $products_query = tep_db_query("select
    cr.parent_id,
    c.categories_id,
    p.products_id,
    pd.products_name,
    p.products_model,
    p.products_image,
    p.products_price,
    p.products_weight,
    p.products_tax_class_id from


	  " . TABLE_PRODUCTS . " p,
	  " . TABLE_CATEGORIES . " cr,
	  " . TABLE_PRODUCTS_TO_CATEGORIES . " c,
	   " . TABLE_PRODUCTS_DESCRIPTION . " pd where

		  p.products_id = '" . (int)$products_id . "' and
		  c.products_id = p.products_id and
		  cr.categories_id = c.categories_id and
		  pd.products_id = p.products_id and
		  pd.language_id = '" . (int)$languages_id . "'");

 

and

 

    $thesubcat_name = $this->getcatnamenow($products['categories_id']);

    $parentcat_name = $this->getcatnamenow($products['parent_id']);

 

 

and

 

   function getcatnamenow($somecatid){		
	 $catname_query = tep_db_query("select categories_name from " . TABLE_CATEGORIES_DESCRIPTION . " where categories_id = '" . (int)$somecatid . "'");
	  if (tep_db_num_rows($catname_query)) {
	    $thecatname = tep_db_fetch_array($catname_query);
	    return $thecatname['categories_name'];
	  }else{
	   return '';
	  }   
   }