Excluding a product or category from best selling list

[fontgarden]

I got a MySQL error when trying the above fix:

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'p.product_is_free != 1 order by p.products_ordered ' at line 10
in:
[select distinct p.products_id, pd.products_name, p.products_ordered                              from products p, products_description pd, products_to_categories p2c, categories c                              where p.products_status = '1'                              and p.products_ordered > 0                              and p.products_id = pd.products_id                              and pd.language_id = '1'                              and p.products_id = p2c.products_id                              and p2c.categories_id = c.categories_id                              and '67' in (c.categories_id, c.parent_id) 				     p.product_is_free != 1                              order by p.products_ordered desc, pd.products_name                              limit 20]

What did I do wrong?

[sweetikins]

Let's say you want to toss out all products in categories_id 42 ...

That would now use the master_categories_id 42 in the products table so you should be able to use:

$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.master_categories_id != 42
                             order by p.products_ordered desc, pd.products_name
                             limit " . MAX_DISPLAY_BESTSELLERS; 

Note: this assumes all products that should not display use the same Category and that Category is "owns" these Products which would reflect that in that the master_categories_id of all of these Products would be the same ...

In this example:

                             and p.master_categories_id != 42 

this is the key to the block ...

Hiya, i was wondering if it is possible to exclude certain products for the bestsellers list? there is 10 of them and they are in different catagories. They where givin as freebies for 3 days before going up for sale and so there for they have taken up all the space on the bestsellers box

[Ajeh]

Let's say you didn't want products_id 199 and 26 to show in the Best sellers sidebox ...

You could customize the best_sellers.php sidebox module with something like:

    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 (199, 26)" . "

                             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 (199, 26)" . "

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

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

[sweetikins]

Thank you so much for the reply, i will give it a go and post back with a result xoxo

[sweetikins]

YAYYYYYYYYYYYY ok all fixed thank you ever so much

[Ajeh]

You are most welcome ... thanks for the update that this worked for you ...

[Fireflyz]

Hi Ajeh,
I followed your directions below and I see no changes to the bestseller sidebox. I did use the override and the products are not free.

zen 1.3.8

Thank you for your help!

Let's say you didn't want products_id 199 and 26 to show in the Best sellers sidebox ...

You could customize the best_sellers.php sidebox module with something like:

    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 (199, 26)" . "

                             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 (199, 26)" . "

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

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

[Ajeh]

Check the products_id for the Products that you want to exclude and make sure you have them where you see the 199, 26 ...

Check that your file actually is updated on the server with these changes in your:
/includes/modules/sideboxes/your_template_dir/best_sellers.php

[Ajeh]

NOTE: there are 2 places in the code for the products_id to be set ...

[Fireflyz]

Ajeh,
I have uploaded it to the correct folder and made the changes in two section. Below is the complete code. Could it because the categories are under a hidden category? The categories are setup like this:
Categories / Products - Top > Brand Names (HIDDEN) > Categories (52, 43)

Thanks a lot!

<?php
/**
 * best_sellers sidebox - displays selected number of (usu top ten) best selling products
 *
 * @package templateSystem
 * @copyright Copyright 2003-2005 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: best_sellers.php 2718 2005-12-28 06:42:39Z drbyte $
 */

// test if box should display
  $show_best_sellers= false;

  if (isset($_GET['products_id'])) {
    if (isset($_SESSION['customer_id'])) {
      $check_query = "select count(*) as count
                      from " . TABLE_CUSTOMERS_INFO . "
                      where customers_info_id = '" . (int)$_SESSION['customer_id'] . "'
                      and global_product_notifications = '1'";

      $check = $db->Execute($check_query);

      if ($check->fields['count'] > 0) {
        $show_best_sellers= true;
      }
    }
  } else {
    $show_best_sellers= true;
  }

  if ($show_best_sellers == true) {
    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 (52, 43)" . "

                             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 (52, 43)" . "

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

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

    if ($best_sellers->RecordCount() >= MIN_DISPLAY_BESTSELLERS) {
      $title =  BOX_HEADING_BESTSELLERS;
      $box_id =  'bestsellers';
      $rows = 0;
      while (!$best_sellers->EOF) {
        $rows++;
        $bestsellers_list[$rows]['id'] = $best_sellers->fields['products_id'];
        $bestsellers_list[$rows]['name']  = $best_sellers->fields['products_name'];
        $best_sellers->MoveNext();
      }

      $title_link = false;
      require($template->get_template_dir('tpl_best_sellers.php',DIR_WS_TEMPLATE, $current_page_base,'sideboxes'). '/tpl_best_sellers.php');
      $title =  BOX_HEADING_BESTSELLERS;
      require($template->get_template_dir($column_box_default, DIR_WS_TEMPLATE, $current_page_base,'common') . '/' . $column_box_default);
    }
  }
?>