Product Sort Problem Solved

[Dragonkeeper]

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]

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

[Guest]

in your catalog\index.php you change 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_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:

// 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]

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]

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]

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:

 

//          $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:

 

  $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]

It looks like that did it! Thyanks a lot!

[bigbuba0]

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:

 

//          $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:

 

  $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]

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!

[NEMROD34]

Hello i use sql with contrib:

ALTER TABLE products ADD `products_sort_order` INT(3);

 

INSERT INTO configuration (configuration_id, configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES (263, 'Display Product Sort Order', 'PRODUCT_SORT_ORDER', '0', 'Do you want to display the Product Sort Order column?', 8, 29, '', '', NULL, NULL);

 

But:

 

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 'order by p.products_sort_order asc, pd.products_name limit 0, 20' at line 1

 

select p.products_image, pd.products_name, pd.products_info, p.products_quantity, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, p.products_sold, p.products_sort_order, 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 products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id = s.products_id, 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 = '1' and p2c.categories_id = '459' order by pd.products_name order by p.products_sort_order asc, pd.products_name limit 0, 20

[Guest]

order by pd.products_name order by p.products_sort_order asc, pd.products_name

You can only use order by once.

 

So the above becomes

order by p.products_sort_order asc, pd.products_name