SELECT cs.categories_storename,cs.categories_id,c.categories_id,c.parent_id,p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, pd.products_description, 
IF (
s.status, s.specials_new_products_price, NULL 
) AS specials_new_products_price, p.products_date_added, m.manufacturers_name
FROM products p, categories c, products_to_categories p2c
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN products_description pd ON p.products_id = pd.products_id
LEFT JOIN categories_stores cs ON c.parent_id = cs.categories_id
LEFT JOIN specials s ON p.products_id = s.products_id
WHERE c.categories_status = 1
AND cs.categories_storename = 'name.co.uk'
AND products_status = '1'
AND c.categories_id = p2c.categories_id
AND pd.language_id = '4'
AND p2c.categories_id = '<category id number goes here>'
LIMIT 4