Why is MySQL seeing 1000.00 as a smaller number than 34.00?

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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.