[RESOLVED] INSERT a long decimal place

perpetualshaun

I'm trying to insert the number '84.7666666666666667' into MySQL. I've tried setting the field type as both FLOAT and DECIMAL (16,16) and getting some weird results either way.

What is the right field type to capture a number with that may places after the decimal point?

Thanks,

Shaun Worcester | senior webologist

design chemistry, llc | next generation web site design
p. 800 640 0424 x 105 | http://www.designchemistry.com/

Connect to me: http://www.google.com/profiles/shaun.daytonwebheads
Follow us on Twitter: http://twitter.com/daytonwebdesign
Become a fan on Facebook: http://www.facebook.com/daytonwebdesign

Weedpacket

What are the "weird results" and do you really need that much precision (you're kind of pushing the limits of double-precision arithmetic there)?

And are you sure it's the database?

php -r "echo 84.7666666666666667;"
84.7666666666666657192764

The processor kind of loses focus after the fourteenth digit or so...

perpetualshaun

It's doing some rounding in MySQL. When I had it as the DECIMAL type (16,16) it was always rounding to like 90.9999999999 and now that I have it set as FLOAT, it's rounding everything to 1.000000000.

If I set it to DECIMAL or FLOAT (3,2) will it automatically round up to the next highest second decimal place number? Ultimately, that's the amount of precision I need for this site and was just planning on doing the rounding with PHP. But as long as the rounding in MySQL will work the same way, that will suffice.

--
Shaun Worcester | senior webologist

design chemistry, llc | next generation web site design
p. 800 640 0424 x 105 | http://www.designchemistry.com/

Connect to me: http://www.google.com/profiles/shaun.daytonwebheads
Follow us on Twitter: http://twitter.com/daytonwebdesign
Become a fan on Facebook: http://www.facebook.com/daytonwebdesign

perpetualshaun

Here's some other examples...

In my query, I'm inserting the values: '84.7666666666666667', '84.7666666666666667 '

And I have those last two fields set as decimal(3,2)

... and then via PHP MyAdmin, I see it recorded the values of 9.99 for each of those fields.

Huh?? What can I change the field type to in order to ensure that it records properly in the database?

--
Shaun Worcester | senior webologist

design chemistry, llc | next generation web site design
p. 800 640 0424 x 105 | http://www.designchemistry.com/

Connect to me: http://www.google.com/profiles/shaun.daytonwebheads
Follow us on Twitter: http://twitter.com/daytonwebdesign
Become a fan on Facebook: http://www.facebook.com/daytonwebdesign

bradgrafelman

perpetualshaun wrote:
I see it recorded the values of 9.99 for each of those fields.

Huh??

Sounds about right. By specifying 2 for the second parameter, you're telling MySQL that the numbers must have 2 decimal places. The max precision of "3" then implies that the range of DECIMAL(3,2) must be -9.99 to +9.99. The closest value within that range to 84.7666666666666667 is 9.99, thus that's what MySQL stored in those columns.

perpetualshaun

Ahh... I got it. Thanks!

--
Shaun Worcester | senior webologist

design chemistry, llc | next generation web site design
p. 800 640 0424 x 105 | http://www.designchemistry.com/

Connect to me: http://www.google.com/profiles/shaun.daytonwebheads
Follow us on Twitter: http://twitter.com/daytonwebdesign
Become a fan on Facebook: http://www.facebook.com/daytonwebdesign