Storing money values in MySQL

Again.. your forgiveness. This is a MySQL question. If the MySQL mailing lists would include a [MySQL Help] tag in their subject lines, I'd use them. What I receive from them is difficult to distinguish from spam half the time so I gave up.

We had a problem a few months ago and now I can't find my notes relating to it. The problem we had involved storing money data as either float or decimal and having the 'cents' round improperly. And it wasn't just a matter of 10.05 becoming 10.06, it was something along the lines of 10.05 becoming 10.12.

This was when just a straight UPDATE was performed. I remember doing it manually, not even through PHP, using absolutely no math functions, just a totally straight:

UPDATE SomeTable SET AmtOwed = 10.74

Someone made a recommendation of "never use SOMETYPE for money.. you should use SOMEOTHERTYPE instead".

I've spent the better part of the afternoon trying to find my notes, recreate the bug in our database, search online for the discussion I had then or other information about this. So far I'm coming up empty.

We're retooling some of our database and trying to make it more efficient and accurate and this is on the list of things to doublecheck. If anyone has any comments, criticisms, information, etc... I'd love to hear them.

We're running MySQL 4.1.11-standard.

Thanks!

-TG

___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Storing money values in MySQL

Hi,

from the MySQL 4.1 manual:
---
The DECIMAL and NUMERIC data types are used to store exact numeric
data values. In MySQL, NUMERIC is implemented as DECIMAL. These types
are used to store values for which it is important to preserve exact
precision, for example with monetary data.
---
and there is lot more there at
http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html , check it
out, hope it helped.

Balazs

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Storing money values in MySQL

Me again,

Sorry for the rtfm stuff, I googled around and found this:

http://bugs.mysql.com/bug.php?id=3D10719

and another thing, maybe worth a try; MySQL stores decimal numbers as
strings...did you try to input decimals as string? I mean not:

UPDATE SomeTable SET AmtOwed =3D 10.74

but:

UPDATE SomeTable SET AmtOwed =3D '10.74'

Bye,

Balazs

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Storing money values in MySQL

Thanks Balazs and David.. I think that's exactly what I was looking for. I searched for hours (and I consider myself a fairly decent researcher) and was just getting frustrated..hah.. I think my problem was I was searching for "money" and "float problems" and such and not "monetary". I think that's the keyword that would have done it for me.

I appreciate the help guys! Best of luck to both of you (and anyone else I inadvertantly missed who may have responded).

-TG

= = = Original message = = =

Me again,

Sorry for the rtfm stuff, I googled around and found this:

http://bugs.mysql.com/bug.php?id=10719

and another thing, maybe worth a try; MySQL stores decimal numbers as
strings...did you try to input decimals as string? I mean not:

UPDATE SomeTable SET AmtOwed = 10.74

but:

UPDATE SomeTable SET AmtOwed = '10.74'

Bye,

Balazs


___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php