simboski19

Great. Ok. sorry this should (sorry) be the last thing. Each child product is associated with a merchant. Here is the final query: select product_id, product_name, product_description, product_image, product_brand, product_active, product_amazon_code_UK, product_amazon_code_US, product_amazon_code_DE, product_amazon_code_FR, masterid, catid, subcatid, mername, currency, min(feedprice) feedprice from ( select m.product_id AS product_id, m.product_name AS product_name, m.product_description AS product_description, m.product_image AS product_image, m.product_brand AS product_brand, m.product_active AS product_active, m.product_amazon_code_UK AS product_amazon_code_UK, m.product_amazon_code_US AS product_amazon_code_US, m.product_amazon_code_DE AS product_amazon_code_DE, m.product_amazon_code_FR AS product_amazon_code_FR, cj.masterid AS masterid, cj.catid AS catid, cj.subcatid AS subcatid, mr.merchantname AS mername, mr.currency AS currency, case when f.product_sale_price is not null then f.product_sale_price else f.product_price end feedprice from tblmpr m JOIN tblmpr_mpc_junction j ON (m.product_id = j.mprid) JOIN tblfeeds f ON (j.product_xmlid = f.product_xmlid) JOIN tbl_mpr_cat_junction cj ON (cj.mprid = m.product_id) JOIN tblmerchants mr ON (f.supplier_id = mr.merchantid) JOIN tblaffiliates af ON (m.product_affiliate = af.affiliateid) WHERE masterid = $masterid AND catid = $categoryid AND subcatid = $subcategoryid AND m.product_active = 1 AND f.product_active = 1 AND mr.active = 1 AND af.active = 1 $addgender $addbrand ) as x GROUP BY product_id $pricesort Problem is, even though there are 2 or 3 different children with different merchants it is only pulling out the first one by the looks of things. Any suggestions? Thanks Simon

Fantastic. Adam that is now working perfectly and I have streamlined it to work smoother. I assume much like other queries, it helps the speed to specify the fields instead of just selecting * (ALL fields)? Also, one last thing - why must you give use ') as x' at the end of the inner query? Many many thanks for the help. Simon

Adam, I am getting there, slowly but surely. The query is begining to make sense and pull my products out, however I have added in another table and it is now only pulling 1 product out instead of 2. This is the query I have now with the new table: $productqry = " select *, min(feedprice) feedprice from ( select m.product_id as product_id, m.product_name as product_name, m.product_description as product_description, m.product_image as product_image, m.product_brand as product_brand, m.product_active as product_active, m.product_amazon_code_UK as product_amazon_code_UK, m.product_amazon_code_US as product_amazon_code_US, m.product_amazon_code_DE as product_amazon_code_DE, m.product_amazon_code_FR as product_amazon_code_FR, cj.masterid as masterid, cj.catid as catid, cj.subcatid as subcatid, case when f.product_sale_price is not null then f.product_sale_price else f.product_price end feedprice from tblmpr m join tblmpr_mpc_junction j on (m.product_id = j.mprid) join tblfeeds f on (j.product_xmlid = f.product_xmlid) join tbl_mpr_cat_junction cj on (cj.mprid = m.product_id) WHERE masterid = $masterid AND catid = $categoryid AND subcatid = $subcategoryid order by feedprice ) as x"; To explain the new table: On the website there are many different categories and because a master product can be shown in many different categories I have another table named 'tbl_mpr_cat_junction'. This holds all of the master products and which section they should go in. You will see that I have added on a new join to this table and when this matches the variables: $masterid, $categoryid and $subcategoryid it will show the master products in this category. As I said earlier I should be able to see 2 prods but can only pull out 1. Also the price is not coming out - any thoughts. Thanks Simon

Ah I see that now Adam. I'll have another go now. Simon

Hi Adam, Sorry but I am having problems getting this right in my head and in the code. Can I show you how the tables are setup: table: master products => tblmpr table: children => tblfeeds junction table: master product id's - children id's => tbl_mpr_cat_junction So I pull out all of the products from the master product table. We then get the minimum priced child from the child table. And then we do the CASE to check if the sale price should be used. I think it may be in my joins that this is failing (I have obviously simplified the overall query): select `tblmpr`.`product_id`, `tblfeeds`.`product_sale_price` AS `feedsaleprice`, min(`tblfeeds`.`product_price`) AS `feedprice` from ( select `tblmpr`.`product_id` AS mprid, case when `tblfeeds`.`product_sale_price` is not null then `tblfeeds`.`product_sale_price` else `tblfeeds`.`product_price` end feedprice from `tbl_mpr_cat_junction` Inner Join `tblmpr` ON `tbl_mpr_cat_junction`.`mprid` = `tblmpr`.`product_id` Inner Join `tblmpr_mpc_junction` ON `tblmpr`.`product_id` = `tblmpr_mpc_junction`.`mprid` Inner Join `tblfeeds` ON `tblmpr_mpc_junction`.`product_xmlid` = `tblfeeds`.`product_xmlid` order by feedprice ) as x Thanks again. Simon

