Customised Specials_Index.php - want to Remove the Randomize Feature

[limelites]

I'm hoping someone can help me with this. Way back when we created the www.silkblooms.co.uk website about 18mnths ago (or more) we wanted the "special_index" files to appear in a completely random order. Our thinking was that this would make the home page appear to Google as a constantly changing page with new items appearing there all the time. I'm not sure if that helped our SEO ratings but we're doing well in that respect. We also thought it'd be nice for customers landing on the home page to see a range of random produts.

Now, we just want to put the specials_index back to the way it should be. However, we can't just copy the original file over as there have been too many customisations to this file and we don't want to lose them all. We simply want to remove the random feature and make the products appear in the order from new-old.

Here is a copy of the coding. Can anyone help me replace whatever is causing the products to 'randomize' with a new code that makes them apear in date listed order from new to old?

<?php
/**
 * specials_index module
 *
 * @package modules
 * @copyright Copyright 2003-2007 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: specials_index.php 6424 2007-05-31 05:59:21Z ajeh $
 */
if (!defined('IS_ADMIN_FLAG')) {
  die('Illegal Access');
}

// initialize vars
$categories_products_id_list = '';
$list_of_products = '';
$specials_index_query = '';
$display_limit = '';

$where_str = " AND p.master_categories_id != 349 ";
$where_str .= " AND p.master_categories_id != 350 ";

// m.manufacturers_name     m.manufacturers_id     p.products_quantity     p.products_model     p.manufacturers_id     p.master_categories_id     ".TABLE_MANUFACTURERS . " m

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') ) {
  $specials_index_query = "select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, p.products_quantity,
                           p.products_date_added, p.products_price, p.products_type,  p.products_model,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 " . $where_str . $display_limit;
} 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
    $specials_index_query = "select distinct p.products_id, p.products_image, pd.products_name, p.master_categories_id,  p.products_model, p.products_quantity
                             from (" . TABLE_PRODUCTS . " p
                             left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id
                             left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id )
                             where p.products_id = s.products_id
                             and p.products_id = pd.products_id
                             and p.products_status = '1' and s.status = '1'
                             and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' 
                             and p.products_id in (" . $list_of_products . ")" . $where_str; 
  }
}
if ($specials_index_query != '') $specials_index = $db->ExecuteRandomMulti($specials_index_query, MAX_DISPLAY_SPECIAL_PRODUCTS_INDEX);

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

