Jump to content
Latest News: (loading..)

Archived

This topic is now archived and is closed to further replies.

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";

 

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

Before installing the official version of oscommerce first look at a responsive version here

It's very easy to over complicate what are simple things in life

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

×