Sort order :: specials products

[xergos055]

How ken i put in this conditions to show me the specials products from the categori with id=120 ?

$specials_query_raw = "SELECT p.products_id, p.products_image, pd.products_name, pd.products_description, 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 pd.language_id = :languagesID


ORDER BY s.specials_date_added DESC";

Please help !

[xergos055]

Nobody ?

[xergos055]

I try to show the special products from category with id=120, and i using that interogation to mysql:

$specials_query_raw = "SELECT p.products_id, p.products_image, pd.products_name, pd.products_description, p.master_categories_id, cd.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
LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd on cd.categories_id = 120 )
WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1' and cd.categories_id = 120
AND s.status = 1
AND cd.categories_id = 120
AND pd.language_id = :languagesID
ORDER BY s.specials_date_added DESC";

The results the same "IN THE SPECIAL PAGE I HAVE ALL TLE PRODUCTS WITH SPECIALS PRICE" is not show the products with SPECIAL PRICE from category with id 120

Anyone ken help me please !

[Ajeh]

To limit the Specials to categories_id on that select, use:
AND p.master_categories_id = 120

vs the:
AND cd.categories_id = 120

This forces the Master Category of the products table to be 120 ...

[xergos055]

I'change :

$specials_query_raw = "SELECT p.products_id, p.products_image, pd.products_name, pd.products_description, p.master_categories_id, cd.categories_id
FROM (" . TABLE_PRODUCTS . " p

LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd on cd.categories_id = 120 and cd.categories_name = 'Aparatori'
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 p.master_categories_id = 120
AND s.status = 1
AND p.master_categories_id = 120
AND pd.language_id = :languagesID
ORDER BY s.specials_date_added DESC";

and now I no longer show any product in specials page .

[Ajeh]

Check the Products that you think are on Special and in categories_id 120 and see if they also are using the master_categories_id 120 in the products table ...

[xergos055]

Yes, and i have:

index.php?main_page=product_info&cPath=120&products_id=1923

and the product with id=1923 is on specials

In database i have: on table products: master_categories_id=0 (all are 0)