OSCOMMERCE SUPPORT CALL 702-453-3332
 

Help - Search - Members - Calendar
Full Version: Product Sort Problem Solved
osCommerce Community Support Forums > osCommerce Online Merchant v2.x > Tips and Tricks
Dragonkeeper
Oct 18 2006, 03:51 AM
For several weeks I had been pulling my hair out trying to solve a problem I was having with my
site not sorting correctly. I had seen many other stores that did not seem to have this problem. I
wanted my site to sort products based on the Product Name but, for some reason, it was sorting
based on the date I was entering products into the store. We thought the problem might be
attributed to a contribution we added someplace. But, my smartie web designer discovered that
since I had disabled displaying the Product Name in Admin (I only wanted to use this field for
sorting and not for displaying) osc disregarded the fact that I was using this field altogether so
not sort was performed. Once the field name was changed in the code, the sort works. So, the
tip here is if the disable the Product Name in your Admin, you will lose osc's default sort. At least,
that's what it seems to me.
kerell78us
May 30 2007, 09:17 PM
QUOTE (Dragonkeeper @ Oct 18 2006, 03:51 AM) *
For several weeks I had been pulling my hair out trying to solve a problem I was having with my
site not sorting correctly. I had seen many other stores that did not seem to have this problem. I
wanted my site to sort products based on the Product Name but, for some reason, it was sorting
based on the date I was entering products into the store. We thought the problem might be
attributed to a contribution we added someplace. But, my smartie web designer discovered that
since I had disabled displaying the Product Name in Admin (I only wanted to use this field for
sorting and not for displaying) osc disregarded the fact that I was using this field altogether so
not sort was performed. Once the field name was changed in the code, the sort works. So, the
tip here is if the disable the Product Name in your Admin, you will lose osc's default sort. At least,
that's what it seems to me.



Hey Dragonkeeper,
For months I have been struggling to sort my products by the date they were entered, without any success. Then I stumbled upon your post, would u be so kind to share what exactly was done to have your products sorted by date
enigma1
May 31 2007, 12:31 AM
in your catalog\index.php you change this code:

CODE
// show the products of a specified manufacturer
    if (isset($HTTP_GET_VARS['manufacturers_id'])) {
      if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price 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)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";
      } else {
// We show them all
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price 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)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
      }
    } else {
// show the products in a given categorie
      if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only specific catgeory
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price 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)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
      } else {
// We show them all
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
      }
    }

    if ( (!isset($HTTP_GET_VARS['sort'])) || (!ereg('[1-8][ad]', $HTTP_GET_VARS['sort'])) || (substr($HTTP_GET_VARS['sort'], 0, 1) > sizeof($column_list)) ) {
      for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
        if ($column_list[$i] == 'PRODUCT_LIST_NAME') {
          $HTTP_GET_VARS['sort'] = $i+1 . 'a';
          $listing_sql .= " order by pd.products_name";
          break;
        }
      }
    } else {


to this:
CODE
// show the products of a specified manufacturer
    if (isset($HTTP_GET_VARS['manufacturers_id'])) {
      if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_date_added, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price 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)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";
      } else {
// We show them all
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_date_added, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price 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)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
      }
    } else {
// show the products in a given categorie
      if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only specific catgeory
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_date_added, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price 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)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
      } else {
// We show them all
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_date_added, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
      }
    }

    if ( (!isset($HTTP_GET_VARS['sort'])) || (!ereg('[1-8][ad]', $HTTP_GET_VARS['sort'])) || (substr($HTTP_GET_VARS['sort'], 0, 1) > sizeof($column_list)) ) {
      for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
        if ($column_list[$i] == 'PRODUCT_LIST_NAME') {
          $HTTP_GET_VARS['sort'] = $i+1 . 'a';
          $listing_sql .= " order by p.products_date_added";
          break;
        }
      }
    } else {


so the default sorting is by products_date_added.
holwerda
Jun 22 2007, 04:31 PM
Hi,
I've changed the sort order to

"order by p.products_id asc" so the latest productID (last entered prodcut) comes first.

This is ok on the first page, but if i click to go to page 2 (of three, there are a lot of products and not all displayed on one page) it goes back to sorting ASC again???

any idea where i change that so it does not go back to default sorting?
LarryMac
Jan 26 2008, 07:54 PM
QUOTE (holwerda @ Jun 22 2007, 04:31 PM) *
Hi,
I've changed the sort order to

"order by p.products_id asc" so the latest productID (last entered prodcut) comes first.

This is ok on the first page, but if i click to go to page 2 (of three, there are a lot of products and not all displayed on one page) it goes back to sorting ASC again???

any idea where i change that so it does not go back to default sorting?


Has anybody else out there cracked this? I'm having the same problem.
edschaum
Jan 28 2008, 02:41 AM
I did this on my site a long time ago. I don't remember if there are more changes, but here are the 2 I remember:

In my code I made the following changes:

CODE
// $HTTP_GET_VARS['sort'] = $col+1 . 'd';
$HTTP_GET_VARS['sort'] ='d';

// $listing_sql .= " order by p.products_price";
$listing_sql .= " order by p.products_id desc";


Since the new code is differerent, maybe try this:

CODE
$HTTP_GET_VARS['sort'] = 'a';
$listing_sql .= " order by p.products_id desc";


If that doesn't do it, I'll try to find the other changes I made. I also seem to remember that this is a shortcut to doing it right, and that it's hard to get back to this sort after clicking any of the re-sortable headings.

Please back up before making any changes.

Ed
LarryMac
Feb 21 2008, 05:25 AM
It looks like that did it! Thyanks a lot!
bigbuba0
Mar 10 2008, 01:08 AM
QUOTE (edschaum @ Jan 27 2008, 10:41 PM) *
I did this on my site a long time ago. I don't remember if there are more changes, but here are the 2 I remember:

In my code I made the following changes:

CODE
// $HTTP_GET_VARS['sort'] = $col+1 . 'd';
$HTTP_GET_VARS['sort'] ='d';

// $listing_sql .= " order by p.products_price";
$listing_sql .= " order by p.products_id desc";


Since the new code is differerent, maybe try this:

CODE
$HTTP_GET_VARS['sort'] = 'a';
$listing_sql .= " order by p.products_id desc";


If that doesn't do it, I'll try to find the other changes I made. I also seem to remember that this is a shortcut to doing it right, and that it's hard to get back to this sort after clicking any of the re-sortable headings.

Please back up before making any changes.

Ed


I used this to change the sorting of my products by price and it worked great, thanks!
designcraft
May 21 2008, 08:25 PM
I have followed everything that the code said and mine just doesn't do anything. I have 5 products. 2 gift certificates and 3 sets of boxed chocolates. This is the order.
gift certificate - $9.25, gift certificate - $14.50, 2-Piece Seed Box - $4.95, 5-Piece Box - $9.25, 8-Piece Box - $14.50. The gift certificates don't have images so they're blank for the moment. I have my products not listed as categories just products. I even looked in mySQL to see if the sort orders matched what I have but I'm not sure what I looking for or how to change things. If anyone can help that would so great. Thanks!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2008 Invision Power Services, Inc.