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? Quote Link to comment 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! Quote Link to comment 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?!?! Quote Link to comment 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. Quote Link to comment 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 Quote Link to comment 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 Quote Link to comment 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 Quote Link to comment 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? Quote Link to comment Share on other sites More sharing options...
fenway Posted April 9, 2008 Share Posted April 9, 2008 You really want DECIMAL, not DOUBLE. Quote Link to comment Share on other sites More sharing options...
fri3ndly Posted April 9, 2008 Author Share Posted April 9, 2008 Sorted. Thanks Quote Link to comment 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.