Display all categories associated with a product

Hi,

I'm looking for a way to put all category names that a poduct is either in or linked to into my product page.

I was going to use php text replacement in the product description section to replace a fixed piece of text (eg COMPATIBILITY_LIST) with the results of a SQL select statement. Here's the link to the nearest thing I've found so far if it helps at all : http://www.zen-cart.com/forum/showthread.php?t=123048.

I can use his code for the page but I'm stuck as to what the SQL would be to get all the category names that a product is in (the original one + any linked categories). I'm a little unsure as to which database table(s) hold the master category associated with a product and which table(s) hold the linked categories and then which table to use to get the category name.

If required, I'm using Zen cart version 1.3.8.

Any help would be much appreciated.

Thanks

Col

No takers?

All I'm looking for is someone to tell me which database tables I need to look at, there probably only 2 (one for categories and one for linked categories) and then maybe what ID or whatever I use to link between them from my product ID.

Col

To get all the categories_name for products_id 12 ...

in phpMyAdmin you can use:

select cd.categories_name from categories_description cd, products_to_categories ptoc where cd.categories_id = ptoc.categories_id and ptoc.products_id = 12;

For within Zen Cart, you would have to translate it ...

Cool, thanks Ajeh.

In case anyone needs it here's the product description section of my product template php file(/includes/templates/my_templat/templates/tpl_product_info_display.php):

<!--bof Product description -->
<?php
$current_categories_list = ""; //database filled category list
$sep = ""; //separator between category names
// categories_description
$sql = "SELECT cd.categories_name
        FROM zen_categories_description cd, zen_products_to_categories ptoc
        WHERE cd.categories_id = ptoc.categories_id
        AND ptoc.products_id = " . (int)$_GET['products_id'];

$categories_list_lookup = $db->Execute($sql);

if ($categories_list_lookup->RecordCount() > 0) { //if categories are returned from the SQL
	while (!$categories_list_lookup->EOF) { //for each category name append it to a string
		$current_categories_list .= $sep . $categories_list_lookup->fields['categories_name'];
		$sep = ", "; //the separator between category names is a comma except for the first time round
		$categories_list_lookup->MoveNext();
	}
}
// use a string replace function to look for %compatibility_list% in the description and replace it with the SQL-driven category list
if ($products_description != '') { ?>
<div id="productDescription" class="productGeneral biggerText"><?php echo stripslashes(str_replace("%compatibility_list%", $current_categories_list, $products_description)); ?></div>
<?php } ?>
<!--eof Product description -->

Hope it helps someone else at some point.

Col

Thanks for the update ...

NOTE: you may want to take the time to see how the code is used to call table names so that you use the same conventions in your custom code ...

Example, for the products table you use:
TABLE_PRODUCTS

Example, for the categories_description table you use:
TABLE_CATEGORIES_DESCRIPTION

This allows for changes to prefixes, table names etc. without having to later update code etc.

I am trying to do similar with my store where I have to order the catergories based on male or female.

I've noticed you used sql to get the categories for the database, but does this need to be done? I have seen various places in the code where a $categories array variable is being used, could you use that instead of repeating the sql query again?

Thanks Ajeh, I've updated my code as required:

<!--bof Product description -->
<?php
$current_categories_list = ""; //database filled category list
$sep = ""; //separator between category names
// categories_description
$sql = "SELECT cd.categories_name
        FROM " . TABLE_CATEGORIES_DESCRIPTION . " cd, " . TABLE_PRODUCTS_TO_CATEGORIES . " ptoc
        WHERE cd.categories_id = ptoc.categories_id
        AND ptoc.products_id = " . (int)$_GET['products_id'];

$categories_list_lookup = $db->Execute($sql);

if ($categories_list_lookup->RecordCount() > 0) { //if categories are returned from the SQL
	while (!$categories_list_lookup->EOF) { //for each category name append it to a string
		$current_categories_list .= $sep . $categories_list_lookup->fields['categories_name'];
		$sep = ", "; //the separator between category names is a comma except for the first time round
		$categories_list_lookup->MoveNext();
	}
}
// use a string replace function to look for %compatibility_list% in the description and replace it with the SQL-driven category list
if ($products_description != '') { ?>
<div id="productDescription" class="productGeneral biggerText"><?php echo stripslashes(str_replace("%compatibility_list%", $current_categories_list, $products_description)); ?></div>
<?php } ?>
<!--eof Product description -->

Upwardtrend, I'm not sure about the categories array but the easiest way I can think of to check what it is is to output the contents of it:

print_r($myarray);

If your online shop is in use at the moment, I suggest you either use a test environment, or try it late at night when nobody will be viewing your wares, or maybe you could try ouputting the array contents with a html around it (<!-- -->) so that it is only viewable in the source code, don't forget to remove it again though.

I suspect that it is an array of sub categories for the category you're currently in (if there are any) or maybe its used for the category menus - but they're just guestimates, maybe Ajeh or someone else has an idea.

Col