fri3ndly Posted April 8, 2008 Share Posted April 8, 2008 I have a website where users can sort data. The price fields are VARCHARS and are displayed as follows: £145.99 £129.99 £62.50 £84.00 £99.99 I can see the logic as to why MYSQL is sorting them with the 'least' at the top, but how do I owercome this? Link to comment https://forums.phpfreaks.com/topic/100116-solved-sorting-by-price-%C2%A3100-is-less-than-%C2%A312/ Share on other sites More sharing options...
friedemann_bach Posted April 8, 2008 Share Posted April 8, 2008 I do not know any modification of the ORDER BY statement that would sort integers within strings. I can thik of the following solutions: 1) If possible, try either to split up the data colums into a currency and a value columns, then using a single character for $ or £ and a real number for the value. If all values have the same currency, omit it. 2) You could also extract the values to an array that is then sorted by sort($array). 3) If nothing helps, try inserting leading zeros after the currency sign, so that all strings have the same length. This is ugly, though. For a nice output, you would have to delete the zeros after sorting for output. Generally, I wouldn't recommend this. Hope that helps! Link to comment https://forums.phpfreaks.com/topic/100116-solved-sorting-by-price-%C2%A3100-is-less-than-%C2%A312/#findComment-511935 Share on other sites More sharing options...
fenway Posted April 8, 2008 Share Posted April 8, 2008 Why on earth are you price fields VARCHARS?!?! Link to comment https://forums.phpfreaks.com/topic/100116-solved-sorting-by-price-%C2%A3100-is-less-than-%C2%A312/#findComment-511982 Share on other sites More sharing options...
aschk Posted April 9, 2008 Share Posted April 9, 2008 I echo fenway's concern. Prices should be stored as numeric/double/float/int (probably not the latter). "What about currency?" i hear you say, well then you need a currency field indicating the currency that the item price is in, i.e. GBP to £ or USD for $, or whatever the shortcode is for the currency. Link to comment https://forums.phpfreaks.com/topic/100116-solved-sorting-by-price-%C2%A3100-is-less-than-%C2%A312/#findComment-512761 Share on other sites More sharing options...
friedemann_bach Posted April 9, 2008 Share Posted April 9, 2008 I totally agree to both of you, as you might see in my proposal (1), though I expressed that in a much too polite way Link to comment https://forums.phpfreaks.com/topic/100116-solved-sorting-by-price-%C2%A3100-is-less-than-%C2%A312/#findComment-512785 Share on other sites More sharing options...
fri3ndly Posted April 9, 2008 Author Share Posted April 9, 2008 Well the field has the '£' sign in it as well as the amount. I stupidly tried changing the type to decimal value yesterday and lost ALL my price fields. Was not happy, but have backed up db now Link to comment https://forums.phpfreaks.com/topic/100116-solved-sorting-by-price-%C2%A3100-is-less-than-%C2%A312/#findComment-512791 Share on other sites More sharing options...
fri3ndly Posted April 9, 2008 Author Share Posted April 9, 2008 I have now made the data in the fields numeric only in the following form: 12.25 99.99 99.00 34.00 Changed the fields to double and it worked perfect. Thanks Link to comment https://forums.phpfreaks.com/topic/100116-solved-sorting-by-price-%C2%A3100-is-less-than-%C2%A312/#findComment-512802 Share on other sites More sharing options...
fri3ndly Posted April 9, 2008 Author Share Posted April 9, 2008 I have just noticed that its perfect apart from one thing. Where a price is for instance £49.50 or £36.90 it is chopping off the 0 at the end. What can I do about this? Link to comment https://forums.phpfreaks.com/topic/100116-solved-sorting-by-price-%C2%A3100-is-less-than-%C2%A312/#findComment-512889 Share on other sites More sharing options...
fenway Posted April 9, 2008 Share Posted April 9, 2008 You really want DECIMAL, not DOUBLE. Link to comment https://forums.phpfreaks.com/topic/100116-solved-sorting-by-price-%C2%A3100-is-less-than-%C2%A312/#findComment-512926 Share on other sites More sharing options...
fri3ndly Posted April 9, 2008 Author Share Posted April 9, 2008 Sorted. Thanks Link to comment https://forums.phpfreaks.com/topic/100116-solved-sorting-by-price-%C2%A3100-is-less-than-%C2%A312/#findComment-512986 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.