Limit products_new by category

[Kakor_98]

I try to limit products_new to only display products from one of my main categories (and that categories sub categories).

 

I have made a copy of the original file and renamed it to products_new_dvd.php

 

At the top of the file I specify the parent id:

 

//Parent id Cpath

$kategorinummer = '3';

//

 

 

The problem is how I write $products_new_query_raw so it only display products from that category. I use the following at the moment

 

// Products_new Query Row

 

$products_new_query_raw = "select p.products_id, pd.products_name, p.products_image_small_url, p.products_price, p.products_tax_class_id, p.products_date_added, p.manufacturers_id, m.manufacturers_name from " . TABLE_PRODUCTS . " p, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join, " . TABLE_PRODUCTS_TO_STORES . " p2s ON p.products_id = p2s.products_id where p2s.stores_id = '" . STORES_ID . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '$kategorinummer' and p.products_status = '1' and p.display_in_new = '1' and p.products_date_added > ('" . $this_year . "-" . $this_month . "-" . $this_day."') order by p.products_date_added DESC, pd.products_name";

 

 

//

 

 

I get the following error message when using it:

 

1064 - You have an error in your SQL syntax near ' products_to_stores p2s ON p.products_id = p2s.products_id where p2s.stores_id =' at line 1

 

 

select count(*) as total from products p, manufacturers m, products_description pd, categories c, products_to_categories p2c left join, products_to_stores p2s ON p.products_id = p2s.products_id where p2s.stores_id = '1' and p.products_id = pd.products_id and pd.language_id = '4' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '3' and p.products_status = '1' and p.display_in_new = '1' and p.products_date_added > ('2004-10-21')

 

[TEP STOP]

 

 

I have really no idea how I write the correct SQL syntax, since I'm a totally newbie at this. I have just copied and pasted som code from the new_products.php and my old products_new.php and tried to get it to work

 

I'm a using the "multi-store" and "Limit by date" contribution.

 

I would really appreciate all help I can get.

 

Thanks

 

Fredric

[ozEworks]

I would check all the quotes " and ' as they look a bit odd in places.

[Kakor_98]

Thanks for your reply Kym

 

I think that I have managed to solve this by using some code from the advanced search SQL-query instead.