Matching Product Categories

[spooks]

Following requests I`ve created this little mod.

 

This contribution adds a info box similar to 'also purchased' at the bottom of the product_info page showing all matching categories for the displayed product, with category images, category name & category description (if you have that). Clicking on the image or name will take you to that category.

 

Written on osC2.2 rc1 with PHP5 and has been tested on PHP 4 & 5, SQL 4 & 5, osC 2.2 ms2 & rc1 and is register_globals compatible.

 

Contrib can be downloaded at http://addons.oscommerce.com/info/6010

Edited June 15, 2008 by spooks

[spooks]

I forgot to mention, the box is only displayed if the product is listed in more than one category.

[Guest]

Hi spooks, great contribution, could you help me adapt it for my site? I have installed some code to enable me to assign each category to a group (called manufacturers2_id in categories table of database). Basically what I want to do is get the manufacturers2_id of the category currently being viewed and then display a list of all the other categories that share the same manufacturers2_id.

 

I tried changing

if (isset($HTTP_GET_VARS['products_id'])) {

to

if (isset($HTTP_GET_VARS['manufacturers2_id'])) {

 

and put

include(DIR_WS_MODULES . FILENAME_ALSO_PURCHASED_PRODUCTS);

into product_listing.php but that didn't work. Help appreciated.

[spooks]

Hi spooks, great contribution, could you help me adapt it for my site? I have installed some code to enable me to assign each category to a group (called manufacturers2_id in categories table of database). Basically what I want to do is get the manufacturers2_id of the category currently being viewed and then display a list of all the other categories that share the same manufacturers2_id.

 

I tried changing

if (isset($HTTP_GET_VARS['products_id'])) {

to

if (isset($HTTP_GET_VARS['manufacturers2_id'])) {

 

and put

include(DIR_WS_MODULES . FILENAME_ALSO_PURCHASED_PRODUCTS);

into product_listing.php but that didn't work. Help appreciated.

 

It might be possible to do this with a single query, but my hangeover stops me working that out right now, still the existing query provides a list of matching categories by product, what you want is to further match that with manufacturers2_id by category.

 

Try this (in matching_products_categories.php)

 

Replace:

 

$categories_query = tep_db_query("select c.categories_id, cd.categories_name, " . ($cat_desc ? 'cd.categories_description, ' : '') . "c.parent_id, c.categories_image from " . TABLE_PRODUCTS_TO_CATEGORIES . " pc left join " . TABLE_CATEGORIES . " c on pc.categories_id = c.categories_id left join " . TABLE_CATEGORIES_DESCRIPTION . " cd on pc.categories_id = cd.categories_id where pc.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name");

 

With

 

$find_categories_query = tep_db_query("select c.categories_id, c.manufacturers2_id, cd.categories_name, " . ($cat_desc ? 'cd.categories_description, ' : '') . "c.parent_id, c.categories_image from " . TABLE_PRODUCTS_TO_CATEGORIES . " pc left join " . TABLE_CATEGORIES . " c on pc.categories_id = c.categories_id left join " . TABLE_CATEGORIES_DESCRIPTION . " cd on pc.categories_id = cd.categories_id where pc.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name");

while ($category = tep_db_fetch_array($find_categories_query)) {
$categories_query = tep_db_query("select c.categories_id, cd.categories_name, " . ($cat_desc ? 'cd.categories_description, ' : '') . "c.parent_id, c.categories_image from " . TABLE_CATEGORIES . " c left join " . TABLE_CATEGORIES_DESCRIPTION . " cd on c.categories_id = cd.categories_id where c.manufacturers2_id = '" . $category['manufacturers2_id'] . "' and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name"); }

 

Let me know how you get on.

[Guest]

It might be possible to do this with a single query, but my hangeover stops me working that out right now, still the existing query provides a list of matching categories by product, what you want is to further match that with manufacturers2_id by category.

 

Try this (in matching_products_categories.php)

 

Replace:

 

$categories_query = tep_db_query("select c.categories_id, cd.categories_name, " . ($cat_desc ? 'cd.categories_description, ' : '') . "c.parent_id, c.categories_image from " . TABLE_PRODUCTS_TO_CATEGORIES . " pc left join " . TABLE_CATEGORIES . " c on pc.categories_id = c.categories_id left join " . TABLE_CATEGORIES_DESCRIPTION . " cd on pc.categories_id = cd.categories_id where pc.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name");

 

With

 

$find_categories_query = tep_db_query("select c.categories_id, c.manufacturers2_id, cd.categories_name, " . ($cat_desc ? 'cd.categories_description, ' : '') . "c.parent_id, c.categories_image from " . TABLE_PRODUCTS_TO_CATEGORIES . " pc left join " . TABLE_CATEGORIES . " c on pc.categories_id = c.categories_id left join " . TABLE_CATEGORIES_DESCRIPTION . " cd on pc.categories_id = cd.categories_id where pc.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name");

while ($category = tep_db_fetch_array($find_categories_query)) {
$categories_query = tep_db_query("select c.categories_id, cd.categories_name, " . ($cat_desc ? 'cd.categories_description, ' : '') . "c.parent_id, c.categories_image from " . TABLE_CATEGORIES . " c left join " . TABLE_CATEGORIES_DESCRIPTION . " cd on c.categories_id = cd.categories_id where c.manufacturers2_id = '" . $category['manufacturers2_id'] . "' and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name"); }

 

Let me know how you get on.

 

 

Hi, thanks for getting back to me. I tried what you suggested and it didn't work because I don't think I explained what I wanted to do properly.

You said in your last post "the existing query provides a list of matching categories by product, what you want is to further match that with manufacturers2_id by category." No, what I didn't explain was that manufacturers2 is for categories not products, so each category is assigned a manufacturer. I know it sounds strange but its for putting subcategories into groups/families because each of my categories has lots of subcategories.

 

I want to run this code on product_listing page (not product_info) and there is no product-id to fetch on product_listing (because there are numerous different products). I want the code to fetch the category id of the category currently being viewed on product_listing and then find what manufacturers2_id is assigned to it and then display all the other categories_id's that are assigned the same manufacturers2_id. I hope this makes sense, it is surprisingly hard to explain.

 

David.

Edited June 22, 2008 by Marker

[spooks]

I wish people would make things clear from the start, if I had a pound for every time someone says, 'no, what I really ment was' I`d be rich. Instead it just wastes a lot of time.

 

