Problem showing categories

[htsoft]

Good morning,

 

my name is Roberto and i', trying to create an e-commerce with OsCommerce. Actually I'm using OsCommerce 2.3.3 and this is the link to the store:

 

http://www.mymilitaria-eshop.com/oscommerce

 

I'm in trouble with the categories box.

 

I have two main categories:

 

Postcard (Cartoline)

Big Save Corner (Occasioni)

 

When a user clicks on Postcards OSCommerce shows the categories with images and new products for the current month.

When a user clicks on Big Save Corner OSCommerce shows the message: There are no products available in this category, but if you check the categories box you can see the categories (they all have an image) and each sub category has one or more products (with images).

 

Is it normal? Or if not how can it be solved?

 

Kindly,

Roberto

[♥bruyndoncx]

it is normal, in index.php your big save corner is a 'nested' category.

 

if you want to show the items in the subcategory too (children), you have to amend the query on top to also include products where the parent_id of the categories_id is the $current_category_id

if you want to show grandchildren, it becomes more complex, if you want grandgrandchildren ...

 

so if you find

categories_id = $current_category_id

 

make it

(categories_id = $current_category_id or parent_id = $current_category_id )

 

Please note, I'm doing this from memory, this is pseudo code, you need to respect the exact syntax used and make the similar clause for parent_id

[htsoft]

Thank you,

 

I will try this solution, but i don't understand why, if Postcards and Big Save Corner have the same parent_id and have the same sub categories all filled with products the first works properly and the second simply does not work.

 

Roberto

[♥14steve14]

This may seem like a silly thing to say, but do you have a mix of products and sub categories in a category, as this sometimes stops products showing.

[wyattstorch42]

Just in case anyone else is Googling this problem, this is how I solved it in version 2.3.3.

 

1. Create a backup of it, then open index.php in a plaintext editor.

-------------------------------------------------------

 

2. Find this line:

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";

 

Replace it with this line:

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c join categories c on c.categories_id = p2c.categories_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and (p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' or p2c.parent_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "')";

-------------------------------------------------------

 

3. Find this line:

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

 

Replace it with this line:

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c join categories c on c.categories_id = p2c.categories_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and (p2c.categories_id = '" . (int)$current_category_id . "' or p2c.parent_id = '" . (int)$current_category_id . "')";

-------------------------------------------------------

 

4. Find this line:

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

 

Replace it with this line:

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c join categories c on c.categories_id = p2c.categories_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and (p2c.categories_id = '" . (int)$current_category_id . "' or c.parent_id = '" . (int)$current_category_id . "')";

-------------------------------------------------------

 

5. Save and reupload index.php, and test it.

 

 

(Note: This only works for immediate descendants, not "grandchildren" or "great-grandchildren" and so on for categories. That would require a considerably more complex query.)