Jump to content
Latest News: (loading..)
Bobber

Product Sort Order - First In Stock Products Then Out Of Stock Both Descending By Price High To Low

Recommended Posts

Hi Everyone!

I know there have been many post and topics about product sorting and ordering... trust me I have read them all :cool:

On this topic I do have a question that I am hoping someone can help me and the OsCommerce Community with...

How can I by default display products in the following sort / order:

First: In Stock Products ( Products with Quantity Available Greater Than 0 ) Descending in Price ( from High to Low )...

Then:  Out Of Stock Products ( Products with Quantity Available Less Than And/Or Equal To 0 ) Descending in Price ( from High to Low )...

I know this need to be done in index.php... I would appreciate help with the exact syntax and .php code need to make this happen.

Thank you all for your help!

Share this post


Link to post
Share on other sites
    if ( (!isset($HTTP_GET_VARS['sort'])) || (!preg_match('/^[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";

What do I need to change this to???

          $listing_sql .= " order by final_price desc";

I am unsure as to how to sort the "In Stock Items" and "Out Of Stock Items"...

Share this post


Link to post
Share on other sites

Bit more complicated since you have to account for product status.
FIND EACH INSTANCE OF:

$listing_sql = "select " . $select_column_list . " p.products_id, SUBSTRING_INDEX(pd.products_description, ' ', 20) as products_description, p.manufacturers_id,

REPLACE EACH INSTANCE WITH:

$listing_sql = "select " . $select_column_list . " p.products_id, SUBSTRING_INDEX(pd.products_description, ' ', 20) as products_description, p.products_status, p.manufacturers_id,

 

THEN:

    if ( (!isset($_GET['sort'])) || (!preg_match('/^[1-8][ad]$/', $_GET['sort'])) || (substr($_GET['sort'], 0, 1) > sizeof($column_list)) ) {
      for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
        if ($column_list[$i] == 'PRODUCT_LIST_PRICE') {
          $_GET['sort'] = $i+1 . 'd';
          $listing_sql .= " order by p.products_status DESC, final_price DESC";

 

Edited by a.forever

Share this post


Link to post
Share on other sites

Thank you for the help @a.forever!

My instances are different than yours: 

        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_date_available, 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'] . "'";

I tried inserting the: 

p.products_status,

before the: 

p.manufacturers_id,

Because our code varied in that instance...

And changed: 

    if ( (!isset($HTTP_GET_VARS['sort'])) || (!preg_match('/^[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";

To: 

    if ( (!isset($_GET['sort'])) || (!preg_match('/^[1-8][ad]$/', $_GET['sort'])) || (substr($_GET['sort'], 0, 1) > sizeof($column_list)) ) {

      for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
                                                 
        if ($column_list[$i] == 'PRODUCT_LIST_PRICE') {
                                                 
          $_GET['sort'] = $i+1 . 'd';
                                                 
          $listing_sql .= " order by p.products_status DESC, final_price DESC";

But no luck on my end...

It did sort by Price Descending, but it did not sort In Stock First Followed After By Out Of Stock Items...

Were you able to test this on your end @a.forever?

Is so... did it work???

Share this post


Link to post
Share on other sites

@Bobber I probably should have mentioned that I'm using 2.3.4 BS Edge as my osC version, so my code may appear different than yours if you're on a different version. Looked fine to me when I tested it. The general idea is to modify catalog/index.php and insert:

p.products_status,

before:

p.manufacturers_id,

for each instance that you find:

$listing_sql = "select " . $select_column_list

This will allow the listing search query to identify out-of-stock vs in-stock items because product status should either be a 0 or a 1. You can't sort order by price/availability until that variable is in the query. And there should be about four (4) instances within catalog/index.php.

Apologies it took a month to reply. I skim these forums nowadays when I seek a neat mod/trick, or when the USPS module breaks. Hope this helps clarify things a bit and gets it working on your end.

Share this post


Link to post
Share on other sites

There are several addons available that will work with the BS version that allow you to enter a custom sort order of products which may be of some help to you. Although not what you wanted, it may be better than nothing.

This is the one that I use. https://apps.oscommerce.com/zyGgV


REMEMBER BACKUP, BACKUP AND BACKUP

 

Find information about the bootstrap community version here

 

Make it idiot proof and someone will make a better idiot.

Share this post


Link to post
Share on other sites
19 hours ago, a.forever said:

@Bobber I probably should have mentioned that I'm using 2.3.4 BS Edge as my osC version, so my code may appear different than yours if you're on a different version. Looked fine to me when I tested it. The general idea is to modify catalog/index.php and insert:


p.products_status,

before:


p.manufacturers_id,

for each instance that you find:


$listing_sql = "select " . $select_column_list

This will allow the listing search query to identify out-of-stock vs in-stock items because product status should either be a 0 or a 1. You can't sort order by price/availability until that variable is in the query. And there should be about four (4) instances within catalog/index.php.

Apologies it took a month to reply. I skim these forums nowadays when I seek a neat mod/trick, or when the USPS module breaks. Hope this helps clarify things a bit and gets it working on your end.

@a.forever

Thank You Very Much For Your Insight Into This Problem...

Once I find the proper solution I will report back and add it as a contribution for others as well :-)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×