Excluding 1 Category from New Products

[libracorn]

Not sure if this is the right forum to ask this....

Is there a way I can exclude one category from appearing in the New Products selection? I have a fund-raising category that I do not want to appear as new products - products that are displayed under NEW on the main page of the store and products that list in the New Products link from the menu.

I was thinking there might be something I could hardcode to exclude the new section from picking up this category.

[libracorn]

Anyone? *** bump ***

[kobra]

You can adjust this in the DB, but unfortunately this function is by product and not by category. This is done by adjusting the add date back to one that will not be picked up as new.

There also might be a way to code this, but as I am php challenged, this would have to come from another.

[libracorn]

Yeah... I knew about the added date thing. My problem is there are products added to this category regularly enough that manually editing the dates would be a pain.

[Ajeh]

When adding to this Category that you want to exclude ... are these Products Linked Products to other categories? Or just belong to 1 Category?

If they are not Linked Products, then you could run a nifty little update on the products table from the Admin ... Tools ... Insert SQL Patches ... to fix the dates of all products where master_categories_id = XX

[libracorn]

Ah... yes that would work. Not sure how to write the replace statement code though. Could you help me with that?

So for instance, say the master_category is 20, how would the mysql statement syntax be written to replace those dates with 6/1/06?

[Ajeh]

If all the products to be excluded are master_categories_id = 20 ... you could add the statement:

AND master_categories_id != 20

To the 2 select statements to exclude them ...

[libracorn]

Sorry... I'm not following that exactly. Didn't you say to run some kind of replace sql statement to modify the dates? It sounds like what you just wrote is something I could put in the actual source code for how the new products are pulled from the database. Is that what you meant here? If so, which file would I add this to?

[Ajeh]

In /includes/modules/new_products.php are 2 select statements:

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
  $new_products_query = "select p.products_id, p.products_image, p.products_tax_class_id, p.products_price, p.products_date_added
                           from " . TABLE_PRODUCTS . " p
                           where p.products_status = 1 " . $display_limit;
} else {
  $new_products_query = "select distinct p.products_id, p.products_image, p.products_tax_class_id, p.products_date_added,
                                           p.products_price
                           from " . TABLE_PRODUCTS . " p
                           left join " . TABLE_SPECIALS . " s
                           on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " .
  TABLE_CATEGORIES . " c
                           where p.products_id = p2c.products_id
                           and p2c.categories_id = c.categories_id
                           and c.parent_id = '" . (int)$new_products_category_id . "'
                           and p.products_status = 1 " . $display_limit;
} 

You need to add another condition to not include the master_categories_id when it is 20 ...

In this case something like:

and p.master_categories_id != 20

to those two statements so that those products with the master_categories_id of 20 are blocked ...

Another way of doing it is to utilize another table, for the products_to_categories but the statement would be more complicated ...

[libracorn]

This should be working... but it's not. It's still displaying the products in that master category.

This is what I have changed it to which looks right... but category 4 products are still displaying in the new products list.

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
  $new_products_query = "select p.products_id, p.products_image, p.products_tax_class_id, p.products_price, p.products_date_added
                           from " . TABLE_PRODUCTS . " p
                           where p.products_status = 1 and p.master_categories_id != 4" . $display_limit;
} else {
  $new_products_query = "select distinct p.products_id, p.products_image, p.products_tax_class_id, p.products_date_added,
                                           p.products_price
                           from " . TABLE_PRODUCTS . " p
                           left join " . TABLE_SPECIALS . " s
                           on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " .
  TABLE_CATEGORIES . " c
                           where p.products_id = p2c.products_id
                           and p2c.categories_id = c.categories_id
                           and c.parent_id = '" . (int)$new_products_category_id . "'
                           and p.products_status = 1 and p.master_categories_id != 4" . $display_limit;
} 

oh... I also verified in the actual database that the master category ID for the products I'm referring to is 4. I did double check that and in the database it's showing 4 but the products are still in the list.