Product listing sort order

[spooks 79]

This question keeps coming up & people seem to have trouble finding the answer in the forums, so I'll put this here to make it easy.

 

The product list order is by name as default, that is hard coded within index.php so you must change that to alter.

 

The product list sort order also depends on what was clicked last, you may have noticed that annoying '+' by 'product name' in the list heading, thats supposed to tell you that clicking on that or 'product name' will change list order on product name, similarly clicking on any other heading will order list by that column.

 

The code that sets the default order starts aroun 200 of index.php

 

 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;

To sort by something else you must change 2 lines to the appropriate item

 

1.

if ($column_list[$i] == 'PRODUCT_LIST_NAME') {

 

change the PRODUCT_LIST_NAME to one of: PRODUCT_LIST_MODEL, PRODUCT_LIST_MANUFACTURER, PRODUCT_LIST_QUANTITY, PRODUCT_LIST_WEIGHT, PRODUCT_LIST_PRICE

 

2.

$listing_sql .= " order by pd.products_name";

 

change the pd.products_name to one of p.products_model, m.manufacturers_name, p.products_quantity, p.products_weight, final_price

 

If you wish to sort it reverse order you add DESC & change a to d to indicate order.

 

So if your wanting the default sort to be by price in descending order you would have:

 

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_PRICE') {
$HTTP_GET_VARS['sort'] = $i+1 . 'd';
$listing_sql .= " order by final_price desc";
break;

 

Please dont confuse this with sorting product atributes, thats another subject & there is a post in tips dealing with it.

 

Hope that makes things clear.

 

:)

[Bob Parr 0]

This question keeps coming up & people seem to have trouble finding the answer in the forums, so I'll put this here to make it easy.

 

The product list order is by name as default, that is hard coded within index.php so you must change that to alter.

 

The product list sort order also depends on what was clicked last, you may have noticed that annoying '+' by 'product name' in the list heading, thats supposed to tell you that clicking on that or 'product name' will change list order on product name, similarly clicking on any other heading will order list by that column.

 

The code that sets the default order starts aroun 200 of index.php

 

 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;

To sort by something else you must change 2 lines to the appropriate item

 

1.

if ($column_list[$i] == 'PRODUCT_LIST_NAME') {

 

change the PRODUCT_LIST_NAME to one of: PRODUCT_LIST_MODEL, PRODUCT_LIST_MANUFACTURER, PRODUCT_LIST_QUANTITY, PRODUCT_LIST_WEIGHT, PRODUCT_LIST_PRICE

 

2.

$listing_sql .= " order by pd.products_name";

 

change the pd.products_name to one of p.products_model, m.manufacturers_name, p.products_quantity, p.products_weight, final_price

 

If you wish to sort it reverse order you add DESC & change a to d to indicate order.

 

So if your wanting the default sort to be by price in descending order you would have:

 

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_PRICE') {
$HTTP_GET_VARS['sort'] = $i+1 . 'd';
$listing_sql .= " order by final_price desc";
break;

 

Please dont confuse this with sorting product atributes, thats another subject & there is a post in tips dealing with it.

 

Hope that makes things clear.

 

:)

Hi Spooks,

 

I tried the above using product_list_model, but it won't work. My product table doesn't display "model", so I wonder if this is the problem and whether you have any ideas how to fix?

 

Thanks.

[spooks 79]

Hi Spooks,

 

I tried the above using product_list_model, but it won't work. My product table doesn't display "model", so I wonder if this is the problem and whether you have any ideas how to fix?

 

Thanks.

 

Obviously you must select the item to be sorted in admin to be displayed, otherwise your asking to sort by nothing!!!

 

So its hardly surprising when it doesnt work!!!

 

:rolleyes:

[Bob Parr 0]

Thanks Spooks - no further comment necessary!!! :D

[brenwa 0]

I went with a very simple approach..but this will ONLY work for you if you are NOT shipping by weight, and do not EVER plan to

I prefer to bend the cart to my will, with the least possible editing, its all about end result, simplicity and speed!

 

 

I use flat rate shipping so it works great for me...

 

I sort the products by weight

I assign the weight to be the order I want the products to display in.

in other words first product gets a weight of 10, second gets a weight of 20, ect...

going in 10's allows easy addition of products later...and also allows easy re-shuffling if you want it down the road..

 

one other make sure you up the max ship weight in the admin settings of your cart.. I use 50000

instead of 50, and then you need not worry about the cart saying....cant ship that, over the limit!

[spooks 79]

I went with a very simple approach..but this will ONLY work for you if you are NOT shipping by weight, and do not EVER plan to

I prefer to bend the cart to my will, with the least possible editing, its all about end result, simplicity and speed!

 

 

I use flat rate shipping so it works great for me...

 

I sort the products by weight

I assign the weight to be the order I want the products to display in.

in other words first product gets a weight of 10, second gets a weight of 20, ect...

