Product Listing Default Sort Order

[edwardtilbury]

I ended up change the sort order and deleted the sort by "2a" to just a blank field "" in the normal admin page, seems to display properly now.

[s_t_e_v_e]

jymontoya:

Those lines should not be commented out (as compared to a default install) doing so, as you have found out, will disable any control of the sort order via the admin

[limelites]

I'm having trouble with this now since installing a neat drop down filter. I've merged my new filter with the modifications made previously in this thread to default_filter.php:

<?php
/**
 * default_filter.php  for index filters
 *
 * index filter for the default product type
 * show the products of a specified manufacturer
 *
 * @package productTypes
 * @copyright Copyright 2003-2007 Zen Cart Development Team
 * @copyright Portions Copyright 2003 osCommerce
 * @todo Need to add/fine-tune ability to override or insert entry-points on a per-product-type basis
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version $Id: default_filter.php 6912 2007-09-02 02:23:45Z drbyte $
 */
if (!defined('IS_ADMIN_FLAG')) {
  die('Illegal Access');
}
  if (isset($_GET['alpha_filter_id']) && (int)$_GET['alpha_filter_id'] > 0) {
    //$alpha_sort = " and pd.products_name LIKE '" . chr((int)$_GET['alpha_filter_id']) . "%' ";
    $filterid = (int)$_GET['alpha_filter_id'];
    switch($filterid)
    {
            case 1:
                $alpha_sort = " order by pd.products_viewed";
                break;
            case 2:
                $alpha_sort = " order by p.products_price";
                break;
            case 3:
                $alpha_sort = " order by p.products_price DESC";
                break;
            case 4:
                $alpha_sort = " order by pd.products_name";
                break;
            case 5:
                $alpha_sort = " order by m.manufacturers_name";
                break;
            case 6:
                $alpha_sort = " order by p.products_date_added";
                break;
            case 7:
                $alpha_sort = " order by p.products_date_added DESC";
                break;
                
                
            
        
    }
  } else {
    $alpha_sort = '';
  }
  if (!isset($select_column_list)) $select_column_list = "";
   // show the products of a specified manufacturer
  if (isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] != '' ) {
    if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id'])) {
// We are asked to show only a specific category
      $listing_sql = "select distinct " . $select_column_list . " p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, if(s.status = 1, s.specials_new_products_price, NULL) AS specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
       from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id , " .
       TABLE_PRODUCTS_DESCRIPTION . " pd, " .
       TABLE_MANUFACTURERS . " m, " .
       TABLE_PRODUCTS_TO_CATEGORIES . " p2c
       where p.products_status = 1
         and p.manufacturers_id = m.manufacturers_id
         and m.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'
         and p.products_id = p2c.products_id
         and pd.products_id = p2c.products_id
         and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
         ";/* bof all_sub_cats_products */
         if(!empty($sub_cats)) 
             $listing_sql .=  "and p2c.categories_id IN($sub_cats)" .
         $alpha_sort;
         else 
/* eof all_sub_cats_products*/ $listing_sql .=  "
         and p2c.categories_id = '" . (int)$_GET['filter_id'] . "'" .
         $alpha_sort;
    } else {
// We show them all
      $listing_sql = "select distinct " . $select_column_list . " p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
      from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " .
      TABLE_PRODUCTS_DESCRIPTION . " pd, " .
      TABLE_MANUFACTURERS . " m
      where p.products_status = 1
        and pd.products_id = p.products_id
        and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
        ";/* bof all_sub_cats_products */
         if(!empty($sub_cats)) 
             $listing_sql .=  "and p2c.categories_id IN($sub_cats)" .
         $alpha_sort;
         else 
/* eof all_sub_cats_products*/ $listing_sql .=  "
        and p.manufacturers_id = m.manufacturers_id
        and m.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'" .
        $alpha_sort;
    }
  } else {
// show the products in a given category
    if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id'])) {
// We are asked to show only specific category
      $listing_sql = "select distinct " . $select_column_list . " p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
      from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " .
      TABLE_PRODUCTS_DESCRIPTION . " pd, " .
      TABLE_MANUFACTURERS . " m, " .
      TABLE_PRODUCTS_TO_CATEGORIES . " p2c
      where p.products_status = 1
        and p.manufacturers_id = m.manufacturers_id
        and m.manufacturers_id = '" . (int)$_GET['filter_id'] . "'
        and p.products_id = p2c.products_id
        and pd.products_id = p2c.products_id
        and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
        ";/* bof all_sub_cats_products */
         if(!empty($sub_cats)) 
             $listing_sql .=  "and p2c.categories_id IN($sub_cats)" .
         $alpha_sort;
         else 
/* eof all_sub_cats_products*/ $listing_sql .=  "
        and p2c.categories_id = '" . (int)$current_category_id . "'" .
        $alpha_sort;
    } else {
// We show them all
      $listing_sql = "select distinct " . $select_column_list . " p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status =1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
       from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " .
       TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " .
       TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id
       where p.products_status = 1
         and p.products_id = p2c.products_id
         and pd.products_id = p2c.products_id
         and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
         ";/* bof all_sub_cats_products */
         if(!empty($sub_cats)) 
             $listing_sql .=  "and p2c.categories_id IN($sub_cats)" .
         $alpha_sort;
         else 
/* eof all_sub_cats_products*/ $listing_sql .=  "
         and p2c.categories_id = '" . (int)$current_category_id . "'" .
         $alpha_sort;
    }
  }

