Need help on new_products.php SQL statement

[LukeDouglas]

I'm having a problem in excluding items with categories.parent_id = '34' in my new_products.php page.

 

Basically, I want it to display new products for all categories except where the categories.parent_id = '34'. The '34' parent_id is for 'Toy Chest' which I DO NOT want to display new products for.

 

Here is the original SQL:

 

$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

 

Here is my modified SQL (which only shows one item for all new products):

 

$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as 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 products_status = '1' and c.parent_id <> '34' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

 

In looking at the database tables, here is what I found:

 

The master categories description file:

categories_description.categories_id = "34"

categories_desscription.categories_name = "Toy Chest"

 

The categories file:

categories.id = 40"

categories.parent_id = '34'

 

The products to categories file:

products_to_categories.products_id = "66"

products_to_categories.categories_id = "40"

 

With this information, I know that product_id '66' is in category_id '40' which is a sub-category of parent_id '34'. So for product_id = '66', I would not want that to display in the new products section on the store home page. I'm know I have made a mistake in my SQL modifications as it only displays 1 item 9 times in the new products section and that item does link to categories.parent_id = "34".

 

Can anyone tell me how to correct this SQL statement?

[♥bruyndoncx]

Try this one

 

$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as 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 products_status = '1' and p.products_id = p2c products_id and p2c.categories_id = c.categories_id and c.parent_id <> '34' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);