Page 1 of 1

Decimal fields for currency

Posted: Sat Feb 18, 2012 12:27 pm
by SpiderMike
Hi guys,
I need to know what's the best way to insert and display currency values in database.

I have a table like this:

id int unsigned auto_increment primary key,
name varchar(30)not null,
category varchar(30)not null,
boughtfor decimal(8,2),
boughtqty int(30),
total decimal(8,2),
saletax decimal(8,2),
date datetime);

The problem being that if a user enters a value like " 10.000", it will insert "10.00".
on the other hand If I change the decimal fields to "decimal(9,3)" if the user enters "10.35" it will insert something like "10.350".

I understand that I can use some php function with a condition to display the values right.
But still, my database values will be all messed up! :banghead:

Any light shed will be greatly appreciated.

Thanks,
Mike

Re: Decimal fields for currency

Posted: Sat Feb 18, 2012 2:29 pm
by litebearer
Do you want users to enter values with 10ths of a cent?

Re: Decimal fields for currency

Posted: Sat Feb 18, 2012 4:20 pm
by SpiderMike
Exactly. the values must be precise,
they can enter something like "14.545" or " 500.720.00" or " 10.25"

Thanks for your quickly reply.
Mike

Re: Decimal fields for currency

Posted: Sat Feb 18, 2012 8:29 pm
by califdon
SpiderMike wrote:Exactly. the values must be precise,
they can enter something like "14.545" or " 500.720.00" or " 10.25"

Thanks for your quickly reply.
Mike
500.720.00 ???? That's not a decimal value. What does this represent????

If you want users to enter precise values, don't you want to store those precise values? If you do, your database field must be defined with the same precision. Then if you need to display it differently, that's an entirely separate issue.