Order by in mySQL with PHP

[petenaylor]

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.

[Psycho]

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

[petenaylor]

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.

 

[Barand]

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

[petenaylor]

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

[Barand]

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

[petenaylor]

That's it! That's all I needed, thanks for your help!

[Psycho]

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.

[Barand]

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.