going in 10's allows easy addition of products later...and also allows easy re-shuffling if you want it down the road..

 

one other make sure you up the max ship weight in the admin settings of your cart.. I use 50000

instead of 50, and then you need not worry about the cart saying....cant ship that, over the limit!

 

Good idea, you would need to edit product_listing though to stop is displaying the now meaningless weight.

[Amrahp 0]

..... you may have noticed that annoying '+' by 'product name' in the list heading, thats supposed to tell you that clicking on that or 'product name' will change list order on product name, similarly clicking on any other heading will order list by that column.

 

And while you´re at it.. How do we remove that "+". I did find this thread: Remove "+" but that removes the sort function as well which I do want to keep.

 

Thanks.

[Amrahp 0]

Sorry for cluttering your thread but I did find it now. In case someone else wants to know:

 

Remove the "+" sign

 

Thanks.

[spooks 79]

I took a look at that thread & unfortunately thats more of a 'work around' than a fix, you should'nt be messing with product-listings.php as the '+' is created in functions/general.php by the function tep_create_sort_heading.

 

The line in question is 870:

 

$sort_suffix = (substr($sortby, 0, 1) == $colnum ? (substr($sortby, 1, 1) == 'a' ? '+' : '-') : '') . '</a>';

 

you could simply replace both the '+' & '-' with '' or use images for more obvoise display of the function.

[Amrahp 0]

Hi,

Nice one Sam!

 

I'm having weird problems with my product listing.

 

Fyi: I am using the "All products" contribution (great one btw!). When viewing that one my products are listed in a correct way.

But: When I click a category in my menu and view it from there.. Well... it has no sorting logic whatsoever!

Every product is sorted in a different way! Really weird! They are for sure always sorted by name but after that it all goes wrong.

I am listing model, weight and product quantity as well and these are listed like they were thrown in randomly!

 

I have compared the code and they are identical on the point where the sort is taken care of:

 

  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;

 

First I thought that the problem was in the lines above

$listing_sql = "select " . $select_column_list . " p.products_id.... etc

 

Could I be on the right track there?

This is drivning me nuts!! How is it possible that allprods.php list my products correct but not index.php??

 

Thanks!

[spooks 79]

Assuming your talking about http://addons.oscommerce.com/info/1501

 

I see there are several fixes for sort order there, have you done them?

[Amrahp 0]

Hi Sam,

Thanks for your time and answer!

 

Yep that one...

 

Maybe I wasn't clear enough. The thing is that that contribution DOES work 100%.

 

The problem is that index.php doesn´t sort correct. I have tried to install a fresh osc-version and still same problem.

 

The prod.name is correct.. the rest (model, quantity and weight) is chaos.

 

Thanks

[spooks 79]

Perhaps there's something up with you dBase, I've default installs of MS2 & RC2a for testing, both sort fine.

 

Unless your talking of search, that does'nt sort, so will be random

[Amrahp 0]

Hi,

 

Yes could be I guess. :blink:

 

But that makes me think how the sort function actually works? After prod_list_name - what are the other sort criterias and where are those defined?

There must be a definition on what criterias to use next after prod_list_name.. :huh:

 

But as I said earlier: Since the sort function works perfect in "allprods" must there be some solution to use those lines and put them in the sort function in index.php.

[spooks 79]

The way it works is index.php builds a sql query, which is then applied in product_listing.php.

 

The order applied is set by the section:

 

 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_PRICE') {
	  $HTTP_GET_VARS['sort'] = $i+1 . 'd';
	  $listing_sql .= " order by final_price desc";
	  break;
	}
  }
} else {
  $sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);
  $sort_order = substr($HTTP_GET_VARS['sort'], 1);
  //$listing_sql .= ' order by ';
  switch ($column_list[$sort_col-1]) {
	case 'PRODUCT_LIST_MODEL':
	  $listing_sql .= " order by p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'PRODUCT_LIST_NAME':
	  $listing_sql .= " order by pd.products_name " . ($sort_order == 'd' ? 'desc' : '');
	  break;
	case 'PRODUCT_LIST_MANUFACTURER':
	  $listing_sql .= " order by m.manufacturers_name " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'PRODUCT_LIST_QUANTITY':
	  $listing_sql .= " order by p.products_quantity " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'PRODUCT_LIST_IMAGE':
	  $listing_sql .= " order by pd.products_name";
	  break;
	case 'PRODUCT_LIST_WEIGHT':
	  $listing_sql .= " order by p.products_weight " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'PRODUCT_LIST_PRICE':
	  $listing_sql .= " order by final_price " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
  }
}

in index.php.

 

You can check the query thats been generated by adding at the bottom of product_listing.php just before the final ?>

 

 echo '<hr>' . $listing_sql . '<hr>';

 

With the setting given in the code above the default query should end with: order by final_price desc

[spooks 79]

