SELECT * FROM (
						(SELECT 'collec' as qfrom,
								deals.id as deals_id,
								deals.dealname, 
								deals.oldprice,
								deals.offerprice,
								deals.endsat,
								newarrivals.porder, newarrivals.opage,
								products.*, 1 as showorder, collectionname, collections.corder, null  as prdorder, designers.`name` AS designame,  designers.hideprices,  designers.prefix,
								COUNT(prodimages.products_id) as prdimgs,
								(SELECT instock FROM prodstocks WHERE prodstocks.products_id = products.id AND prodstocks.instock>0 LIMIT 1 ) AS instock ,
								if ((deals.products_id = products.id AND COALESCE(deals.prodsizes_id,0)=0 AND COALESCE(deals.colormaps_id,0)=0),1,0) as onsale,
								count(*) as cnt
							FROM newarrivals INNER JOIN products ON ( products.id = newarrivals.products_id )
									LEFT JOIN deals on (deals.products_id=products.id)
									LEFT JOIN collections ON ( products.collections_id = collections.id )
									LEFT JOIN designers ON ( designers.id = products.designers_id )
									INNER JOIN prodimages ON ( prodimages.products_id = newarrivals.products_id )
							WHERE newarrivals.opage=1
									AND newarrivals.products_id>0
							HAVING COUNT(prodimages.products_id)>0
							ORDER BY newarrivals.porder ASC, newarrivals.opage ASC
						)
						UNION
						(
						SELECT 'newarr' as qfrom,
								deals.id as deals_id,
								deals.dealname, 
								deals.oldprice,
								deals.offerprice,
								deals.endsat,
								newarrivals.porder, newarrivals.opage,
								products.*, 3 as showorder, collectionname, collections.corder, null  as prdorder, designers.`name` AS designame,  designers.hideprices,  designers.prefix,
								COUNT(prodimages.products_id) as prdimgs,
								(SELECT instock FROM prodstocks WHERE prodstocks.products_id = products.id AND prodstocks.instock>0 LIMIT 1 ) AS instock ,
								if ((deals.products_id = products.id AND COALESCE(deals.prodsizes_id,0)=0 AND COALESCE(deals.colormaps_id,0)=0),1,0) as onsale,
								count(*) as cnt
							FROM products left JOIN deals on (deals.products_id=products.id),
								newarrivals,collections,designers,prodimages
							WHERE newarrivals.opage=1
								AND newarrivals.collections_id=collections.id
								AND products.collections_id = collections.id
								AND designers.id = products.designers_id
								AND products.id = prodimages.products_id 
							GROUP BY newarrivals.porder ASC, newarrivals.opage ASC, stylenum ASC
							
							
							
						)
					) as newarvl
					 LIMIT 0, 29 
                    ;
Array
(
)
New Arrivals | Paris Dress

Dresses - Best Women’s Designer Dresses | Paris Dress 

There are 697 products.
 
Products you may be interested