petenaylor Posted August 28, 2012 Share Posted August 28, 2012 Hi all I am trying to write a piece of PHP code which will look through the attached database and fetch the product which has the cheapest price out of the 5 price columns. Sorry, I am not sure how to add in the mySQL into the text editor window, so I have attached a screenshot. Basically, I have a table for products and they will have up to 5 possible prices (price1 to price5) I need to write the PHP code for the mySQL query that brings back the ids numbers for the products by the cheapest. So in the attached screenshot it would be ID 6 as it has price3 as ?1.01. Then it would bring back ID 3 as price1 is ?17.00 then ID4 as the next cheapest price is ?17.99 (price1) Is this possible? Many thanks for your help. Quote Link to comment Share on other sites More sharing options...
Psycho Posted August 28, 2012 Share Posted August 28, 2012 In short, you need to revise your database to do this properly. It looks like you are trying to combine similar products that have different sizes. But, they are really different products. Some are showing the same Product Code for different sizes, but I have to assume that is incorrect. You can still create an association between products and get the results you want. I would create two tables such as: products & prod_items. The first would describe the general product and the second would describe the individual items (e.g. sizes) of that product. So, you can have one or many different items for the general product products id | catid | title | url | description | live prod_items item_id | prod_id | image | name | code | price You can then write a query to get just the lowest priced items for a particular product Quote Link to comment Share on other sites More sharing options...
petenaylor Posted August 29, 2012 Author Share Posted August 29, 2012 Hi there Thanks for your response, I have created a new table for the product options and attached it. What I need my script to do is order by the price column, smallest first and then bring back one instance of each of the product ids. Once I have the product ids in the correct order, I then need to pull info back from the database by the product id. Many thanks for your help. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 29, 2012 Share Posted August 29, 2012 Still don't know what info you want to pull back from the database but at least that was a little clearer than your other post on this topic (double posts are frowned upon BTW) . This will order by lowest price and pull the options for each prod id in that order SELECT p.product_id, p.name, p.code, p.price FROM product_options p INNER JOIN ( SELECT product_id, MIN(price) as minprice FROM product_options GROUP BY product_id ) as x USING (product_id) ORDER BY x.minprice, p.product_id, p.price Quote Link to comment Share on other sites More sharing options...
petenaylor Posted August 29, 2012 Author Share Posted August 29, 2012 Hi there That's great, is there anyway of just pulling one instance of the product ID? So the result will be 6,3,4,5 ? I am trying to get just one instance of the product ID in a list. Thank you, I really appreciate your help. I'll close the other post too. Pete Quote Link to comment Share on other sites More sharing options...
Barand Posted August 29, 2012 Share Posted August 29, 2012 There is but you say you want to pull info back from the database, which implies that you will then loop through those results performing further queries. As I told you in your other post, that should never be done as it is grossly inefficient, so I am trying to help you to get all the data you need in a single query first. But, if all you want is the ids, just run the subquery ordered by min price SELECT product_id, MIN(price) as minprice FROM product_options GROUP BY product_id ORDER BY minprice Quote Link to comment Share on other sites More sharing options...
petenaylor Posted August 29, 2012 Author Share Posted August 29, 2012 That's it! That's all I needed, thanks for your help! Quote Link to comment Share on other sites More sharing options...
Psycho Posted August 29, 2012 Share Posted August 29, 2012 That's great, is there anyway of just pulling one instance of the product ID? So the result will be 6,3,4,5 ? I am trying to get just one instance of the product ID in a list. Do you even understand what you are asking or what Barand or I have tried to explain to you? Just getting a list of product Ids from this new table is moronic. Those are foreign keys and it has no bearing on finding the lowest prices. If you just want a list of the product IDs then you should be doing that from the products table. It would be like sayng you need a list of author IDs and doing a query against the books table to get that info. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 29, 2012 Share Posted August 29, 2012 I know. Trying to help this guy is like banging your head against a brick wall. I guess the cure is to ignore any future posts. Quote Link to comment 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.