petenaylor Posted November 26, 2012 Share Posted November 26, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/271178-sort-by-then-sort-by-again-in-mysql/ Share on other sites More sharing options...
Scott_S Posted November 26, 2012 Share Posted November 26, 2012 What is taking care of the relationship, your code or the db? Quote Link to comment https://forums.phpfreaks.com/topic/271178-sort-by-then-sort-by-again-in-mysql/#findComment-1395211 Share on other sites More sharing options...
petenaylor Posted November 26, 2012 Author Share Posted November 26, 2012 Hi there, the database is taking care of the relationship. Quote Link to comment https://forums.phpfreaks.com/topic/271178-sort-by-then-sort-by-again-in-mysql/#findComment-1395214 Share on other sites More sharing options...
krisw44 Posted November 26, 2012 Share Posted November 26, 2012 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/271178-sort-by-then-sort-by-again-in-mysql/#findComment-1395298 Share on other sites More sharing options...
petenaylor Posted November 27, 2012 Author Share Posted November 27, 2012 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! Quote Link to comment https://forums.phpfreaks.com/topic/271178-sort-by-then-sort-by-again-in-mysql/#findComment-1395506 Share on other sites More sharing options...
krisw44 Posted November 27, 2012 Share Posted November 27, 2012 Can you show us the structure of your tables? Is the price field a string? Quote Link to comment https://forums.phpfreaks.com/topic/271178-sort-by-then-sort-by-again-in-mysql/#findComment-1395517 Share on other sites More sharing options...
petenaylor Posted November 27, 2012 Author Share Posted November 27, 2012 I think it is becuase the prices are stored as a varchar, do they need storing as decimal? Quote Link to comment https://forums.phpfreaks.com/topic/271178-sort-by-then-sort-by-again-in-mysql/#findComment-1395559 Share on other sites More sharing options...
krisw44 Posted November 28, 2012 Share Posted November 28, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/271178-sort-by-then-sort-by-again-in-mysql/#findComment-1395939 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.