A question came up about setting default sort by date added, so I`ll add the answer here.

 

Find:

 

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_PRICE') {
	  $HTTP_GET_VARS['sort'] = $i+1 . 'd';
	  $listing_sql .= " order by final_price desc";
	  break;
	}
  }
} else {

 

Replace with:

 

if ( (!isset($HTTP_GET_VARS['sort'])) || (!ereg('^[1-8][ad]$', $HTTP_GET_VARS['sort'])) || (substr($HTTP_GET_VARS['sort'], 0, 1) > sizeof($column_list)) ) {
	  $_GET['sort'] = 0 . 'd';
	  $sort_sql = " order by p.products_date_added DESC, pd.products_name";
} else {

 

I hope you find that useful.

 

:)

[spooks 79]

OOPS, did you spot the deliberate mistake? It should be:

 

Replace with:

 

if ( (!isset($HTTP_GET_VARS['sort'])) || (!ereg('^[1-8][ad]$', $HTTP_GET_VARS['sort'])) || (substr($HTTP_GET_VARS['sort'], 0, 1) > sizeof($column_list)) ) {
	  $HTTP_GET_VARS['sort'] = 0 . 'd';
	  $listing_sql .=  " order by p.products_date_added DESC, pd.products_name";
} else {

[allaboutwicker 15]

Hello,

 

I am having the problem that my site will no longer sort by anything other than the default product name. That is fine for when they first go to the page, however, the sorting function does not appear to work at all anymore! When you click to sort by say "Price" it just refreshes the page the same with no sorting change. I have done so many changes, I have no clue where I might have changed the code that effects this.

 

Thanks!

[spooks 79]

Could be one of any number of errors, you can check the query by adding: echo '<hr>' . $listing_sql . '<hr>'; to the end of product_listing.php

 

You could also try installing Product Listing Enhancements, Thumbnails & Manufacturer Headings http://addons.oscommerce.com/info/6051 as that adds some extra sort select options, might give a clue where errror is. To unistall just put back original product_listing.php

[allaboutwicker 15]

Could be one of any number of errors, you can check the query by adding: echo '<hr>' . $listing_sql . '<hr>'; to the end of product_listing.php

 

You could also try installing Product Listing Enhancements, Thumbnails & Manufacturer Headings http://addons.oscommerce.com/info/6051 as that adds some extra sort select options, might give a clue where errror is. To unistall just put back original product_listing.php

 

Hi Spooks,

 

Thanks for the quick reply! I did install your contrib yesterday which was what brought my attention to my sorting problem again. I now have the drop down box at the bottom of the page where they can choose and it does nothing. I tried the code as you suggested at the end of the product_lising.php file and got the following:

 

select p.products_image, pd.products_name, 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 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 = '24' order by pd.products_name

 

I am not a programmer and really don't know what that means, so if it means something to you or if you have any other ideas of why the sort function isn't working I would be so grateful.

[spooks 79]

Ok

 

Don't tell me, you have search engine friendly url is set to 'on'?

 

If you look in the thread for that contrib you will see posts about that issue & the fix.

[allaboutwicker 15]

Ok

 

Don't tell me, you have search engine friendly url is set to 'on'?

 

If you look in the thread for that contrib you will see posts about that issue & the fix.

 

Wow! Spooks, you are the Bestest!

 

I did install the Ultimate SEO URLs contrib a while back. When I turn that off, my sort order problem goes away!!! Thanks so much for your help in pointing that out to me. I have not found the fix yet, but I see there are several versions after the one I installed, so it may be in one the new ones or in the thread somewhere. I guess will have to go find it.

 

Thanks again

[swiftmed 0]

Sam,

 

thanks for the advice in this thread, although i have a problem, (i have put this reply in my thread you commented on earlier too), i have followed the advice of this thread, but it doesnt change the way products are listed in the WHATS_NEW box. Products are still all over the place.

 

you can see what i mean by looking at my site: http://loveandmarriagebridal.co.uk/

 

This is the hard code from index.php

 

	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_PRICE') {
	  $HTTP_GET_VARS['sort'] = $i+1 . 'a';
	  $listing_sql .= " order by pd.products_price";
	  break;

 

Please help with regards to ordering by price in my whats_new box please. my customer really needs a solution for this ASAP.

 

Thanks for your time

Andy

[spooks 79]

Setting sort order for the new_products.php module (includes/modules/new_products.php) that appears on your home or 'default' at the bottom

 

find: (appears twice)

 

order by p.products_date_added desc

 

change both to

 

order by pd.products_name desc

 

to order by name, or

 

order by products_price desc

 

to order by price, remove the 'desc' if you don't want reverse order

 

It should be clear what to do for other search orders.

 

 

 

[aquawrks 0]

In need of help?

Tried everything on this thread and still cant get my sorting to work. My site is only currently showing all products added last first. Been trying to sort by name, manufacturer, price, and i just dont seem to be getting anywere?

Greatly appreciate any help i can get!