Excluding Category from Bestsellers Sidebox

[jmberman]

Zen 1.5.6c


Trying to hide category 34 from Bestsellers list:

Adding this code to my includes/templates/template_default/sideboxes tpl_best_sellers.php file didn't make a change:

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

if (isset($current_category_id) && ($current_category_id > 0)) {
$best_sellers_query = "select distinct p.products_id, pd.products_name, p.products_ordered
from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, "
. TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c
where (p.products_status = '1'
and p.products_ordered > 0
and p.products_id = pd.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p.products_id = p2c.products_id
and p2c.categories_id = c.categories_id
and '" . (int)$current_category_id . "' in (c.categories_id, c.parent_id))
and p.products_id not in (34)" . "

order by p.products_ordered desc, pd.products_name
limit " . MAX_DISPLAY_BESTSELLERS;

$best_sellers = $db->Execute($best_sellers_query);

} else {
$best_sellers_query = "select distinct p.products_id, pd.products_name, p.products_ordered
from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
where (p.products_status = '1'
and p.products_ordered > 0
and p.products_id = pd.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "')
and p.products_id not in (34)" . "

order by p.products_ordered desc, pd.products_name
limit " . MAX_DISPLAY_BESTSELLERS;

$best_sellers = $db->Execute($best_sellers_query);
}

[mc12345678]

If by "adding" you are referring to the last criteria of the where statement of: p.products_id not in (34)

Then no, that won't accomplish what was described as desired. That will remove the product that has products_id of 34. What is desired to address is either the master_categories_id (a single value assigned to every product such that the product is tied to a single category) or the categories_id where any record that is assigned or linked to the category is omitted. The first field is a factor of the products table, the second is a factor of the products_to_categories table.

So you could have:

AND p.master_categories_id NOT IN (34)

Or to broaden the exclusion list:

AND p2c.categories_id NOT IN (34)

Although I just realized that won't fully work for the second part of that code. Follow up message coming.

[jmberman]

Adding the actual product id's isn't working either:

if (isset($current_category_id) && ($current_category_id > 0)) {
$best_sellers_query = "select distinct p.products_id, pd.products_name, p.products_ordered
from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, "
. TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c
where (p.products_status = '1'
and p.products_ordered > 0
and p.products_id = pd.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p.products_id = p2c.products_id
and p2c.categories_id = c.categories_id
and '" . (int)$current_category_id . "' in (c.categories_id, c.parent_id))
and p.products_id not in (347, 440, 941, 942)" . "

order by p.products_ordered desc, pd.products_name
limit " . MAX_DISPLAY_BESTSELLERS;

$best_sellers = $db->Execute($best_sellers_query);

} else {
$best_sellers_query = "select distinct p.products_id, pd.products_name, p.products_ordered
from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
where (p.products_status = '1'
and p.products_ordered > 0
and p.products_id = pd.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "')
and p.products_id not in (347, 440, 941, 942)" . "

[jmberman]

Using AND p.master_categories_id NOT IN (34)
isn't either

[mc12345678]

So in the first part of the code, the current category is known and it is known to not be the root of the store. As a result the code within the if statement needs more filtering so that only the product associated with the categories at and below the current category is returned. So my suggestion above for either type should work as described.

For the else part or second set of code, it again depends on the desired result: omit product that have the category as the "home" category, or omit any and all product that are assigned and/or linked to the category?

I would say that in the second query of the original code that removal should simply be of the product that are directly assigned to the category. This makes sense to me unless there is some reason that product shown in that category are actually assigned elsewhere (for sale discount purposes) and only displayed in this one category and not linked to others. Again, need to consider your setup and what is really wanted.
So I would say that to address the link condition within the second query, then it would need a little rewrite:

AND p.products_id NOT IN (SELECT products_id FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " WHERE categories_id  = 34)

This is untested, but seems right.

As to the query having no effect even if the specific product is excluded indicates to me that the query statement is likely not being processed as part of operation.

Where it has been placed, there is some form of $db->Execute or ExecuteRandom of that query and there is no reassignment of $best_sellers_query to anything else before the query is executed?

[jmberman]

The main character is 34, and yea I'm trying to make all subcategories in that category not display under best sellers

This isn't working:

if (isset($current_category_id) && ($current_category_id > 0)) {
$best_sellers_query = "select distinct p.products_id, pd.products_name, p.products_ordered
from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, "
. TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c
where (p.products_status = '1'
and p.products_ordered > 0
and p.products_id = pd.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p.products_id = p2c.products_id
and p2c.categories_id = c.categories_id
and '" . (int)$current_category_id . "' in (c.categories_id, c.parent_id))
AND p.products_id NOT IN (SELECT products_id FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " WHERE categories_id = 34)

order by p.products_ordered desc, pd.products_name
limit " . MAX_DISPLAY_BESTSELLERS;

$best_sellers = $db->Execute($best_sellers_query);

} else {
$best_sellers_query = "select distinct p.products_id, pd.products_name, p.products_ordered
from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
where (p.products_status = '1'
and p.products_ordered > 0
and p.products_id = pd.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "')
AND p.products_id NOT IN (SELECT products_id FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " WHERE categories_id = 34)

order by p.products_ordered desc, pd.products_name
limit " . MAX_DISPLAY_BESTSELLERS;

$best_sellers = $db->Execute($best_sellers_query);
}

[jmberman]

The full sidebox file shows as:

<?php
/**
* Side Box Template
*
* @package templateSystem
* @copyright Copyright 2003-2018 Zen Cart Development Team
* @copyright Portions Copyright 2003 osCommerce
* @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
* @version $Id: Drbyte Sun Jan 7 21:28:50 2018 -0500 Modified in v1.5.6 $
*/
$content = '';
$content .= '<div id="' . str_replace('_', '-', $box_id . 'Content') . '" class="sideBoxContent">' . "\n";
$content .= '<div class="wrapper">' . "\n" . '<ol>' . "\n";
for ($i=1, $j=sizeof($bestsellers_list); $i<$j; $i++) {
$content .= '<li><a href="' . zen_href_link(zen_get_info_page($bestsellers_list[$i]['id']), 'products_id=' . $bestsellers_list[$i]['id']) . '">' . zen_trunc_string($bestsellers_list[$i]['name'], BEST_SELLERS_TRUNCATE, BEST_SELLERS_TRUNCATE_MORE) . '</a></li>' . "\n";
}
$content .= '</ol>' . "\n";
$content .= '</div>' . "\n";
$content .= '</div>';

if (isset($current_category_id) && ($current_category_id > 0)) {
$best_sellers_query = "select distinct p.products_id, pd.products_name, p.products_ordered
from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, "
. TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c
where (p.products_status = '1'
and p.products_ordered > 0
and p.products_id = pd.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p.products_id = p2c.products_id
and p2c.categories_id = c.categories_id
and '" . (int)$current_category_id . "' in (c.categories_id, c.parent_id))
AND p.products_id NOT IN (SELECT products_id FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " WHERE categories_id = 34)

order by p.products_ordered desc, pd.products_name
limit " . MAX_DISPLAY_BESTSELLERS;

$best_sellers = $db->Execute($best_sellers_query);

} else {
$best_sellers_query = "select distinct p.products_id, pd.products_name, p.products_ordered
from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
where (p.products_status = '1'
and p.products_ordered > 0
and p.products_id = pd.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "')
AND p.products_id NOT IN (SELECT products_id FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " WHERE categories_id = 34)

order by p.products_ordered desc, pd.products_name
limit " . MAX_DISPLAY_BESTSELLERS;

$best_sellers = $db->Execute($best_sellers_query);
}

[mc12345678]

Well, there is at least one issue with this and three possibly seen.

The first and primary issue is that the reassignment of $best_sellers is occurring after its use. The if statement that is currently at the bottom of the file should be at the top.

Then, even if that is placed there, if this file is overridden in the template structure, then this modification would have no impact.

As for the proper place to put this modification, it would be expected to be in includes/modules/YOUR_TEMPLATE/best_sellers.php which is where the actual processing is performed...

[carlwhat]

jm,
are you familiar with bb code tags? it makes reading your code much easier. there is a link at the bottom of every page (maybe not on the mobile template for the forum).

example:

$best_sellers_query = "select distinct p.products_id, pd.products_name, p.products_ordered
from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
where (p.products_status = '1'
and p.products_ordered > 0 

what you want to do is not that hard. you only say, this is not working. as stated above, are you sure you are modifying the correct file in the correct place? you are using the template default? see post above.

but really, why you are not modifying the code here:

includes/modules/sideboxes/best_sellers.php

that's where the sql is.

[carlwhat]

you are running an sql query, after the sidebox is already generated. look at:

for ($i=1, $j=sizeof($bestsellers_list); $i<$j; $i++) {
$content .= '<li><a href="' . zen_href_link(zen_get_info_page($bestsellers_list[$i]['id']), 'products_id=' . $bestsellers_list[$i]['id']) . '">' . zen_trunc_string($bestsellers_list[$i]['name'], BEST_SELLERS_TRUNCATE, BEST_SELLERS_TRUNCATE_MORE) . '</a></li>' . "\n";
} 

thats from your code above. the module that i just posted constructs the array

$bestsellers_list 

and that is what the template is displaying. you are then adding some sql code afterwards that is not going anywhere. you need to modify the $bestseller_list array before it gets assigned to the $content. make sense?

hope that helps.