repeat items in both featured items and what's new

[buildingblocks]

Hi

Is there any way to keep items that are set for featured from also showing in the what's new center box? Too often items show up in both places at the same time.

Thanks

[Ajeh]

You could customize, using your templates and overrides, the file:
/includes/modules/new_products.php

the two SELECT statements:

  $new_products_query = "select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name,
                                p.products_date_added, p.products_price, p.products_type, p.master_categories_id
                           from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
                           where p.products_id = pd.products_id
                           and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                           and   p.products_status = 1 " . $display_limit . "
                           and p.products_id not in (select products_id from " . TABLE_FEATURED . ")";

    $new_products_query = "select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name,
                                  p.products_date_added, p.products_price, p.products_type, p.master_categories_id
                           from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
                           where p.products_id = pd.products_id
                           and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                           and p.products_status = 1
                           and p.products_id in (" . $list_of_products . ")" . "
                           and p.products_id not in (select products_id from " . TABLE_FEATURED . ")";

[buildingblocks]

perfect! Thank you. That worked.

One step further, is there a way to keep the items that are being displayed in the what's new side box from also being displaying in the what's new center box at the same time?

I have the featured products up top below my head banner. I have the what's new side box at the top of the right column and the whats new centerbox is at the bottom below the home page content. So when the what's new products display at the top right column I don't want those to ever be the same as what is displayed in the what's new centerbox. Is that possible too?

[buildingblocks]

or if post 3 is not possible then is it possible to tell the what's new side box and what's new centerbox what category/categories to pick from or exclude?

[buildingblocks]

I remembered back to another thread to exclude categories which was done by adding

and p.master_categories_id != 52

where 52 is the category number and change that to your desired cat ID

When I have a lot of categories to exclude how would the code be constructed to include only one or two categories which would automatically exclude all other categories?

[Ajeh]

I have the what's new side box at the top of the right column and the whats new centerbox is at the bottom below the home page content. So when the what's new products display at the top right column I don't want those to ever be the same as what is displayed in the what's new centerbox. Is that possible too?

Customize the file, using your templates and overrides, for:
/includes/templates/template_default/templates/sideboxes/tpl_whats_new.php

with the code in Red:

