How do I keep Sold Items from appearing on new products?

I am looking though the archives but have yet to find this answer. Any help would be greatly appreciated.
I keep all of my products, even the SOLD ones in the shop. The reason being is that if folks want to leave a comment or a review, the item still has to be present, whether sold or not. Not wanting to forfeit any opportunities for reviews......all products stay.....sold or not.

My question is, how to do I get SOLD products to NOT show up in the New Products on the main/front page of the cart? Is there a way to do this? I hear it is frustrating to find a product they have been looking for, only to click on it and see it is sold. Shopping carts can be a frustrating beast to those who are not familiar, so i want to make this as seamless and stress free as possible :)

So, is there a way to make sure SOLD products do not show up in the new products?

Thank you.

you could modify the new products sql to omit any products with quantity = 0. (assuming you are using the quantity fields on products).

in /includes/modules/pages/products_new/header.php, change this:

$products_new_query_raw = "SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price,
                                    p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model,
                                    p.products_quantity, p.products_weight, p.product_is_call,
                                    p.product_is_always_free_shipping, p.products_qty_box_status,
                                    p.master_categories_id
                             FROM " . TABLE_PRODUCTS . " p
                             LEFT JOIN " . TABLE_MANUFACTURERS . " m
                             ON (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd
                             WHERE p.products_status = 1
                             AND p.products_id = pd.products_id
                             AND pd.language_id = :languageID " . $display_limit . $order_by;

to this: (note the additional "AND" statement)

$products_new_query_raw = "SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price,
                                    p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model,
                                    p.products_quantity, p.products_weight, p.product_is_call,
                                    p.product_is_always_free_shipping, p.products_qty_box_status,
                                    p.master_categories_id
                             FROM " . TABLE_PRODUCTS . " p
                             LEFT JOIN " . TABLE_MANUFACTURERS . " m
                             ON (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd
                             WHERE p.products_status = 1
                             AND p.products.quantity > 0
                             AND p.products_id = pd.products_id
                             AND pd.language_id = :languageID " . $display_limit . $order_by;

That looked like something I could use, so I dropped the line of php in, hit save, blew up my site. :ohmy:
I'm looking to see if I fat-fingered it, but as far as I can tell I added that one line and nothing else, no stray comma or (), just:
AND p.products.quantity > 0

????

joejoejoe:

That looked like something I could use, so I dropped the line of php in, hit save, blew up my site. :ohmy:
I'm looking to see if I fat-fingered it, but as far as I can tell I added that one line and nothing else, no stray comma or (), just:
AND p.products.quantity > 0

????
Change the SQL fragment from AND p.products.quantity > 0 to AND p.products_quantity > 0

Thanks, I changed the . to _ and it works.