// set the default sort order setting from the Admin when not defined by customer
  if (!isset($_GET['sort']) and PRODUCT_LISTING_DEFAULT_SORT_ORDER != '') {
    $_GET['sort'] = PRODUCT_LISTING_DEFAULT_SORT_ORDER;
  }

  if (isset($column_list)) {
    if ((!isset($_GET['sort'])) || (isset($_GET['sort']) && !ereg('[1-8][ad]', $_GET['sort'])) || (substr($_GET['sort'], 0, 1) > sizeof($column_list)) ) {
      for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
        if (isset($column_list[$i]) && $column_list[$i] == 'PRODUCT_LIST_NAME') {
          $_GET['sort'] = $i+1 . 'a';
          //$listing_sql .= " order by p.products_sort_order, pd.products_name";
          break;
        } else {
// sort by products_sort_order when PRODUCT_LISTING_DEFAULT_SORT_ORDER is left blank
// for reverse, descending order use:
//       $listing_sql .= " order by p.products_sort_order desc, pd.products_name";
          //$listing_sql .= " order by p.products_date_added DESC, pd.products_name";
          break;
        }
      }  
// if set to nothing use products_sort_order and PRODUCTS_LIST_NAME is off
      if (PRODUCT_LISTING_DEFAULT_SORT_ORDER == '') {
        $_GET['sort'] = '20a';
      }
    } else {
      $sort_col = substr($_GET['sort'], 0 , 1);
      $sort_order = substr($_GET['sort'], 1);
      $listing_sql .= ' order by ';
      switch ($column_list[$sort_col-1]) {
        
        case 'PRODUCT_LIST_MODEL':
          $listing_sql .= "p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_NAME':
          $listing_sql .= "pd.products_name " . ($sort_order == 'd' ? 'desc' : '');
          break;
        case 'PRODUCT_LIST_MANUFACTURER':
          $listing_sql .= "m.manufacturers_name " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_QUANTITY':
          $listing_sql .= "p.products_quantity " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_IMAGE':
          $listing_sql .= "pd.products_name";
          break;
        case 'PRODUCT_LIST_WEIGHT':
          $listing_sql .= "p.products_weight " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_PRICE':
//          $listing_sql .= "final_price " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          $listing_sql .= "p.products_price_sorter " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
      }
    }
  }
