Make a product that is sold out not appear in listings, sideboxes

[dgent]

Is there any way to make a product which is marked as sold out, not appear in product listings, centreboxes etc.

I don't want to delete the product or turn it off as that makes a page not found, so I want to mark it as sold out, but not come up in the current products listings on my sites.

[Ajeh]

For the Product Listing, edit the file:
/includes/index_filters/default_filter.php

and where you find in each of the SELECT statements:

       where p.products_status = 1

add to it:

       where p.products_status = 1
       and p.products_quantity > 0

For the Centerboxes, you would need to do the same thing, example for the Featured Products centerbox edit the file, using your templates and overrides:
/includes/modules/featured_products.php

copy to:
/includes/modules/your_templates_dir/featured_products.php

and add the same thing to each of the SELECT statements:

                           and p.products_status = 1 and f.status = 1

to read:

                           and p.products_status = 1 and f.status = 1
                           and p.products_quantity > 0

and that will prevent the display of Sold Out Products ...

[dgent]

Excellent thanks.

[Ajeh]

Thanks for the update that this worked for you ...

[dgent]

Actually, couple of issues. Those products still show in searches (and not for those particular products - but they just happen to have a keyword used in it, ie 'dress') and they still show in the new products page and all products page.

Any way of preventing this?

[Ajeh]

Peek at the:
/includes/modules/pages/advanced_search_result/header_php.php

$where_str = " WHERE (p.products_status = 1
               AND p.products_id = pd.products_id
               AND pd.language_id = :languagesID
               AND p.products_id = p2c.products_id
               AND p2c.categories_id = c.categories_id ";

[Renz]

Hi Ajeh,
Following your above advice, I was sucessfull in removing"Sold Out" items from the "Product Listings", and "Advanced Search Results".

I have not been able to prevent them showing on the "Specials" "Featured""All Products"

I Have recently added this line:
$listing_sql .= " order by IF( p.products_quantity >0, 1, 0 ) DESC , p.products_sort_order , pd.products_name";
To: /includes/index_filters/default_filter.php
Around line 107. Though I dont think this would have an effect on the other pages I am trying to stop the sold out items showing.
Thanks
Regards
Renz

[Renz]

Update..

I have now done it for "All Products" & "New Products"

Nice and easy.

These though are not playing nice. (I actually deleted/renamed them and they still work, so for some reason I am not editing the right ones, but I triple checked that I am going to the below file structure locations)

Featured….
includes/modules/theme163/featured_products.php
and p.products_quantity > 0

Specials…
You need to customize the code for the:
/includes/modules/pages/specials/main_template_vars.php

and add to the WHERE statement something like:
AND p.products_quantity > 0

I look fwd to any assistance.
Regards
Renz

[Ajeh]

On the:
/includes/modules/pages/specials/main_template_vars.php

You are using:

  $specials_query_raw = "SELECT p.products_id, p.products_image, pd.products_name,
                          p.master_categories_id
                         FROM (" . TABLE_PRODUCTS . " p
                         LEFT JOIN " . TABLE_SPECIALS . " s on p.products_id = s.products_id
                         LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id )
                         WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1'
                         AND s.status = 1
                         AND p.products_quantity > 0
                         AND pd.language_id = :languagesID
                         ORDER BY s.specials_date_added DESC";

and added to both SELECT statements:

  $featured_products_query = "select distinct p.products_id, p.products_image, pd.products_name, p.master_categories_id
                           from (" . TABLE_PRODUCTS . " p
                           left join " . TABLE_FEATURED . " f on p.products_id = f.products_id
                           left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id )
                           where p.products_id = f.products_id
                           and p.products_id = pd.products_id
                           and p.products_status = 1 and f.status = 1
                         AND p.products_quantity > 0 
                           and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'";

    $featured_products_query = "select distinct p.products_id, p.products_image, pd.products_name, p.master_categories_id
                                from (" . TABLE_PRODUCTS . " p
                                left join " . TABLE_FEATURED . " f on p.products_id = f.products_id
                                left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id)
                                where p.products_id = f.products_id
                                and p.products_id = pd.products_id
                                and p.products_status = 1 and f.status = 1
                         AND p.products_quantity > 0 
                                and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                                and p.products_id in (" . $list_of_products . ")";

[Renz]

I am sorry Ajeh,
I really struggle to follow what you are telling me...I am limited with Scripts to.
Find/Change this goes there.
I dont understand the 2 select statements.
Also the example line of mine:
WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1'
AND s.status = 1
AND p.products_quantity > 0
AND pd.language_id = :languagesID

Looks different to yours..where p.products_id = f.products_id
and p.products_id = pd.products_id
and p.products_status = 1 and f.status = 1
AND p.products_quantity > 0
and pd.language_id = '" .

So its very hard for me to figure it out.
Thanks
RR