product_listing.php is called from index.php & uses queries generated in that file, so is not suitable for modding to do somthing quite different.

 

I don`t know why your putting include(DIR_WS_MODULES . FILENAME_ALSO_PURCHASED_PRODUCTS); as thats for products & totally off point.

 

You could use a modded matching_products_categories.php and call that from index .php putting the call include(DIR_WS_MODULES . 'matching_products_categories.php'); somewhere suitable in index.php.

 

This time in matching_products_categories.php replace:

 

if (isset($HTTP_GET_VARS['products_id'])) {
$categories_query = tep_db_query("select c.categories_id, cd.categories_name, " . ($cat_desc ? 'cd.categories_description, ' : '') . "c.parent_id, c.categories_image from " . TABLE_PRODUCTS_TO_CATEGORIES . " pc left join " . TABLE_CATEGORIES . " c on pc.categories_id = c.categories_id left join " . TABLE_CATEGORIES_DESCRIPTION . " cd on pc.categories_id = cd.categories_id where pc.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name");

 

 

With:

 

if (tep_not_null($current_category_id)) {
$find_categories_query = tep_db_query("select manufacturers2_id from " . TABLE_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'");

$category = tep_db_fetch_array($find_categories_query)); 
$categories_query = tep_db_query("select c.categories_id, cd.categories_name, " . ($cat_desc ? 'cd.categories_description, ' : '') . "c.parent_id, c.categories_image from " . TABLE_CATEGORIES . " c left join " . TABLE_CATEGORIES_DESCRIPTION . " cd on c.categories_id = cd.categories_id where c.manufacturers2_id = '" . $category['manufacturers2_id'] . "' and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name");

 

:rolleyes: :rolleyes: :rolleyes:

[Guest]

Sorry about that. Still no luck with the new code though, no error messages but the page stops loading at the point where include(DIR_WS_MODULES . 'matching_products_categories.php'); is called. Any suggestions?

[spooks]

Sorry about that. Still no luck with the new code though, no error messages but the page stops loading at the point where include(DIR_WS_MODULES . 'matching_products_categories.php'); is called. Any suggestions?

 

 

Don't kmow why you get nothing!!, There is a syntax error, otherwise I can see no problem

 

the line:

 

$category = tep_db_fetch_array($find_categories_query));

 

Should be:

 

$category = tep_db_fetch_array($find_categories_query);

 

:huh:

[Guest]

Don't kmow why you get nothing!!, There is a syntax error, otherwise I can see no problem

 

the line:

 

$category = tep_db_fetch_array($find_categories_query));

 

Should be:

 

$category = tep_db_fetch_array($find_categories_query);

 

:huh:

 

Sweet...

That worked a treat. Thanks man, I really appreciate this.