best way to deal with prices on a database?

[fael097]

hi, im making a database for a really simple e commerce, and i need to publish products, for that ill have to store their prices on the database. i tried storing them with dots for the cents, but if i sort by price, it will return weird results like 530.00 510.00 50.00 490.00 (it will put the 50.00 between 500 and 400, like 50 was the same as 500) so dot is not an option.

also, making a field for the value and another for the cents, sounds strange, but so far is the only option. anyone have better ideas?

 

i also need a way to make users type the cents value on the price field, like a javascript that automatically adds a dot for the last 2 digits when you type something. also need better ideas on this.

 

thanks in advance

[Mchl]

There are two approaches.

1. Use DECIMAL(x,2) field. It will store x digits with two decimal places

2. Use INT to store your prices multiplied by 100 (i.e. store the prices in cents).

[myrddinwylt]

Personally I use "DOUBLE" as the field data type which allows for negative numbers as well as positive, and the field length, i set 20, for decimals, i set between 2 and 8 (depending on the precision of the application).

[Mchl]

Personally I use "DOUBLE" as the field data type which allows for negative numbers as well as positive, and the field length, i set 20, for decimals, i set between 2 and 8 (depending on the precision of the application).

 

DOUBLE or DOUBLE(x,y)? DOUBLE (floating point) is probably the worst datatype you can set for monetary values.