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

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.

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

Excellent thanks.

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

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?

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

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

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

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

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

What version of Zen Cart are you using?

Hi I am on 1.3.8a

(Sorry not able to upgarde at the moment, not i my capacity)

Thanks
RR

Post the whole SELECT statement that you have in your code ... it is not making a lot of sense why it does not work for you to add:
AND p.products_quantity > 0

to the WHERE portion of the SELECT statements ...

<?php /** * Specials * * @package page * @copyright Copyright 2003-2007 Zen Cart Development Team * @copyright Portions Copyright 2003 osCommerce * @license <http://www.zen-cart.com/license/2_0.txt> GNU Public License V2.0 * @version $Id: main_template_vars.php 6912 2007-09-02 02:23:45Z drbyte $ */ if (MAX_DISPLAY_SPECIAL_PRODUCTS > 0 ) { $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"; $specials_query_raw = $db->bindVars($specials_query_raw, ':languagesID', $_SESSION['languages_id'], 'integer'); $specials_split = new splitPageResults($specials_query_raw, MAX_DISPLAY_SPECIAL_PRODUCTS); $specials = $db->Execute($specials_split->sql_query); $row = 0; $col = 0; $list_box_contents = array(); $title = ''; $num_products_count = $specials->RecordCount(); if ($num_products_count) { if ($num_products_count < SHOW_PRODUCT_INFO_COLUMNS_SPECIALS_PRODUCTS || SHOW_PRODUCT_INFO_COLUMNS_SPECIALS_PRODUCTS==0 ) { $col_width = floor(100/$num_products_count); } else { $col_width = floor(100/SHOW_PRODUCT_INFO_COLUMNS_SPECIALS_PRODUCTS); } $list_box_contents = array(); while (!$specials->EOF) { $products_price = zen_get_products_display_price($specials->fields['products_id']); $specials->fields['products_name'] = zen_get_products_name($specials->fields['products_id']); $list_box_contents[$row][$col] = array('params' => 'class="specialsListBoxContents"' . ' ' . 'style="width:' . $col_width . '%;"', 'text' => '<a href="' . zen_href_link(zen_get_info_page($specials->fields['products_id']), 'cPath=' . zen_get_generated_category_path_rev($specials->fields['master_categories_id']) . '&products_id=' . $specials->fields['products_id']) . '">' . (($specials->fields['products_image'] == '' and PRODUCTS_IMAGE_NO_IMAGE_STATUS == 0) ? '' : zen_image(DIR_WS_IMAGES . $specials->fields['products_image'], $specials->fields['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a>') . '<br /><a href="' . zen_href_link(zen_get_info_page($specials->fields['products_id']), 'cPath=' . zen_get_generated_category_path_rev($specials->fields['master_categories_id']) . '&products_id=' . $specials->fields['products_id']) . '">' . $specials->fields['products_name'] . '</a><br />' . $products_price); $col ++; if ($col > (SHOW_PRODUCT_INFO_COLUMNS_SPECIALS_PRODUCTS - 1)) { $col = 0; $row ++; } $specials->MoveNext(); } require($template->get_template_dir('tpl_specials_default.php',DIR_WS_TEMPLATE, $current_page_base,'templates'). '/tpl_specials_default.php'); } } ?>

I am sorry but I am not seeing the problem in the code ...

I do not have a functional v1.3.8 to fool with to see if there is something to break this but it works on v1.3.9h and v1.5 ... :unsure:

I hope to have a little help from you .

After I eliminate all sold products for being listed by applying advices on this thread I still have an Issue . When I click on next product button ( product info page ) products already sold are coming out . Do you know which file should a check in order to prevent that ?

Thx

If you do not want Sold Out Products in the Listings, sideboxes or in the Previous/Next ... then do you want Sold Out Products to ever show?

If not, why don't you set your site up that Sold Out Products are marked as Out of Stock and they are turned off an do not show ...

I need products remain on stock in order to have google working links and to avoid a loop problem ( another own trhread ) but hide them from categories listing . Everything works ok except previous/next . Is there any solution ?

Try customizing the file:
/includes/modules/product_prev_next.php

using your templates and overrides and add the code in RED:

  $sql = "select p.products_id, p.products_model, p.products_price_sorter, pd.products_name, p.products_sort_order
          from   " . TABLE_PRODUCTS . " p, "
  . TABLE_PRODUCTS_DESCRIPTION . " pd, "
  . TABLE_PRODUCTS_TO_CATEGORIES . " ptc
          where  [B]p.products_quantity >= 1 and [/B]p.products_status = '1' and p.products_id = pd.products_id and pd.language_id= '" . (int)$_SESSION['languages_id'] . "' and p.products_id = ptc.products_id and ptc.categories_id = '" . (int)$current_category_id . "'" .
  $prev_next_order;

and see if that does what you want where you can still get to the product_info page for the Products with products_quantity 0, like a spider would, but not get to these products using the Previous/Next ...

Thank you so much Linda , worked perfect .

Have anice day !