Hi, The query works like a treat, however I realised after I had used and tested it that I have another issue. I am no sure if this will be possible but thought I would ask anyway. Q. The query is returning the results in the correct low-to-high or vice versa order. This is all perfect. However I realised that there are two prices associated with each child item: a normal RRP price and a sale price. The way I had it working was that if a sale price was available (i.e. not empty) it would use this price instead of the normal RPP price. Is there a way of first checking this in the query before we then check the min price? Many thanks Simon

Brilliant Adam. Thanks again for your time and help. I will look at giving this a go and test tomorrow and let you know how I get on. Yeah I'll make sure I give them an alias. Thanks Simon

Thanks for the speedy replies. drisate: I have tried adding all products to an array and then sorting this however the page became very very slow so I don't want to use this method. Adam: To try and explain it a bit better this is the current way it works: - I have list of 5 Master Products (there are no prices associated to the Master Products). - My list of Master Products are all linked to some products: Child product 1 (£1.99), Child product 2 (£3.99), Child product 3 (£2.95). They all have different prices. - I only want to show the cheapest Child price (which are in a different table) for each Master Product. - I query the table to get all of the Master Products, of which there are 5. - In my while loop for each Master Product I then do a seperate query to get the cheapest Child product price to display. So I get something like as my list: Master Product 1 £1.99 Master Product 2 £3.99 Master Product 3 £2.95 Master Product 4 £1.99 Master Product 5 £2.95 I have a filter on the page which is High-to-Low and Low-to-High. However if I try and change the order I cannot because the actual prices are found using the inner query (which is different). Hope that clears it up slightly. Thanks Simon

Hello, I have developed a site which uses affiliate data to get lots of prices for individual products like kelkoo. I create a master product. Then I match the different affiliate products/prices to this master product. I then display these in a list format and calculate the cheapest price to show here. The problem I have is that I cannot then sort this list by price high-to-low or low-to-high as I cannot simply apply ORDER BY to the main sql which pulls out the master products. So I guess my question is, is there a way of pulling out the cheapest price and then sorting these master products after they have been pulled out or is the only way to sort using the first master query and have another field that is populated somewhere else? Many thanks Simon

Thanks a million HuggieBear. It worked first time. Simon

