sam123 Posted November 26, 2012 Author Share Posted November 26, 2012 Yes, i swapped the low and high prices and it worked for panasonic. But it is not working if more that one product of same type with different price. Please see the attachement for sample data. Thanks for your help. sample_data_v1.txt Quote Link to comment https://forums.phpfreaks.com/topic/270934-calculating-product-offer/page/3/#findComment-1395109 Share on other sites More sharing options...
Muddy_Funster Posted November 26, 2012 Share Posted November 26, 2012 @sam, The prodID in prod and whishlist tables are different. watchlist should get the prodID from pp_prod and every entry in the product table should have a unique prodID, that's why it was made as a primary key. If the features are different between products then the product is different and it goes in as a different item. You need to have unique identifiers for each record in order to maintain and query the data successfully. wishlist should use the prodID to refference the product from prod, If you duplicate the prodID in prod then bad things will happen. Â @barand, Liking the query you posted, It looks to me that you are using the ARP (which I ment to be Actual Retail Price) rather than calculating the discount price, is that right? Quote Link to comment https://forums.phpfreaks.com/topic/270934-calculating-product-offer/page/3/#findComment-1395127 Share on other sites More sharing options...
sam123 Posted November 26, 2012 Author Share Posted November 26, 2012 The problem what i am facing is that , the user is saying i am ready to pay this much amount for one product. May be that time there will not be any product added by the retailer. If after sometime the retailer added the 2 products with different price. eg. Samsung 30 LED and Samsung 40 HD and he is setting the offer if 3 customers request product of this price range he will give it for offerprice. Â So how do we do that..? Here i got confused. Can you please advice..? Quote Link to comment https://forums.phpfreaks.com/topic/270934-calculating-product-offer/page/3/#findComment-1395137 Share on other sites More sharing options...
Barand Posted November 26, 2012 Share Posted November 26, 2012 @Sam123, Does this fit the requirement? SELECT DISTINCT u.uid, u.e_mail, cnt.mfid, cnt.prodid, cnt.name, cnt.total, cnt.arp, cnt.discount_price FROM pp_watchlist w INNER JOIN pp_user u USING (uid) INNER JOIN ( SELECT p.mfid, p.prodid, p.ARP, p.name, COUNT(w.uid) as total, (SELECT MIN(discount_as_price) FROM pp_offers WHERE prodID = p.prodID AND no_required <= COUNT(w.uid) ) as discount_price FROM pp_watchlist w INNER JOIN pp_prod p ON w.prodid = p.mfid WHERE p.ARP BETWEEN w.priceLow AND w.priceHigh GROUP BY p.prodID ) as cnt ON w.prodID = cnt.mfid ORDER BY mfid, uid; Â @Muddy I use ARP to see if it is within the low-high range in watchlist - as required AFAIK Also I am not checking the three types of discount (as your CASE did). Trying to keep it simple as only the one type is being used. Quote Link to comment https://forums.phpfreaks.com/topic/270934-calculating-product-offer/page/3/#findComment-1395139 Share on other sites More sharing options...
Muddy_Funster Posted November 26, 2012 Share Posted November 26, 2012 yeah, thought that was it Barand, and keeping it simple is never a bad thing. I just thought that it was the discount price, not the ARP, that had to hit within the price watch ranges. To be honest this cURL problem I'm having is pickling my brain :'( So'm i'm gonna take a back seat for a while. Quote Link to comment https://forums.phpfreaks.com/topic/270934-calculating-product-offer/page/3/#findComment-1395144 Share on other sites More sharing options...
sam123 Posted November 26, 2012 Author Share Posted November 26, 2012 Thank you so much Barand. It worked. Quote Link to comment https://forums.phpfreaks.com/topic/270934-calculating-product-offer/page/3/#findComment-1395147 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.