Price database and confusing SQL query |
[eluser]MarcL[/eluser]
Hello. I've been coding up a product database which takes a number of XML feeds and records the prices of the products over time. I'd like to be able to monitor the price of say an iPod and see what the best price is at a number of different retailers on a given day. I'm pretty new to MySQL so I'd like some help on whether I'm representing my data correctly in the database tables and how to form the correct SQL query (using ActiveRecord but the whole query is fine too). So my data is in this format: product - id - name - description - etc merchant - id - name - description - etc prices - id - price - product_id - merchant_id - created_on (DATETIME that this price was added to the database) Have I represented my data in the prices table correctly or have I made it difficult for myself?! The query I'd like is something to get the latest prices from all merchants for a given product id. Thanks in advance for any help. Cheers, Marc
[eluser]umefarooq[/eluser]
hi if you have one product with multiple merchants and price is different from each other or same than its ok
[eluser]obiron2[/eluser]
looks good to me. SQL to get latest prices select p.merchant_id,p.price,max(p.created_on),m.name,m.description from prices p join merchant m on p.merchant_id = m.id where p.product_id = $productID group by p.merchant_id,m.name,m.description order by m.name SQL to get prices at a specific date select p.merchant_id,p.price,max(p.created_on),m.name,m.description from prices p join merchant m on p.merchant_id = m.id where p.product_id = $productID and p.created_on <= $PriceDate group by p.merchant_id,m.name,m.description order by m.name Obiron
[eluser]MarcL[/eluser]
I read that the people on the Code Igniter website were helpful but I didn't expect replies in under an hour. Thanks guys! @umefarooq Yes, there will be products with multiple prices from multiple merchants. I'm trying to record the change in prices over time and find out which merchant is the cheapest. Like a price comparison website. @obiron2 Thanks for the SQL. But the 'get latest prices' hasn't quite worked. The data I've got in the prices array for a certain product id (435) is: Code: product_id merchant_id price created_on When I run the query I get Code: merchant_id price max(p.created_on) name description Any ideas?
[eluser]umefarooq[/eluser]
try order by created_on either desc or asc not need to use max
[eluser]MarcL[/eluser]
Thanks umefarooq. I'll try it when I get home tonight (as I was working on it during my lunch hour) and I'll let you know if it works.
[eluser]kgill[/eluser]
Using asc or desc to solve this is the wrong way to go about it, that will give you all the prices including old ones but just stick them at the start or end of the list. The reason the other query didn't work is that the price is different for each of the dates so you can't include it in the same query as the max() & group by statements. Anytime you want to get the most recent set of values for a given group where one of the columns is dependent on the date you're looking at a subquery. Code: select prc.id, prc.price, prd.id, prd.name, mer.name Now the disclaimer, I work with Oracle on a daily basis so you might have to alter that query a bit.
[eluser]MarcL[/eluser]
Thanks for that query kgill. It worked great. Looks like I picked the wrong project to work on as an introduction to SQL! Now, can I reproduce the sub-query using ActiveRecord? Time to try!
[eluser]MarcL[/eluser]
Sorry for my ignorance but I've been having a look at ActiveRecord and I'm not sure that I can do the inner query with it. Is that right? Should I split this into 2 queries? If so, how do I do it with ActiveRecord? Or should I just run this as a query using $this->db->query()?
[eluser]TheFuzzy0ne[/eluser]
Code: // Set up your query here These are private functions, so this should work, but I would imagine there's a better way. I'm totally sure there is a much simpler way that I've overlooked, and another member is going to jump up and smack me round the head with it... Also, it will break when CodeIgniter eventually supports only PHP version 5 and up (if this ever happens). So please, keep an eye on this post in case someone, does indeed, offer you a real solution. |
Welcome Guest, Not a member yet? Register Sign In |