subcategories and root category

[Guest]

Hi,

 

I'm trying to modify the Featured Products contribution this way : I want a featured product only to be shown in his category. It works like a charm when the product is in 2nd level of the category tree, but won't go any further.

 

Here is the part of my featured.php file :

 

	$mystring = $cPath;
$findme = '_';
$pos = strpos($mystring, $findme);
if ($pos == NULL) { 
	$finalcPath = $cPath;
} else { 
	$finalcPath = substr($cPath, 0, $pos);
}
if ($finalcPath != NULL) {
$featured_products_query = tep_db_query("select p.products_id, p.products_image, p.products_quantity, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c left join " . TABLE_FEATURED . " f on p.products_id = f.products_id where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = $finalcPath and f.status = '1' order by rand($mtm) DESC limit " . MAX_DISPLAY_FEATURED_PRODUCTS); }
else {
$featured_products_query = tep_db_query("select p.products_id, p.products_image, p.products_quantity, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join " . TABLE_FEATURED . " f on p.products_id = f.products_id where p.products_status = '1' and f.status = '1' order by rand($mtm) DESC limit " . MAX_DISPLAY_FEATURED_PRODUCTS);

 

As you can see, I'm playing with c.parent_id and $finalcPath.

 

My question is : how, from the categories table to find the 2nd level category ?

 

Thanks in advance

[Guest]

Better told my question is : how, only from a products_id, can I find the "4" (root category) of "4_234_354_655" within a SQL query ?