176 replies to this topic

[spooks]

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.

Edited by spooks, 15 July 2008, 09:47.

[Bob Parr]

spooks, on Jul 15 2008, 10:43 AM, said:

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]

Bob Parr, on Jul 16 2008, 11:22 AM, said:

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!!!

[Bob Parr]

[/quote]
Thanks Spooks - no further comment necessary!!!

[brenwa]

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]

brenwa, on Jul 20 2008, 03:14 AM, said:

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]

spooks, on Jul 15 2008, 09:43 AM, said:

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

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

Remove the "+" sign

Thanks.

[spooks]

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]

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]

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]

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]

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]

Hi,

Yes could be I guess.

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..

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.

Edited by Amrahp, 26 July 2008, 04:45.

[spooks]

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]

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]

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 {

Edited by spooks, 03 August 2008, 20:35.

[allaboutwicker]

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]

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]

spooks, on Aug 16 2008, 10:50 AM, said:

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.