Coding advice... show Subcategories' products when browsing main category?

Hi,

I've been trying to figure this one out..... When a category has no products, only sub-categories, we want our web store to display all the products from the sub-categories - on the main category page.
(This way, the sub-categories can act as "filters" - when browsing the main category, the customer will see all the products from the sub-categories, and can click on each sub-category to filter the results).

I know the key is probably in the SQL query.... I know some PHP and have successfully done a bit of custom modding with it already, but alas, I'm in the dark when it comes to SQL.... :blink:

(By the way, I'm using Zen Cart 1.3.0.1 with the "column grid display" mod)

I thought of taking a cue from the SQL query in the "New Products" display routine, since it shows (random) new products from all the subcategories under the current top-level category, but this is not exactly what I want (I want it to show all products from the current-level category downwards)..... and besides I'm not exactly sure how to refit it for the main category browsing code....

So, hopefully someone more familiar with SQL and the Zen Cart code could help me... would be massively appreciated! :thumbsup:

Upgrade to 1.3.7 and post the URL and I'll have a look for you.

Thanks. Actually, upgrading would be a problem for me right now (since I wasn't very smart in making my custom program modding and template changes until now, and they would all be lost... I know, I'll have to clean this up) - but, here are the relevant segments of code, as far as I can tell:

• Original SQL query code for getting all products in a category (which I want to change) - from product_listing.php:

$max_results = (PRODUCT_LISTING_LAYOUT_STYLE=='columns' && PRODUCT_LISTING_COLUMNS_PER_ROW>0) ? (PRODUCT_LISTING_COLUMNS_PER_ROW * (int)(MAX_DISPLAY_PRODUCTS_LISTING/PRODUCT_LISTING_COLUMNS_PER_ROW)) : MAX_DISPLAY_PRODUCTS_LISTING;

$show_submit = zen_run_normal();
$listing_split = new splitPageResults($listing_sql, $max_results, 'p.products_id', 'page');
$how_many = 0;

• SQL query code from new_products.php - I figured that this should help, since it already makes a selection from all the sub-categories of the parent category... however, it is random (and based on a time limit); I want to modify this to show all products from the subcategories of the current one, in a way that would work with product_listing.php:

$display_limit = zen_get_products_new_timelimit();

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
  $new_products_query = "select p.products_id, p.products_image, p.products_tax_class_id, p.products_price, p.products_date_added
                           from " . TABLE_PRODUCTS . " p
                           where p.products_status = 1 " . $display_limit;
} else {
  $new_products_query = "select distinct p.products_id, p.products_image, p.products_tax_class_id, p.products_date_added,
                                           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
                           where p.products_id = p2c.products_id
                           and p2c.categories_id = c.categories_id
                           and c.parent_id = '" . (int)$new_products_category_id . "'
                           and p.products_status = 1 " . $display_limit;
}
$new_products = $db->ExecuteRandomMulti($new_products_query, MAX_DISPLAY_NEW_PRODUCTS);

I would greatly appreciate any help! My boss wants this feature to be implemented ASAP and I'm in the dark ;)

Thanks very much in advance.

Do you have records of your changes? If not, compare your files with a fresh copy of v1.3.0.2 in Winmerge and note the changed files. Then make a custom template structure and copy those files into it. That will allow you to upgrade without overwriting your changes.

You will still have to merge your changes with the new versions if any of your files are listed in the changelog, but that needs to be done with any upgrade.

Yellow1912 has a mod (available from his site) to list all categories and all products on a specific page. I think you would only need to trim down the display criteria to get what you want.

Follow the linky in his signature.