// optional Product List Filter
  if (PRODUCT_LIST_FILTER > 0) {
    if (isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] != '') {
      $filterlist_sql = "select distinct c.categories_id as id, cd.categories_name as name
      from " . TABLE_PRODUCTS . " p, " .
      TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " .
      TABLE_CATEGORIES . " c, " .
      TABLE_CATEGORIES_DESCRIPTION . " cd
      where p.products_status = 1
        and p.products_id = p2c.products_id
        and p2c.categories_id = c.categories_id
        and p2c.categories_id = cd.categories_id
        and cd.language_id = '" . (int)$_SESSION['languages_id'] . "'
        and p.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'
      order by cd.categories_name";
    } else {
      $filterlist_sql= "select distinct m.manufacturers_id as id, m.manufacturers_name as name
      from " . TABLE_PRODUCTS . " p, " .
      TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " .
      TABLE_MANUFACTURERS . " m
      where p.products_status = 1
        and p.manufacturers_id = m.manufacturers_id
        and p.products_id = p2c.products_id
        and p2c.categories_id = '" . (int)$current_category_id . "'
      order by m.manufacturers_name";
    }
    $do_filter_list = false;
    $filterlist = $db->Execute($filterlist_sql);
    if ($filterlist->RecordCount() > 1) {
        $do_filter_list = true;
      if (isset($_GET['manufacturers_id'])) {
        $getoption_set =  true;
        $get_option_variable = 'manufacturers_id';
        $options = array(array('id' => '', 'text' => TEXT_ALL_CATEGORIES));
      } else {
        $options = array(array('id' => '', 'text' => TEXT_ALL_MANUFACTURERS));
      }
      while (!$filterlist->EOF) {
        $options[] = array('id' => $filterlist->fields['id'], 'text' => $filterlist->fields['name']);
        $filterlist->MoveNext();
      }
    }
  }

// Get the right image for the top-right
  $image = DIR_WS_TEMPLATE_IMAGES . 'table_background_list.gif';
  if (isset($_GET['manufacturers_id'])) {
    $sql = "select manufacturers_image
              from   " . TABLE_MANUFACTURERS . "
              where      manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'";

    $image_name = $db->Execute($sql);
    $image = $image_name->fields['manufacturers_image'];

  } elseif ($current_category_id) {

    $sql = "select categories_image from " . TABLE_CATEGORIES . "
            where  categories_id = '" . (int)$current_category_id . "'";

    $image_name = $db->Execute($sql);
    $image = $image_name->fields['categories_image'];
  }
?>

This is working well, but the default sort order is coming through as old to new. I want the reverse, new to old.

You'll notice that line 155 is commented out:

//$listing_sql .= " order by p.products_date_added DESC, pd.products_name"; 

When I remove the comments, it sorts the default order to new to old. You'd think problem would be solved, as it looks ok until you choose a filter option from the drop down. For some reason line 155 is causing a 1064 MYSQL syntax error as reported here:

http://www.zen-cart.com/forum/showthread.php?t=133166

I've also tried changing the default sort order in admin. At present I've left it blank, but as soon as I change it, say to 2a for example, it just creates the same 1064 error as line 155 does.

Can anyone help me please? How can I make my default sort order NEW to OLD??

[Ajeh]

Your alpha_sorter has:

    switch($filterid)
    {
            case 1:
                $alpha_sort = " order by pd.products_viewed";
                break;
            case 2:
                $alpha_sort = " order by p.products_price";
                break;
            case 3:
                $alpha_sort = " order by p.products_price DESC";
                break;
            case 4:
                $alpha_sort = " order by pd.products_name";
                break;
            case 5:
                $alpha_sort = " order by m.manufacturers_name";
                break;
            case 6:
                $alpha_sort = " order by p.products_date_added";
                break;
            case 7:
                $alpha_sort = " order by p.products_date_added DESC";
                break;
    }

The original has:

  if (isset($_GET['alpha_filter_id']) && (int)$_GET['alpha_filter_id'] > 0) {
    $alpha_sort = " and pd.products_name LIKE '" . chr((int)$_GET['alpha_filter_id']) . "%' ";
  } else {
    $alpha_sort = '';
  }

Notice it does NOT contain the order by in it ... as it really doesn't have anything to do with the order by on the original file ...

If you look at the original file for the:
order by

you will see where that is built and from what ...

If you comment out the original order by code that might help ...

I don't have the time right now to re-write all the code, but hope this gives you a clue ...

[balak]

Dear limelites

I think you got this module from http://platoon.in blog.

Dont worry about this code. It was perfectly working.

yes you can able to change the default sort order.

if you download this module from http://blog.platoon.in/product-filte...-for-download/

then the following steps may be useful


Originally posted in Platoon's Blog:

Please download the above zip file again as a small bug has been fixed.

