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

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?

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?

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

Try this:

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

$num1 = -64.05;

$num2 = 64.05;

echo $num1 + $num2;

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.00000000000018474111129

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

http://php.net/manual/en/language.types.float.php

```
$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;
```

The database stores the numbers. If they are both exactly the same and negative it comes out with that strange output.

Its weird!

```
<?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
?>
```

```
<?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);
}
?>
```

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
```

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
```

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.

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.

```
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 ;
```

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.

http://php.net/manual/en/language.types.float.php

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?

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);
```

* No Trouble Found, Did Not Fail
Depending on software versions etc, mySQL is likely to return the

$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

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

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);

Using

In the database tables (constructed and used by mySQL) data can be in approximate floating point format or exact format.

Within mySQL you have

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

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.

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

multiplycould bedivide.-------

In your question text you said

What you get is not random at all. It is going to be equal (or very close to) 2 ^ (-k) where k

depends onthe size (in bits) allocated to the fraction part in the floating point representation in the hardware you are usingsubtracted fromthe 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

alwaysbe careful about subtracting or comparing floating point numbers. You can get round the comparison problem by using a language that provides you with automaticfuzz.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