products_to_categories how can I get a products cPath?

[dailce]

how can I get a products cPath if I'm given the products id?

 

I have something like this:

 

$sql_query[2] = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, s.status, s.specials_new_products_price, p.products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1'  order by p.products_quantity  desc  limit " . MAX_DISPLAY_NEW_PRODUCTS);

 

and i can get the products id so I can now make a link somewhere like this:

 

 <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products['products_id']) . '"><font color="#D0042F">' . $new_products['products_name'] . '</font></a>

 

BUT I want to make a link that give the cPath of the product:

 

<a href="' . tep_href_link(FILENAME_DEFAULT, cPath_id=' . XXXXXXXXXX) . '"><font color="#D0042F">' . $new_products['products_name'] . '</font></a>

 

Where XXXXXXXX is the cPath - How can I do this??

[Guest]

do another left join with TABLE_PRODUCTS_TO_CATEGORIES on products_id and select the categories_id. You need to generate the entire path however so once you fetch the categories_id call the tep_get_path function and pass it as an argument. It will return the cpath that you need.

[dailce]

Yeah I don't know ho to type the code to do all of that :(

[dailce]

So this is the code I have now:

 

$sql_query[4] = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, s.status, s.specials_new_products_price, p.products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1'  order by p.products_date_available desc  limit " . MAX_DISPLAY_NEW_PRODUCTS);


<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $myproducts[$new_products['products_id']]) . '">"' . $new_products['products_name'] . '</a>

 

 

I can't get it to work becuase my sql skills suck

[dailce]

tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, s.status, s.specials_new_products_price, p.products_price from " . TABLE_PRODUCTS . " p left join " .  TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' order by p.products_date_available desc  limit " . MAX_DISPLAY_NEW_PRODUCTS";

 

This doesn't work where is my code wrong??????

[Guest]

yea I did added the missing fields. Make sure the products_id is present.

tep_db_query("select p2c.categories_id, p.products_id, p.products_image, p.products_tax_class_id, s.status, s.specials_new_products_price, p.products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on (p.products_id=p2c.products_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' order by p.products_date_available desc limit " . MAX_DISPLAY_NEW_PRODUCTS";

[dailce]

OK I got this it doesn't give me an error but I still can't get the cPath - here bits of the code

 

tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, s.status, 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 where p.products_id = p2c.products_id and p2c.categories_id = p2c.categories_id and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);



tep_get_path($new_products['categories_id'])

[Guest]

where's the other code to fetch the array

 

if( $new_products = tep_db_fetch_array($query_result) ) {
$my_cpath = tep_get_path($new_products['categories_id']) 
}

[dailce]

yeah I got that but it still won't work. I think the error might be from the intial query.

 

$sql_query[0] = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, s.status, 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 where p.products_id = p2c.products_id and p2c.categories_id = p2c.categories_id and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

.
.
.
.

$new_products_query =  $sql_query[0];

 while ($new_products = tep_db_fetch_array($new_products_query)) 
 {		 echo tep_get_path($new_products['categories_id']) . ' <br>';

.
.
.
.
.

[Guest]

:D why you changed the query The one I posted didn't work? And replace the "while" with "if"