PHP Math

I am pulling numbers from a mysql database of type decimal(15,2).

I am having a problem with doing simple subtraction with PHP and it relates to negative numbers.

Say I got two numbers -64.05  and -64.05 and these need to be subtracted. In math, it should read as -64.05 + 64.05 to make 0.  But no matter how I script it, using abs(#) or converting the second number into a positive another way it comes out being equal to -1.8474111129763E-13

Or some other random string depending on the values.

It seems so basic, what am I missing?
the-mizAsked:
Who is Participating?
 
IanStatisticianCommented:
Hi there the-miz,

A slight typo in my first comment above ( ID: 39280356  )

(set sum <- 0,  then repeat (multiply by 10 and add next digit) until you run out of digits - skipping over the decimal point)
next multiply by 10.0 ^ (number of digits to the right of decimal point)


should be

(set sum <- 0,  then repeat (multiply by 10 and add next digit) until you run out of digits - skipping over the decimal point)
next multiply by 10.0 ^ (- number of digits to the right of decimal point)

The minus sign was missed out. Alternatively the word multiply could be divide.

-------


In your question text you said
...  it comes out being equal to -1.8474111129763E-13

Or some other random string depending on the values.

What you get is not random at all. It is going to be equal (or very close to) 2 ^ (-k) where k depends on the size (in bits) allocated to the fraction part in the floating point representation in the hardware you are using subtracted from the exponent ( in base two) of the absolute value of the larger number.

I say depends on because the value may be +/- a few bits which are hardware related.  Some floating point processes actually perform the arithmetic in extended precision and truncate/round result for storage back in memory - this is likely to increase precision by at least 1 bit.  Some floating point representations don't have the exponent as a power of two - the IBM mainframe has the exponent as a power of 16 (4 bits) so precision is in chunks of 4 bits.  

All this adds up to you should always be careful about subtracting or comparing floating point numbers.  You can get round the comparison problem by using a language that provides you with automatic fuzz.

This is shown up dramatically where you generate a matrix with random values chosen from a distribution. You then invert the matrix (find inverse). Finally you matrix multiply both of these together, which should produce the identity matrix (1's down the leading diagonal, zeros every where else).  Typically, when printed, all the off diagonal elements are small but non-zero numbers resulting from the subtraction of (what should really be but aren't) identical numbers.

Hope all this background helps in your understanding of the problem.

Ian
0
 
Chris StanyonCommented:
We really need to see code. Subtracting your two numbers would normally look something like this:

-64.05 - -64.05 = 0 (double negative = positive)

Try this:

<?php 
$a = -64.05;
$b = -64.05;
echo $a - $b; //will echo 0
?>

Open in new window

0
 
ScorchDCommented:
If you just try something very basic like the following?

$num1 = -64.05;
$num2 = 64.05;
echo $num1 + $num2;
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
ScorchDCommented:
Apologies was writing that at the same time as Chris
0
 
Robert SaylorSenior DeveloperCommented:
I ran into this issue a long time ago and I think I had to declare the variable first.

http://php.net/manual/en/function.settype.php

I can not remember what file I worked on to show an example. It's been a while since I ran into this problem.
0
 
Dave BaldwinFixer of ProblemsCommented:
PHP does not have a 'decimal' number format.  If it has a decimal point in it, it is handled as floating point which is notorious for being inexact in the small details.  -1.8474111129763E-13 is a number with 12 zeros after the decimal point.

-0.00000000000018474111129763

That's about as close to zero as you get in most floating point calculations.

http://php.net/manual/en/language.types.float.php
0
 
the-mizAuthor Commented:
$sql = "select num1, num2 from db_name";
$result=mysql_query($sql);
$row=mysql_fetch_row($result);

$number1 = $row[0];
$number2 = $row[1];

$total = $number1 - $number2;

Open in new window


The database stores the numbers. If they are both exactly the same and negative it comes out with that strange output.
0
 
ScorchDCommented:
if you print_r on $row do you see what you would expect?
0
 
the-mizAuthor Commented:
interesting find...  I echo the two numbers as it is in a while loop to create a spreadsheet look.  I noticed it works when subtraction is -187.8 - -187.80 but not for -2191.25 - -2191.25 and it also gets a funky output for your basic subtraction, for example: 224.27 - 224.27

Its weird!
0
 
Chris StanyonCommented:
FYI - You should be moving away from the mysql library and switching to either mySQLi or PDO. Here's a test I ran using PDO:

<?php 
$sql = "select num1, num2 from math";
$row = $dbh->query($sql)->fetch(PDO::FETCH_NUM);

$number1 = $row[0];
$number2 = $row[1];

$total = $number1 - $number2;

echo $total; //this echoes out 0
?>

Open in new window

0
 
the-mizAuthor Commented:
print_r looks fine, producing the numbers as expected. I find it truly odd it correctly subtracts without one number having an ending zero an the other does. ie. -187.8 - -187.80 but if its like this -187.80 - -187.80 it fails
0
 
Robert SaylorSenior DeveloperCommented:
use substr to test if the last number is a 0 then remove it and try the math again.
0
 
Chris StanyonCommented:
Something else must be going on. I've just ran some code with all your examples in the database and it all works fine. This is the PHP I used - it's using PDO rather than mySQL but all the math works fine:

<?php 
$statement = $dbh->query("select num1, num2 from math");

$statement->bindColumn('num1', $number1);
$statement->bindColumn('num2', $number2);

while ($statement->fetch(PDO::FETCH_BOUND)) {
	printf("<p>Number1: %s | Number2: %s | Total: %s</p>", $number1, $number2, $number1-$number2);
}
?>

Open in new window

Here's the table structure:

CREATE TABLE `math` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num1` decimal(15,2) DEFAULT NULL,
  `num2` decimal(15,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

Open in new window

And here's the table data:

id	num1	num2
1	-64.05	-64.05
2	100.00	25.00
3	-187.80	-187.80
4	-2191.25	-2191.25
5	224.27	224.27

Open in new window

0
 
the-mizAuthor Commented:
this is actually a while loop going through to get a list of numbers. here is the results:


1905.83 - 2001.52 = -95.69
10759.89 - 10759.89 = 0
300 - 300 = 0
255.24 - 285.78 = -30.54
-1455.52 - -1455.52 = 0
1159.29 - 1159.29 = 0
2200 - 2200 = 0
-233 - -233 = 0
1165.22 - 1165.22 = 0
1462.99 - 1462.99 = 2.2737367544323E-13
-64.05 - -64.05 = -1.8474111129763E-13
-845.26 - -845.26 = -2.2737367544323E-13
-25583.23 - -24157.15 = -1426.08
602.35 - 602.35 = 0

As you can see some work some don't.
0
 
Chris StanyonCommented:
It's difficult to know where the problem is - I've tested and it all works fine.

The one thing does stand out to me is that earlier you said your data type in mySQL was decimal 15,2, but some of the numbers you've echoed out don't seem to reflect that. If your datatype was 15,2 and you enter a value of 2200, it would actually get stored as 2200.00 and would therefore be retrieved back as 2200.00 - your example above shows some figures not having any decimal places, which strikes me as being a little odd!

Dump your CREATE TABLE statement and let us see that, and update your code to use PDO.

You might also want to show us exactly what code you are using - the code you posted earlier clearly isn't the code used to loop through your data.
0
 
the-mizAuthor Commented:
CREATE TABLE IF NOT EXISTS `jobcost` (
  `id` int(100) NOT NULL AUTO_INCREMENT,
  `job` varchar(10) NOT NULL,
  `datetime` datetime NOT NULL,
  `costcode` varchar(11) NOT NULL,
  `category` varchar(100) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `variance` decimal(15,2) DEFAULT NULL,
  `entered_by` varchar(255) DEFAULT NULL,
  `estimate` decimal(15,2) DEFAULT NULL,
  `jobcost` decimal(15,2) DEFAULT NULL,
  `commitment` decimal(15,2) DEFAULT NULL,
  `invoiced` decimal(15,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Open in new window


I did call of a float command and tried dropping the trailing zeros to see what that would do.  I just find it strange why it would calculate it correctly one time and directly after, it would not.

My select command from the database is actually grouping and using SUM to get the values.  These values are then used to subtract with.
0
 
Dave BaldwinFixer of ProblemsCommented:
As I said above, PHP considers it a floating point number if there is a decimal point.  If there is not a decimal point, it is an integer and your arithmetic is much more predictable.  You really should read the page on floating point in PHP.  Direct comparisons between floating point numbers are unreliable and not recommended because standard floating point is limited in precision and does not represent some numbers well because they don't fit on the binary boundaries in floating point.

http://php.net/manual/en/language.types.float.php
0
 
Chris StanyonCommented:
We're missing something. The code and examples you've posted are clearly not the same as you're using. Without seeing your code in full we might as well just keep on guessing.
0
 
Robert SaylorSenior DeveloperCommented:
Try dumping the numbers without using MySQL sum
0
 
Ray PaseurCommented:
+1 for the comment here which is what I expect may be going on.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28168567.html#a39279015

Are you doing the math in the MySQL query or in PHP after retrieving the data from MySQL?

Are you using a 32-bit or 64-bit machine?
0
 
Ray PaseurCommented:
NTF, DNF *

http://www.laprbass.com/RAY_temp_the_miz.php

<?php // RAY_temp_the_miz.php
error_reporting(E_ALL);

Class Subtractor
{
    public function __construct($x, $y)
    {
        $this->menuend    = $x;
        $this->subtrahend = $y;
        $this->difference = $x - $y;
        $this->formatted  = number_format($this->difference,2);
    }
}

// TEST DATA
$arr = array
( new Subtractor(1905.83, 2001.52)
, new Subtractor(10759.89, 10759.89)
, new Subtractor(300, 300)
, new Subtractor(255.24, 285.78)
, new Subtractor(-1455.52, -1455.52)
, new Subtractor(1159.29, 1159.29)
, new Subtractor(2200, 2200)
, new Subtractor(-233, -233)
, new Subtractor(1165.22, 1165.22)
, new Subtractor(1462.99, 1462.99)
, new Subtractor(-64.05, -64.05)
, new Subtractor(-845.26, -845.26)
, new Subtractor(-25583.23, -24157.15)
, new Subtractor(602.35, 602.35)
)
;

print_r($arr);

Open in new window

* No Trouble Found, Did Not Fail
0
 
IanStatisticianCommented:
Hi there the-miz,

NOTE in discussion below integers are without a decimal point and floating point numbers always have a decimal point.

Depending on software versions etc, mySQL is likely to return the DECIMAL(15,2) values as characters and it is up to PHP in executing the code below (or just prior to executing it)

$number1 = $row[0];
$number2 = $row[1];

 - or your equivalent - to convert the text strings into floating point. There are a few ways to go in the conversion routines, but given the text string "-234.56" it might do something like -

(10.0^-2) *( (-1) * (6 + 10 * (5 + 10 * (4 + 10 * (3 + 10 * (2))))))
  [[ where all but the (10^-2) is in integer arithmetic ]]

Note that the 10.0^-2 is not an exact number in the computer, it is 0.01 in base 10 but is a repeating fraction in base 2 (binary).  For the final multiplication to occur the integer  (-1) * (23456) needs to be converted into floating point. Such a conversion can be done exactly, but that is not to say it will be!

After the multiplication of 0.01 by  (-23456.0) you are going to get an inexact floating point number.

An alternative method would be

 (10.0^-2) * (6 + 10 * (5 + 10 * (4 + 10 * (3 + 10 * (-2)))))

or

(10.0^-2) * ((-1.0) * (6.0 + 10.0 * (5.0 + 10.0 * (4.0 + 10.0 * (3.0 + 10.0 * (2.0))))))

All these methods require an iterative loop, something like

(set sum <- 0,  then repeat (multiply by 10 and add next digit) until you run out of digits - skipping over the decimal point)
next multiply by 10.0 ^ (number of digits to the right of decimal point)

However there is some fancy footwork required for negative numbers because  -32768 is a valid negative number but 32768 is an overflow for a 2 byte positive number.  Same argument for -2147483648 as a four byte integer.

All it requires is a stray bit not correctly cleared (or set) in the looping mechanism for the lowest order bit in the floating point number to depend on previous values in the register, or some other like case where testing may not pick up the very small error.  Especially when we all know that the -234.56 cannot be exactly represented in binary.

Another possibility is that the first value in a returned mySQL row is converted by one mechanism and all subsequent values are done in a loop with slightly different coding.

The value
2.2737367544323E-13
is approximately (to good printing precision) equal to -1 * 2^-42, that is a 1 bit 42 binary places to the right of binary point (not decimal places and decimal point) in a negative number.  Note here that floating point numbers are almost always packed into an eight byte structure with part of it for +/- sign, part for an exponent and the rest for a fractional part.

If you have two variables and subtract them then the system should just subtract, but if you are subtracting two constants in code (as in your above demonstrations) then the system may multiply the second one by -1 and add the results, or even do the arithmetic at the time of syntax analysis so all the program gets to do is to use the calculated number!

There are many tricks and traps for the PHP system coder!

There are some languages that perform fuzzy comparisons to get around this problem of inexact representations and the associated problem of inexact translation. Two numbers compare if their absolute difference is less than the absolute value(largest) * 5.0E-14   - or some  similar rule.

Seems funny though that the exact same character sequence are not represented the same and correctly subtracted to get zero. But above gives some reasons for the inexact representations.
 

Food for thought.

Ian
0
 
the-mizAuthor Commented:
I used number_format and all the math seems to work fine now.
0
 
Robert SaylorSenior DeveloperCommented:
I think that will still give you a false reading if the numbers do not equal 0. Have you tried the calculations without using MySQL sum? Number format in this case it sweeping the dirt under the rug.
0
 
Ray PaseurCommented:
I think you can trust number_format() if the allowable values are down to a penny.

Try this on for size (pidgin code):

$x = 1462.99 + 2.2737367544323E-13;
var_dump($x);
echo number_format($x,2);
echo number_format($x,8);
0
 
IanStatisticianCommented:
Hi there the-miz,

Using number_format just changes presentation, the calculated value may still have the stray bit in it.

In the database tables (constructed and used by mySQL) data can be in approximate floating point format or exact format. int and decimal are examples of exact format. float is the floating point format.

Within mySQL you have DECIMAL(15, 2) being an EXACT representation.  Refer to the mySQL manual page and following 4 pages.  When the data is manipulated within mySQL the arithmetic (as in using  SELECT SUM(...)   ) will be exact provided the columns in the sum are exact.

Calculations on exact format columns will in general (except for example division) use exact methods. If there are any float columns or floating point functions it will produce a floating result.

However when the data gets out into PHP the returned numeric strings with decimals that mySQL provides will be converted (and used) as a floating point number.  It is the approximate arithmetic -performed by PHP - that can lead to these type of precision errors.


 What this means is that if you produce a select clause (executed by mySQL) that does the subtraction in the select using exact values (DECIMAL(15, 2) is exact) then you will have an exact result.  If, however, you use the select clause to return just the column values and PHP does the calculation then it will be performed using floating point arithmetic and can produce these precision errors.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.