SELECT products.*, collectionname, designers.`name` AS designame,  designers.hideprices, deals.id as deals_id, deals.oldprice, deals.offerprice, deals.endsat, 
                            (SELECT instock FROM prodstocks WHERE prodstocks.products_id = products.id AND prodstocks.instock>0 LIMIT 1 ) AS instock 
                        FROM products ,
                            prodscategs, collections, designers, prodimages, deals, prodscolors, colormap
                        WHERE products.id = prodscategs.products_id
                            AND products.collections_id = collections.id
                            AND designers.id = products.designers_id
                            AND products.id = prodimages.products_id 
                            AND deals.products_id=products.id
                            AND deals.published = 1
                            AND deals.endsat >= curDate()
                            AND products.id = prodscolors.products_id
                            AND prodscolors.colormaps_id = colormap.id
                            
                        GROUP BY products.id
                        ORDER BY collections.corder, stylenum ASC
                         LIMIT 0, 29 
                        
                        ;

 

There are 39 products.
Products you may be interested