How do you order by Asc and DESC with decimal numbers in MySQl database?

[imgrooot]

I have a Mysql table where I store prices of a product.  I have the values in "varchar" format because I needed to include "decimals" in the prices(for eg. 15.30) and "int" doesn't allow that.

Having done that, when ever I filter the products by prices in Ascending or Descending order, they don't show up in proper order. It seems like the decimal in the prices is messing up the order.

 

Is there a way to fix this so that the php query can filter the prices in proper order despite the decimals?

 

Here's the eg of the query.

$get_records = $db->prepare("SELECT * FROM records ORDER BY records.price DESC");

[mac_gyver]

you would want to store the data in column defined as a decimal data type - http://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html

[imgrooot]

you would want to store the data in column defined as a decimal data type - http://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html

 

 

There is a problem with that though.  It removes any number after the decimal and instead adds .00 to it.

[Jacques1]

Not with a proper ALTER TABLE statement.

ALTER TABLE records MODIFY price DECIMAL(10, 2);

[imgrooot]

 

Not with a proper ALTER TABLE statement.

ALTER TABLE records MODIFY price DECIMAL(10, 2);

 

Yes you're right.  Thanks a bunch.