Now open default_filter.php found in index_filter module.

Goto Line 50: Replace $alpha_sort = ''; with
$alpha_sort = " order by p.products_date_added DESC";
$filterid = 6; // 6 is the case sortquery to list new to old.

now open product_listing_alpha_sorter.php found in modules/yourtemplatename folder.

Goto Line 39: Replace "echo zen_draw_pull_down_menu('alpha_filter_id', $letters_list, (isset($_GET['alpha_filter_id']) ? $_GET['alpha_filter_id'] : ''), 'onchange="this.form.submit()"');" with

echo zen_draw_pull_down_menu('alpha_filter_id', $letters_list, (isset($_GET['alpha_filter_id']) ? $_GET['alpha_filter_id'] : $filterid), 'onchange="this.form.submit()"');

Thats it.

Thanks
Bala

[limelites]

Hi Bala,

Thank you, that worked perfectly. Site's working again as it should

[sam_m]

Thanks for this post Balak, something I was looking for aswell, although, for some reason I kept getting a blank page until I added quotes...

$alpha_sort = " order by p.products_date_added DESC";
$filterid = '6'; // 6 is the case sortquery to list new to old.

echo zen_draw_pull_down_menu('alpha_filter_id', $letters_list, (isset($_GET['alpha_filter_id']) ? $_GET['alpha_filter_id'] : '$filterid'), 'onchange="this.form.submit()"');

Not sure why it was different for me, but in case anyone else tries this and has the same problem, thats what fixed it for me

[anetahermitage]

I am having problems with this and have read this post and others as well as tried various things, I hope I am just missing something and someone can help me with this ---- The URL is http://blockislandhoney.com/ go to either Candles or Novelties to see the problem, For example Novelties, The product listing page is set up in the correct order that my client wants it to be, I used SORT ORDER field to do this, However, when you click on the Yoga CD, the first product, you go to it's product page and it says it is Product 4/7.... It should be 1/7.... then if you click on Next you should see the Calendar which is the next product on the Product Listing page and according to the sort order, but you don't see the calendar. Any general or specific help would be appreciated, if you need to know more about various settings I can provide the information.

I'm having the same problem. It's driving me nuts! Did you ever found the solution to it? or does anyone know the solution to this?

[zerodb72]

I installed this mod and it works… until I click on “product” column that change display order adding “&sort=2a” to URL.

There is a SQL error (double “order by” clausole)

The SQL string ends with:

“….. and p2c.categories_id = ‘97′order by p.products_date_added DESC order by ]”

Any easy fix for this?

Thank you, Antonello.

[lauriesscraps]

s_t_e_v_e, I can't believe you! I think the guru's should take a leaf from your book though.

You've helped me now with a dozen or so problems and each and every time, you've responded with a one liner and each and every time it's been 100% spot on perfect.

You've hit the nail on the head here again and it seemed effortless, you're amazing!!

To summarise then, for anyone who wants the default order of their product listings to be newest-to-oldest then here's all you need do:

Admin/Product Listing/Product Listing/Display Product Listing Default Sort Order make sure that you edit this and leave it blank... don't put anything in there at all.... (NOTE: Leave Blank for Product Sort Order. Sort the Product Listing in the order you wish for the default display to start in to get the sort order setting. Example: 2a)

Now:

edit /includes/index_filters/default_filter.php:

replace approx lines 123-129:

// sort by products_sort_order when PRODUCT_LISTING_DEFAULT_SORT_ORDER is left blank
// for reverse, descending order use:
//       $listing_sql .= " order by p.products_sort_order desc, pd.products_name";
          $listing_sql .= " order by p.products_sort_order, pd.products_name";
          break;
        }
      } 

with this:

// sort by products_sort_order when PRODUCT_LISTING_DEFAULT_SORT_ORDER is left blank
// for reverse, descending order use:
//       $listing_sql .= " order by p.products_sort_order desc, pd.products_name";
          $listing_sql .= " order by p.products_date_added DESC, pd.products_name";
          break;
        }
      } 

Again Steve, thank you for your time. True genius!!

I LOVE YOU, LOVE YOU, LOVE YOU!!!!!! After 3 hours of trying to figure it out I read this and get it fixed! MMMMMWAHHH!