$num_products_count = ($specials_index_query == '') ? 0 : $specials_index->RecordCount();

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

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


    $soldoutimage = ($specials_index->fields['products_quantity'] > 0) ? '' : '<div class="sold_out_on_image"><a href="'
     . zen_href_link(zen_get_info_page($specials_index->fields['products_id']), 'cPath=' . $productsInCategory[$specials_index->fields['products_id']] . '&products_id='
     . $specials_index->fields['products_id'])
     . '"><img src="http://www.silkblooms.co.uk/includes/templates/freetemplate2/images/sold_out_mine.png" style="position:relative;top:30px;left:7px;border:0;"></a></div>';  


    $specials_index->fields['products_name'] = zen_get_products_name($specials_index->fields['products_id']);
    $list_box_contents[$row][$col] = array('params' => 'class="centerBoxContentsSpecials centeredContent back"' . ' ' . 'style="width:' . $col_width . '%;"',

    'text' => (($specials_index->fields['products_image'] == '' and PRODUCTS_IMAGE_NO_IMAGE_STATUS == 0) ? '' : '<a href="' . zen_href_link(zen_get_info_page($specials_index->fields['products_id']), 'cPath=' . $productsInCategory[$specials_index->fields['products_id']] . '&products_id=' . $specials_index->fields['products_id']) . '">' . zen_image(DIR_WS_IMAGES . $specials_index->fields['products_image'], $specials_index->fields['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br />'.$soldoutimage) . '<a href="' . zen_href_link(zen_get_info_page($specials_index->fields['products_id']), 'cPath=' . $productsInCategory[$specials_index->fields['products_id']] . '&products_id=' . $specials_index->fields['products_id']) . '">'. '</a>' .$specials_index->fields['manufacturers_name'] . $manufacturers_name.''. $specials_index->fields['products_model'] .$products_model .'<br />' .$products_price.'<br /><p>' );

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

  if ($specials_index->RecordCount() > 0) {
    $title = '<h2 class="centerBoxHeading">' . sprintf(TABLE_HEADING_SPECIALS_INDEX, strftime('%B')) . '</h2>';
    $zc_show_specials = true;
  }
}
?>

[niccol]

'the way it should be' displays a random selection of the specials. so what exactly are you trying to do?

You'll be interested in the line:

if ($specials_index_query != '') $specials_index = $db->ExecuteRandomMulti($specials_index_query, MAX_DISPLAY_SPECIAL_PRODUCTS_INDEX)

Possibly changing 'ExeucuteRandomMulti' to 'Execute' will do what you want but until you say what order you actually want them displayed in (date? name? discount? ) then it is hard to say.

[limelites]

'the way it should be' displays a random selection of the specials. so what exactly are you trying to do?

You'll be interested in the line:

if ($specials_index_query != '') $specials_index = $db->ExecuteRandomMulti($specials_index_query, MAX_DISPLAY_SPECIAL_PRODUCTS_INDEX)

Possibly changing 'ExeucuteRandomMulti' to 'Execute' will do what you want but until you say what order you actually want them displayed in (date? name? discount? ) then it is hard to say.

Yeah, I did see that line but wondered how to change it to make it work the way we'd like. I tried it there like this:

if ($specials_index_query != '') $specials_index = $db->Execute($specials_index_query, MAX_DISPLAY_SPECIAL_PRODUCTS_INDEX); 

.... But is seems to just then display one single product (the second oldest product on the site). Refreshing the page a few times and it's always the same product so it removes the "Random" and also the "Multi" part.

So, I tried this:

if ($specials_index_query != '') $specials_index = $db->ExecuteMulti($specials_index_query, MAX_DISPLAY_SPECIAL_PRODUCTS_INDEX); 

.... But this just white's out the all the products and none of them appear at all.


From the sound of the syntax, we would love it to "Execute(Date-old-to-new)Multi"

[niccol]

Well, again it would be handy if you said what you were trying to do exactly. You imply that you want to display the most recent 4 sale items? Is that the ones that were added to the catalog most recently or the ones that were put on sale most recently? What exactly are you trying to do.

But, I did miss that you need to change this line :

    $specials_index->MoveNextRandom();

to this

    $specials_index->MoveNext();

Then you will get more than one :-)

If you want to order them in some way you are going to want to add an ORDER BY phrase to the end of the sql. It might be something like

ORDER BY s.specials_id DESC

which will order by the time that the specials were created.

[limelites]

We're getting somewhere, thank you for this. Just simply removing the word Random from the file gives a list of all products. The only thing is the sort order. It's listing them in old to new. We'd need it to do the opposite, so from new to old.

Just plopping this line at the end of the php gives syntax errors in Dreamweaver ORDER BY s.specials_id DESC

[niccol]

Hmmm, well I actually do not give a hoot what dreamweaver thinks. But that is just my opinion of that software....

Anyway the code chunk will look like this :

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') ) {
  $specials_index_query = "select p.products_id, p.products_image, pd.products_name, p.master_categories_id
                           from (" . TABLE_PRODUCTS . " p
                           left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id
                           left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id )
                           where p.products_id = s.products_id
                           and p.products_id = pd.products_id
                           and p.products_status = '1' and s.status = 1
                           and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' ORDER BY s.specials_date_added ASC";
} 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
    $specials_index_query = "select distinct p.products_id, p.products_image, pd.products_name, p.master_categories_id
                             from (" . TABLE_PRODUCTS . " p
                             left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id
                             left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id )
                             where p.products_id = s.products_id
                             and p.products_id = pd.products_id
                             and p.products_status = '1' and s.status = '1'
                             and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                             and p.products_id in (" . $list_of_products . ") ORDER BY s.specials_date_added ASC"
;
  }
}

Or you can change the ASC to DESC to get it the other way round.

[limelites]

Hi Niccol,

I'm not a big fan of it either but it's usually right when is says there's an error in the code. I've tried the file with that block of code and it's not working, all it does is wipe out all the products so that no products show at all, just a blank white space where the pics should be. Here's the file contents in it's entirety:

<?php
/**
 * specials_index module
 *
 * @package modules
 * @copyright Copyright 2003-2007 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: specials_index.php 6424 2007-05-31 05:59:21Z ajeh $
 */
