Sort By Then Sort By Again In Mysql

[petenaylor]

Hi all

 

I have a question that relates to fetching records from a mySQL databse using php.

 

Basically I have a table that stores product options and another table that stores the products themselves.

 

There can be one or more product options for each product but there is always 1.

 

The product options table has the product option ID, the product ID and the price.

 

Thre product table has the product ID, category ID, name etc...

 

 

The page has a $_GET on it with a category ID such as category.php?id=4&sortby=priceasc

 

I need to firstly group all the product options by product ID and sort by the lowest price or highest price.

 

Then I need to filter these by the category ID before outputting them to the browser.

 

Hope someone can help me.

 

Many thanks

 

Pete

[Scott_S]

What is taking care of the relationship, your code or the db?

[petenaylor]

Hi there, the database is taking care of the relationship.

[krisw44]

Give this a try:

 

SELECT *
FROM products LEFT JOIN options ON products.id = options.id
WHERE products.categoryid = '4'
ORDER BY options.price

 

That should link your tables, only return the results in the category that you want, and then sort it accordingly. Add a conditional to your code to determine asc or dec

Edited November 26, 2012 by krisw44

[petenaylor]

Hi there

 

That's great! I have an issue however, with the price. It seems to be ordering by price size and not price value. Here's the full query:

 

" SELECT * FROM products LEFT JOIN product_options ON products.id = product_options.product_id WHERE products.category_id = '21' GROUP BY product_options.product_id ORDER BY product_options.price DESC "

 

This does the following sort:

 

774: £2.99

1498: £2.75

1102: £14.99

1103: £11.99

1229: £13.19

8: £2.03

9: £1.75

 

As you can see it's sorting correctly to start with, and then does it by price size inbetween.

 

Do I need to edit the price fetch in the query?

 

Many thanks!

[krisw44]

Can you show us the structure of your tables? Is the price field a string?

[petenaylor]

I think it is becuase the prices are stored as a varchar, do they need storing as decimal?

[krisw44]

Yea, that would definitely help with the whole sorting process. If you want the symbols then have them added in the html/php instead of writing them to the database