PHPNewbie55 Posted December 17, 2007 Share Posted December 17, 2007 I don't know if there is a work around for this but I am having a problem sorting records correctly based on PRICE. It seems that PHP/MySQL does not sort the records correctly when they are being sorted by a NUMBER... Example: I sort my data by PRICE -- ASCENDING... I get:: 10.17 10.98 10.99 108.49 110.98 99.98 98.49 97.50 It seems that it just goes by the first number and that's it... All 1's get put together no matter if it's 100 or 1000.. It happens in PHPmyADMIN too... so it seems to be a BUG in PHP... Any way to get PHP to actually sort the data correctly..?? Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted December 17, 2007 Share Posted December 17, 2007 What is the type of the field where the numbers are stored? If you are storing the numbers in a text field, then the numbers are sorted as ASCII, not numerical. Change the type to float and the sort will work. Ken Quote Link to comment Share on other sites More sharing options...
PHPNewbie55 Posted December 17, 2007 Author Share Posted December 17, 2007 Ahhh.. that makes sense.. it is sorting it based on the TYPE of FIELD it is... OK... Thanks! Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted December 17, 2007 Share Posted December 17, 2007 You should actually use a DECIMAL data type instead of float to avoid precision and rounding errors. Quote Link to comment Share on other sites More sharing options...
PHPNewbie55 Posted December 17, 2007 Author Share Posted December 17, 2007 Using FLOAT requires me to rewrite some code... no big deal... Using DECIMAL automatically rounds the number UP... So 198.95 becomes 199 automagically..... So DECIMAL will not work for a PRICE FIELD..... Quote Link to comment Share on other sites More sharing options...
revraz Posted December 17, 2007 Share Posted December 17, 2007 Decimal will work fine, you just have to set the number of digits to display after the decimal. http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html Quote Link to comment Share on other sites More sharing options...
PHPNewbie55 Posted December 17, 2007 Author Share Posted December 17, 2007 Cool... I'll try that.. either way I have some code to rewrite... The default LENGTH/VALUES for DECIMAL is 10,0 so it auto rounds the number up... If you set the LENGTH/VALUES to 10,2 it gives the decimal points without rounding. Thanks... just asking this one question I have learned a couple of things I didn't know before.....!!! VERY MUCH APPRECIATED.... 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.