I am attempting to sort an array by price but cannot seem to get php to do this. Below is the array I am attempting to sort by the value ~ price: Array ( [http://pdt.tradedoubler.com/click?a(1774061)p(19045)prod(550992137)ttid(5)url(http%3A%2F%2Fwww.halfords.com%2Fwebapp%2Fwcs%2Fstores%2Fservlet%2Fproduct_storeId_10001_catalogId_10151_categoryId_165488_productId_561801_langId_-1%3Fsource%3Dtd)] => Array ( [logo] => nc0iicjxmq.gif [stock] => In Stock In Stock [price] => 14.99 [delivery] => 4.99 => http://pdt.tradedoubler.com/click?a(1774061)p(19045)prod(550992137)ttid(5)url(http%3A%2F%2Fwww.halfords.com%2Fwebapp%2Fwcs%2Fstores%2Fservlet%2Fproduct_storeId_10001_catalogId_10151_categoryId_165488_productId_561801_langId_-1%3Fsource%3Dtd) [mname] => Halfords ) [http://pdt.tradedoubler.com/click?a(1774061)p(19045)prod(767538675)ttid(5)url(http%3A%2F%2Fwww.halfords.com%2Fwebapp%2Fwcs%2Fstores%2Fservlet%2Fproduct_storeId_10001_catalogId_10151_categoryId_165497_productId_780417_langId_-1%3Fsource%3Dtd)] => Array ( [logo] => nc0iicjxmq.gif [stock] => In Stock In Stock [price] => 19.99 [delivery] => 4.99 => http://pdt.tradedoubler.com/click?a(1774061)p(19045)prod(767538675)ttid(5)url(http%3A%2F%2Fwww.halfords.com%2Fwebapp%2Fwcs%2Fstores%2Fservlet%2Fproduct_storeId_10001_catalogId_10151_categoryId_165497_productId_780417_langId_-1%3Fsource%3Dtd) [mname] => Halfords ) [http://pdt.tradedoubler.com/click?a(1774061)p(19045)prod(254177017)ttid(5)url(http%3A%2F%2Fwww.halfords.com%2Fwebapp%2Fwcs%2Fstores%2Fservlet%2Fproduct_storeId_10001_catalogId_10151_categoryId_165497_productId_271987_langId_-1%3Fsource%3Dtd)] => Array ( [logo] => nc0iicjxmq.gif [stock] => In Stock In Stock [price] => 15.00 [delivery] => 2.99 => http://pdt.tradedoubler.com/click?a(1774061)p(19045)prod(254177017)ttid(5)url(http%3A%2F%2Fwww.halfords.com%2Fwebapp%2Fwcs%2Fstores%2Fservlet%2Fproduct_storeId_10001_catalogId_10151_categoryId_165497_productId_271987_langId_-1%3Fsource%3Dtd) [mname] => Halfords ) [http://pdt.tradedoubler.com/click?a(1774061)p(19045)prod(550992487)ttid(5)url(http%3A%2F%2Fwww.halfords.com%2Fwebapp%2Fwcs%2Fstores%2Fservlet%2Fproduct_storeId_10001_catalogId_10151_categoryId_165488_productId_561805_langId_-1%3Fsource%3Dtd)] => Array ( [logo] => nc0iicjxmq.gif [stock] => In Stock In Stock [price] => 4.99 [delivery] => 2.99 => http://pdt.tradedoubler.com/click?a(1774061)p(19045)prod(550992487)ttid(5)url(http%3A%2F%2Fwww.halfords.com%2Fwebapp%2Fwcs%2Fstores%2Fservlet%2Fproduct_storeId_10001_catalogId_10151_categoryId_165488_productId_561805_langId_-1%3Fsource%3Dtd) [mname] => Halfords ) [http://pdt.tradedoubler.com/click?a(1774061)p(19045)prod(18823539)ttid(5)url(http%3A%2F%2Fwww.halfords.com%2Fwebapp%2Fwcs%2Fstores%2Fservlet%2Fproduct_storeId_10001_catalogId_10151_categoryId_165488_productId_155878_langId_-1%3Fsource%3Dtd)] => Array ( [logo] => nc0iicjxmq.gif [stock] => In Stock In Stock [price] => 18.00 [delivery] => 4.99 => http://pdt.tradedoubler.com/click?a(1774061)p(19045)prod(18823539)ttid(5)url(http%3A%2F%2Fwww.halfords.com%2Fwebapp%2Fwcs%2Fstores%2Fservlet%2Fproduct_storeId_10001_catalogId_10151_categoryId_165488_productId_155878_langId_-1%3Fsource%3Dtd) [mname] => Halfords ) [http://pdt.tradedoubler.com/click?a(1774061)p(19045)prod(580777789)ttid(5)url(http%3A%2F%2Fwww.halfords.com%2Fwebapp%2Fwcs%2Fstores%2Fservlet%2Fproduct_storeId_10001_catalogId_10151_categoryId_242553_productId_385055_langId_-1%3Fsource%3Dtd)] => Array ( [logo] => nc0iicjxmq.gif [stock] => In Stock In Stock [price] => 21.99 [delivery] => Free Shipping => http://pdt.tradedoubler.com/click?a(1774061)p(19045)prod(580777789)ttid(5)url(http%3A%2F%2Fwww.halfords.com%2Fwebapp%2Fwcs%2Fstores%2Fservlet%2Fproduct_storeId_10001_catalogId_10151_categoryId_242553_productId_385055_langId_-1%3Fsource%3Dtd) [mname] => Halfords ) [http://pdt.tradedoubler.com/click?a(1774061)p(19045)prod(18823503)ttid(5)url(http%3A%2F%2Fwww.halfords.com%2Fwebapp%2Fwcs%2Fstores%2Fservlet%2Fproduct_storeId_10001_catalogId_10151_categoryId_165497_productId_217063_langId_-1%3Fsource%3Dtd)] => Array ( [logo] => nc0iicjxmq.gif [stock] => In Stock In Stock [price] => 24.99 [delivery] => Reserve and Collect => http://pdt.tradedoubler.com/click?a(1774061)p(19045)prod(18823503)ttid(5)url(http%3A%2F%2Fwww.halfords.com%2Fwebapp%2Fwcs%2Fstores%2Fservlet%2Fproduct_storeId_10001_catalogId_10151_categoryId_165497_productId_217063_langId_-1%3Fsource%3Dtd) [mname] => Halfords ) [http://track.webgains.com/click.html?wgcampaignid=72782&wgprogramid=2730&product=1&wglinkid=90005&productname=Continental+Sport+Contact+Road%2FHybrid+Bicycle+Tyre&wgtarget=http://www.awcycles.co.uk/brands/Continental/Sport_Contact_Road/Hybrid_Bicycle_Tyre] => Array ( [logo] => 8yfd588e8t.jpg [stock] => In Stock In Stock [price] => 21.99 [delivery] => => http://track.webgains.com/click.html?wgcampaignid=72782&wgprogramid=2730&product=1&wglinkid=90005&productname=Continental+Sport+Contact+Road%2FHybrid+Bicycle+Tyre&wgtarget=http://www.awcycles.co.uk/brands/Continental/Sport_Contact_Road/Hybrid_Bicycle_Tyre [mname] => AW Cycles ) ) Any help would be much appreciated. Thanks Simon