0

I have 2 products, one is a thousand dollars so for it's price I inserted into the database:

1000.00

and nother thats 34 dollars so I inserted:

34.00

But when I sort the products in order by price (low to high) the $1,000 product comes before the $34 one, why?

The query:

SELECT * FROM products ORDER BY price ASC;

EDIT

It's a varchar field. I would have used int but I need the decimal to be accepted.

If I turn it to int, is there any way I can convert something like this:

10000

to a more user friendly form when display to the user like:

$10,000

I am using PHP for scripting by the way.

James
  • 5
  • 2

3 Answers3

4

Because strings are sorted lexicographically. You need to cast it to a number to be sorted numerically.

SELECT * FROM products ORDER BY CAST(price AS DECIMAL) ASC;

(It may be better to store the price as INTEGERs as multiple of cents (100000 and 3400), or use DECIMAL.)

Edit: You can use money_format in PHP to convert a number to currency format. See http://www.ideone.com/VEcgy for an example.

kennytm
  • 510,854
  • 105
  • 1,084
  • 1,005
  • that is a pretty nice question inst double more applicable for currency ? – Prix Jul 03 '10 at 19:03
  • 2
    @Prix: [ __No__ !](http://stackoverflow.com/questions/61872/use-float-or-decimal-for-accounting-application-dollar-amount/66678#66678) – kennytm Jul 03 '10 at 19:06
3

Lexically speaking, 1000.00 is less than 34.00. What data type is price?

Chris
  • 1,303
  • 8
  • 6
0

You've stored price as an varchar, but indeed it is numeric ;)
Use DECIMAL(10,2) or DOUBLE(10,2) to store the price, both types preserve the point.

Tobias P.
  • 4,537
  • 2
  • 29
  • 36
  • I actually prefer DECIMAL(10,4) as people like the IRS can specify sub 1 cent values (such as car mileage rebate rates) – Peter M Jul 03 '10 at 19:10