Decimal fields for currency

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
SpiderMike
Forum Newbie
Posts: 3
Joined: Wed Feb 08, 2012 11:31 am

Decimal fields for currency

Post 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
litebearer
Forum Contributor
Posts: 194
Joined: Sat Mar 27, 2004 5:54 am

Re: Decimal fields for currency

Post by litebearer »

Do you want users to enter values with 10ths of a cent?
SpiderMike
Forum Newbie
Posts: 3
Joined: Wed Feb 08, 2012 11:31 am

Re: Decimal fields for currency

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Decimal fields for currency

Post 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.
Post Reply