if (!defined('IS_ADMIN_FLAG')) {
  die('Illegal Access');
}

// initialize vars
$categories_products_id_list = '';
$list_of_products = '';
$specials_index_query = '';
$display_limit = '';

$where_str = " AND p.master_categories_id != 349 ";
$where_str .= " AND p.master_categories_id != 350 ";

// m.manufacturers_name     m.manufacturers_id     p.products_quantity     p.products_model     p.manufacturers_id     p.master_categories_id     ".TABLE_MANUFACTURERS . " m

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') ) {
  $specials_index_query = "select p.products_id, p.products_image, pd.products_name, p.master_categories_id
                           from (" . TABLE_PRODUCTS . " p
                           left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id
                           left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id )
                           where p.products_id = s.products_id
                           and p.products_id = pd.products_id
                           and p.products_status = '1' and s.status = 1
                           and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' ORDER BY s.specials_date_added DESC";
} 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
    $specials_index_query = "select distinct p.products_id, p.products_image, pd.products_name, p.master_categories_id
                             from (" . TABLE_PRODUCTS . " p
                             left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id
                             left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id )
                             where p.products_id = s.products_id
                             and p.products_id = pd.products_id
                             and p.products_status = '1' and s.status = '1'
                             and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                             and p.products_id in (" . $list_of_products . ") ORDER BY s.specials_date_added DESC"
;
  }
}
if ($specials_index_query != '') $specials_index = $db->Execute($specials_index_query, MAX_DISPLAY_SPECIAL_PRODUCTS_INDEX);

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

$num_products_count = ($specials_index_query == '') ? 0 : $specials_index->RecordCount();

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

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


    $soldoutimage = ($specials_index->fields['products_quantity'] > 0) ? '' : '<div class="sold_out_on_image"><a href="'
     . zen_href_link(zen_get_info_page($specials_index->fields['products_id']), 'cPath=' . $productsInCategory[$specials_index->fields['products_id']] . '&products_id='
     . $specials_index->fields['products_id'])
     . '"><img src="http://www.silkblooms.co.uk/includes/templates/freetemplate2/images/sold_out_mine.png" style="position:relative;top:30px;left:7px;border:0;"></a></div>';  


    $specials_index->fields['products_name'] = zen_get_products_name($specials_index->fields['products_id']);
    $list_box_contents[$row][$col] = array('params' => 'class="centerBoxContentsSpecials centeredContent back"' . ' ' . 'style="width:' . $col_width . '%;"',

    'text' => (($specials_index->fields['products_image'] == '' and PRODUCTS_IMAGE_NO_IMAGE_STATUS == 0) ? '' : '<a href="' . zen_href_link(zen_get_info_page($specials_index->fields['products_id']), 'cPath=' . $productsInCategory[$specials_index->fields['products_id']] . '&products_id=' . $specials_index->fields['products_id']) . '">' . zen_image(DIR_WS_IMAGES . $specials_index->fields['products_image'], $specials_index->fields['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br />'.$soldoutimage) . '<a href="' . zen_href_link(zen_get_info_page($specials_index->fields['products_id']), 'cPath=' . $productsInCategory[$specials_index->fields['products_id']] . '&products_id=' . $specials_index->fields['products_id']) . '">'. '</a>' .$specials_index->fields['manufacturers_name'] . $manufacturers_name.''. $specials_index->fields['products_model'] .$products_model .'<br />' .$products_price.'<br /><p>' );

    $col ++;
    if ($col > (SHOW_PRODUCT_INFO_COLUMNS_SPECIALS_PRODUCTS - 1)) {
      $col = 0;
      $row ++;
    }
    $specials_index->MoveNext();
  }

  if ($specials_index->RecordCount() > 0) {
    $title = '<h2 class="centerBoxHeading">' . sprintf(TABLE_HEADING_SPECIALS_INDEX, strftime('%B')) . '</h2>';
    $zc_show_specials = true;
    
  }
}
?>

[niccol]

I can't be bothered to go through that line by line. Instead attached is a version that works fine on a clean install.

If you are getting errors then have a look at the debug files or the error log and see what they are.

[limelites]

Well, that's odd. This file does the exact same thing. It removes all the products form the page and shows just a blacnk white space where the products should be.

[niccol]

see previous:

If you are getting errors then have a look at the debug files or the error log and see what they are.