// bof: block New Products in sidebox from centerbox
// get new_product_skip in centerbox
global $new_product_skip;
$new_product_skip = '';
// eof: block New Products in sidebox from centerbox

  while (!$random_whats_new_sidebox_product->EOF) {

// bof: block New Products in sidebox from centerbox
    $new_product_skip .= $random_whats_new_sidebox_product->fields['products_id'];
    $random_whats_new_sidebox_product->MoveNextRandom();
    if (!$random_whats_new_sidebox_product->EOF) {
      $new_product_skip .= ', ';
    }
// eof: block New Products in sidebox from centerbox

and the file:
/includes/modules/new_products.php

// bof: block New Products in sidebox from centerbox
global $new_product_skip;
//echo 'New Product Skip: ' . $new_product_skip . '<br>';

if ( (($manufacturers_id > 0 && $_GET['filter_id'] == 0) || $_GET['music_genre_id'] > 0 || $_GET['record_company_id'] > 0) || (!isset($new_products_category_id) || $new_products_category_id == '0') ) {
  $new_products_query = "select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name,
                                p.products_date_added, p.products_price, p.products_type, p.master_categories_id
                           from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
                           where p.products_id = pd.products_id
                           and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                           and   p.products_status = 1 " . $display_limit . "
and p.products_id not in (select products_id from " . TABLE_FEATURED . ")" . "
and p.products_id not in (" . $new_product_skip . ")";
} else {
  // get all products and cPaths in this subcat tree
  $productsInCategory = zen_get_categories_products_list( (($manufacturers_id > 0 && $_GET['filter_id'] > 0) ? zen_get_generated_category_path_rev($_GET['filter_id']) : $cPath), false, true, 0, $display_limit);

  if (is_array($productsInCategory) && sizeof($productsInCategory) > 0) {
    // build products-list string to insert into SQL query
    foreach($productsInCategory as $key => $value) {
      $list_of_products .= $key . ', ';
    }
    $list_of_products = substr($list_of_products, 0, -2); // remove trailing comma

    $new_products_query = "select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name,
                                  p.products_date_added, p.products_price, p.products_type, p.master_categories_id
                           from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
                           where p.products_id = pd.products_id
                           and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                           and p.products_status = 1
                           and p.products_id in (" . $list_of_products . ")" . "
and p.products_id not in (select products_id from " . TABLE_FEATURED . ")" . "
and p.products_id not in (" . $new_product_skip . ")";
  }
}
// eof: block New Products in sidebox from centerbox

[buildingblocks]

I'll give this a try in the morning and come back to let you know how I make out. Thank you very much for your help.

[buildingblocks]

altering /sideboxes/tpl_whats_new.php went fine


I'm having a problem with /includes/modules/new_products.php

When I add the code and refresh the page the right column and what's new centerbox disappear showing this error message on home page

WARNING: An Error occurred, please refresh the page and try again.

cache debug file shows

[28-Jun-2012 14:00:19] PHP Fatal error:  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 ')' at line 10 :: select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name,
                                p.products_date_added, p.products_price, p.products_type, p.master_categories_id
                           from zen_products p, zen_products_description pd
                           where p.products_id = pd.products_id
                           and pd.language_id = '1'
                                                     and p.products_quantity > 0
                                                     and p.master_categories_id != 465
                           and   p.products_status = 1  and p.products_date_added >=20120229
                                                     and p.products_id not in (select products_id from zen_featured)
and p.products_id not in () in /home/USER_NAME/public_html/includes/classes/db/mysql/query_factory.php on line 101 

I think I got the extra code in there correctly. I tried a number of times and get the same results.

This is the contents of the /includes/modules/new_products.php

<?php
/**
 * new_products.php module
 *
 * @package modules
 * @copyright Copyright 2003-2008 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: new_products.php 8730 2008-06-28 01:31:22Z drbyte $
 */
if (!defined('IS_ADMIN_FLAG')) {
  die('Illegal Access');
}

// initialize vars
$categories_products_id_list = '';
$list_of_products = '';
$new_products_query = '';

$display_limit = zen_get_new_date_range();
// bof: block New Products in sidebox from centerbox
global $new_product_skip;
//echo 'New Product Skip: ' . $new_product_skip . '<br>';
if ( (($manufacturers_id > 0 && $_GET['filter_id'] == 0) || $_GET['music_genre_id'] > 0 || $_GET['record_company_id'] > 0) || (!isset($new_products_category_id) || $new_products_category_id == '0') ) {
  $new_products_query = "select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name,
                                p.products_date_added, p.products_price, p.products_type, p.master_categories_id
                           from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
                           where p.products_id = pd.products_id
                           and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                                                     and p.products_quantity > 0
                                                     and p.master_categories_id != 465
                           and   p.products_status = 1 " . $display_limit . "
                                                     and p.products_id not in (select products_id from " . TABLE_FEATURED . ")" . "
and p.products_id not in (" . $new_product_skip . ")";
} else {
  // get all products and cPaths in this subcat tree
  $productsInCategory = zen_get_categories_products_list( (($manufacturers_id > 0 && $_GET['filter_id'] > 0) ? zen_get_generated_category_path_rev($_GET['filter_id']) : $cPath), false, true, 0, $display_limit);

  if (is_array($productsInCategory) && sizeof($productsInCategory) > 0) {
    // build products-list string to insert into SQL query
    foreach($productsInCategory as $key => $value) {
      $list_of_products .= $key . ', ';
    }
    $list_of_products = substr($list_of_products, 0, -2); // remove trailing comma

    $new_products_query = "select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name,
                                  p.products_date_added, p.products_price, p.products_type, p.master_categories_id
                           from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
                           where p.products_id = pd.products_id
                           and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                                                     and p.products_quantity > 0
                                                     and p.master_categories_id != 465
                           and p.products_status = 1
                           and p.products_id in (" . $list_of_products . ")" . "
                           and p.products_id not in (select products_id from " . TABLE_FEATURED . ")" . "
and p.products_id not in (" . $new_product_skip . ")";
  }
}
// eof: block New Products in sidebox from centerbox

if ($new_products_query != '') $new_products = $db->ExecuteRandomMulti($new_products_query, MAX_DISPLAY_NEW_PRODUCTS);

$row = 0;
$col = 0;
$list_box_contents = array();
$title = '';

$num_products_count = ($new_products_query == '') ? 0 : $new_products->RecordCount();

// show only when 1 or more
if ($num_products_count > 0) {
  if ($num_products_count < SHOW_PRODUCT_INFO_COLUMNS_NEW_PRODUCTS || SHOW_PRODUCT_INFO_COLUMNS_NEW_PRODUCTS == 0 ) {
    $col_width = floor(100/$num_products_count);
  } else {
    $col_width = floor(100/SHOW_PRODUCT_INFO_COLUMNS_NEW_PRODUCTS);
  }

  while (!$new_products->EOF) {
    $products_price = zen_get_products_display_price($new_products->fields['products_id']);
    if (!isset($productsInCategory[$new_products->fields['products_id']])) $productsInCategory[$new_products->fields['products_id']] = zen_get_generated_category_path_rev($new_products->fields['master_categories_id']);

    $list_box_contents[$row][$col] = array('params' => 'class="centerBoxContentsNew centeredContent back"' . ' ' . 'style="width:' . $col_width . '%;"',
    'text' => (($new_products->fields['products_image'] == '' and PRODUCTS_IMAGE_NO_IMAGE_STATUS == 0) ? '' : '<a href="' . zen_href_link(zen_get_info_page($new_products->fields['products_id']), 'cPath=' . $productsInCategory[$new_products->fields['products_id']] . '&products_id=' . $new_products->fields['products_id']) . '">' . zen_image(DIR_WS_IMAGES . $new_products->fields['products_image'], $new_products->fields['products_name'], IMAGE_PRODUCT_NEW_WIDTH, IMAGE_PRODUCT_NEW_HEIGHT) . '</a><br />') . '<a href="' . zen_href_link(zen_get_info_page($new_products->fields['products_id']), 'cPath=' . $productsInCategory[$new_products->fields['products_id']] . '&products_id=' . $new_products->fields['products_id']) . '">' . $new_products->fields['products_name'] . '</a><br />' . $products_price);

    $col ++;
    if ($col > (SHOW_PRODUCT_INFO_COLUMNS_NEW_PRODUCTS - 1)) {
      $col = 0;
      $row ++;
    }
    $new_products->MoveNextRandom();
  }

  if ($new_products->RecordCount() > 0) {
    if (isset($new_products_category_id) && $new_products_category_id != 0) {
      $category_title = zen_get_categories_name((int)$new_products_category_id);
      $title = '<h2 class="centerBoxHeading">' . sprintf(TABLE_HEADING_NEW_PRODUCTS, strftime('%B')) . ($category_title != '' ? ' - ' . $category_title : '' ) . '</h2>';
    } else {
      $title = '<h2 class="centerBoxHeading">' . sprintf(TABLE_HEADING_NEW_PRODUCTS, strftime('%B')) . '</h2>';
    }
    $zc_show_new_products = true;
  }
}
?>

[Ajeh]

Do you have the New Products sidebox on the Left or the Right?

This only works if the New Products sidebox is on the left ...

[buildingblocks]

right side ........ :-( ........ LOL

If I have the new products sidebox on the left then that means the categories have to move down OR the new products side box moves down below cats, which then ends up along side the what's new center box. My aim is new products sidebox top of right column.

Thank you for spending time.