Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Subject
- Posted on
- Mark Livingstone
November 6, 2009, 1:24 am
Hi,
This is the first time I am facing this problem and can't figure out
WHY is it when I pull data from MySQL, instead
of 10.98 from a certain cell, PHP shows "10.9799995422".
The cell stores data as a "float" in MySQL.
Here is what I do...:
$stmt = mysqli_prepare($mysql_db, "SELECT
`products`.`price`......................<blah blah>")
mysqli_stmt_execute($stmt)
mysqli_stmt_bind_result($stmt, $product['price'])
print $product['price']
and that's where I get the more decimal places than I should be :)
any idea why?
THANKS!
Re: "10.98" from MySQL shows up as "10.9799995422" in PHP - why?
Mark Livingstone wrote:
Because 10.98, like most floating point numbers, cannot be represented
exactly in a computer.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Re: "10.98" from MySQL shows up as "10.9799995422" in PHP - why?
Let me be more precise about it.
Most typically, the storage format will have decreasing powers of two after
the radix point, i.e 1/2, 1/4, 1/8, 1/16, etc. This is a basic feature of
computer arithmetic.
Assume you have a rational number, A'/B'. Assume that you reduce A'/B' to
its lowest terms (perhaps using Euclid's GCD algorithm), and call that
rational number A/B.
Once reduced, if B has as a prime factor any prime number other than 2, then
the number will be an infinitely repeating binary number.
An example or two might help.
98/100 reduced is 49/50 = (7 * 7) / 2 * 5 * 5. The "5's" in the denominator
ruin it. In binary, it is non-terminating.
Assume on the other hand that the number you want to represent is 10.6875.
The part after the decimal is 6875/10000 = (11 * 5 * 5 * 5 * 5) / (2 * 2 * 2
* 2 * 5 * 5 * 5 * 5) = 11/16.
You can decompose 11/16 as 1/2 + 1/8 + 1/16. So in binary the
representation will be 0.1011.
Note that in base-10, you run into the same effect. Any reduced rational
number whose denominator has a prime factor other than 2 or 5 (2 * 5 = 10)
is non-terminating.
So 1/125 will terminate, but 1/175 will not.
The former is 0.008, and the latter is non-terminating. The 7 as a prime
factor in the denominator ruins it.
I'm sure you can find many good links about computer arithemtic.
Datesfat
Re: "10.98" from MySQL shows up as "10.9799995422" in PHP - why?
wrote:
Thank you for the comprehensive answer :) However, theory doesn't
solve my problem. Which is... MySQL has 10.98 and I am getting a
different number. In fact, I KNOW it should be 10.98 since I was the
one who entered that into the MySQL cell.
So, how do I get PHP to properly read the cell contents?
Thanks.
Re: "10.98" from MySQL shows up as "10.9799995422" in PHP - why?
You could either round it to two decimal places, or store the number in a
different format -- one that won't suffer from this problem.
The former approach is on topic here. The latter is on topic in
comp.databases.mysql, but not in a php group.
Re: "10.98" from MySQL shows up as "10.9799995422" in PHP - why?
On Nov 5, 9:22 pm, spamb...@milmac.com (Doug Miller) wrote:
If you declare the field type as FLOAT or similar, this tells MySQL that
when you INSERT into the table, it should take the string it is given
("10.98" in this case), convert it to computer representation that MySQL
uses (and in this representation, 10.98 can't be represented exactly), and
manipulate it that way.
Here are your options:
a)Store the number as a string in MySQL.
b)Store the number as a rational number with two integer components.
c)Store the number as an integer with an implied denominator.
It really depends on what you want to do with the number.
If you want to sort based on the number, (a) probably won't do what you want
without more thought. If you know the range of the numbers you'll
manipulate, you might store it as "000010.980000" (and store every number as
exactly 13 characters). That way, the lexical string sort order is the same
as the numerical order, and MySQL can do this fairly efficiently.
I don't immediately see a way to sort using MySQL if you do (b).
If you do (c), again you can sort. 10.98 might be stored as 1098, an
integer. 11.00 would be stored as 1100. Etc. But you need to know the
range of your data in advance.
If you are just looking to avoid loss of precision, strings will do fine.
But if you want to have MySQL sort for you efficiently based on the number's
value rather than the string's lexical sort order (and if the two aren't the
same), you need to think a bit more.
Rounding will work, if you know in advance about the range of your data.
But you need to be careful there, to. 10.98 will work fine, but
100000000000000000000000000000.98 is another type of problem in floating
point.
Datesfat
Re: "10.98" from MySQL shows up as "10.9799995422" in PHP - why?
[silly suggestions snipped]
d) Define the column as DECIMAL (4, 2)
4 specifies the total number of digits, and 2 indicates the number of digits
right of the decimal point. If the OP needs, say, 4 digits left of the d.p.,
then he should use DECIMAL (6, 2).
We're getting off-topic for PHP, though... better to take this up in
comp.databases.mysql, where it's on topic. (Followups set)
Re: "10.98" from MySQL shows up as "10.9799995422" in PHP - why?
You don't seem to understand. The number you're getting (10.9799995422) *is*
"as it is stored in MySQL". The purpose of rounding it is to make sure that
you *don't* get the number "as it is stored in MySQL".
So the answer to your question is... No.
Rounding it will return the same value that you *entered* in MySQL.
Re: "10.98" from MySQL shows up as "10.9799995422" in PHP - why?
On Nov 5, 10:24=A0pm, spamb...@milmac.com (Doug Miller) wrote:
OK. So I guess it's all about computer arithemtics and the way the DB
stores data based on the the cell format. I thought that if I imported
"10.98" into the DB, then this is what I would get when exporting the
data from it. This doesn't seem to be the case with the "float" format
for that column.
Thanks for clarifying.
Re: "10.98" from MySQL shows up as "10.9799995422" in PHP - why?
Please also bear in mind that the only reason you don't normally lose data
when you're forming the decimal representation of a float is that 2 is a
prime factor of 10. Any number that has a terminating representation in
base-2 also has a terminating representation in base-10 (but not the
converse).
The data loss is in one direction only, normally.
Datesfat
Re: "10.98" from MySQL shows up as "10.9799995422" in PHP - why?
On Thu, 5 Nov 2009 19:44:17 -0800 (PST), Mark Livingstone
I think you need to go back and re-read what's written. Except you
don't really need to understand anything more than the differences
occur when trying to store a base 10 number in binary format.
The difference is so tiny you will always get your original number
back with round(). But you may be better off looking at number
format() if the values you are entering are currency and likely to
have trailing zeroes.
As others have said, if you are really bothered, change the data type
to text. PHP won't care, and you'll get out exactly what you put in.
--
Geoff Berrow (Put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs www.ckdog.co.uk/rfdmaker
Re: "10.98" from MySQL shows up as "10.9799995422" in PHP - why?
Not really.
The suggestion was that the OP use round() to avoid having to know about the
underlying machine representation used by MySQL.
For precision, that will work fine with numbers like 10.98 or 100.98 or
1000.98.
But there will come a point where the mantissa isn't large enough, and that
point may come sooner than the OP expects, and then we're back to the "MySQL
beat me on the head for no reason" kind of post. Once again, the user is
forced to know something about the underlying machine representation and how
it affects him.
It has been a while, but my guess is that a typical 32-bit float
representation has a mantissa somewhere around 26 bits.
That means that 1000000.98 probably won't work as expected with round().
The OP needs to understand that.
It is hardly silly to point that out.
Datesfat
P.S.--2500000000000000000000000000000000000000000000000000000000000000000000000000000.98
may be silly, but 1000000.98 is somewhat less silly. : )
Re: "10.98" from MySQL shows up as "10.9799995422" in PHP - why?
wrote:
I learned a lot of new stuff :)
Those cells always have simple $ values that with 2 decimal points at
the most. The highest price in the database will not exceed $100, so
100.98 will work with round() for the time being. I will look into
changing the DB structure.
Thans again to all who replied.
Re: "10.98" from MySQL shows up as "10.9799995422" in PHP - why?
Doug Miller wrote:
Until he retrieves it into a language such as PHP which doesn't have bcd
support (directly, anyway) - in which case it will again become a float
- and have rounding problems.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Re: "10.98" from MySQL shows up as "10.9799995422" in PHP - why?
Just a few parting comments:
a)My recollection is that the MySQL mailing list is a bit more responsive
than the newsgroup. If you hunt around on their site, you'll find it. It
is one of those automatic opt-in/opt-out things.
b)There is one poster who called my suggestion to map floats onto strings
"silly" or similar. In this case, he was right because MySQL has built-in
capability to handle decimal numbers (which I didn't know about).
However, in the general case ...
Usually what you're trying to achieve in a database table design is O(log N)
search time, i.e.
http://en.wikipedia.org/wiki/Big_O_notation
Sometimes when you're doing something unusual, you have to figure out if you
can map the data type you have onto an existing data type to achieve this.
The specific example I supplied was bad, but the concept is sound.
When you declare a table field as an index, you're telling MySQL to build an
auxiliary construct so it can achieve approximately O(log N) times.
But MySQL can only do this with certain data types. It can do it with
strings.
So, let's say that you are using keys of some sort that are 2048-bit
numbers. MySQL probably won't handle these natively. You may be able to
map these onto strings to get the effect you want. The requirement is that
you map in a way where the lexical sort order of the strings corresponds to
the ordering you want.
Your example is similar. Let's assume that MySQL doesn't have a data type
big enough to handle the dollar amounts you want with the precision you
want. You may be able to store the values as integer numbers of cents. I
think MySQL allows 64-bit unsigned integer fields, which would give you a
range up to 184,470,000,000,000,000.00, with precision to one penny, which
is quite a lot of money with a high precision.
However, if that doesn't work, mapping onto strings is the next logical
step. (I think). You just want to store them in a way where the lexical
sort order corresponds to the value order of the number stored.
I may have said something silly (as I did with the earlier suggestion). If
anybody knows of a better way to store a 2048-bit key or a huge sum of money
in a way where it can be indexed ... I look forward to reading it.
Good luck, Datesfat
Site Timeline
- » Anyone Have Experience with IPV6, Apache, and PHP
- — Next thread in » PHP Scripting Forum
- » question about smarty
- — Previous thread in » PHP Scripting Forum
- » URL redirection
- — Newest thread in » PHP Scripting Forum
- » Seamless SSO
- — Last Updated thread in » PHP Scripting Forum
- » need help creating a 404 file
- — The site's Newest Thread. Posted in » HTML Markup Language