Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

stray fractions on float mysql

6:32 pm on Feb 3, 2010 (gmt 0)

Junior Member

10+ Year Member

joined:June 12, 2004
posts: 119
votes: 0

I've got a field called "shipping" that's set as a FLOAT at 5,2. Due to an error in my PHP (not rounding a calculation), I found that INSERTS were being saved as 2.9800000035 and somehow that amount is even being saved in the database (shouldn't it have been dropped to 2 places with the 5,2?)

Now I'm trying to fix the problem. I temporarily changed the structure to 20,17 and can clearly see the stray numbers. In phpmyadmin I issued:

SELECT shipping from order WHERE shipping > 2.98 AND shipping < 2.99;

and get tons of results. So then I thought it would be simple to fix. I did:

UPDATE order SET shipping = '2.98' WHERE shipping > 2.98 AND shipping < 2.99;

and I get no changes. Why? And how to fix the entries in the database?

7:08 pm on Feb 3, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
votes: 0

What you should really use there, for ease of access and reliability, is the decimal type.

update order change shipping shipping decimal(12,2) not null default '0.00';

What you are experiencing is the annoying "feature" of floating point precision, and it exists in all programming languages (I think.) Here's a torrid tale of the problems with floating point precision in Javascript [webmasterworld.com].