Jump to content

Sort By Then Sort By Again In Mysql


Recommended Posts

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



Link to comment
Share on other sites

Give this a try:


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 by krisw44
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.