PHP Math

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

Select allOpen in new window

If you just try something very basic like the following?

$num1 = -64.05;
$num2 = 64.05;
echo $num1 + $num2;

Apologies was writing that at the same time as Chris

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.

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

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

Select allOpen in new window

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

if you print_r on $row do you see what you would expect?

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!

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

Select allOpen in new window

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

use substr to test if the last number is a 0 then remove it and try the math again.

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

Select allOpen 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

Select allOpen 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

Select allOpen in new window

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


As you can see some work some don't.

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.

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 ;

Select allOpen 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.

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

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.

Try dumping the numbers without using MySQL sum

+1 for the comment here which is what I expect may be going on.
https://www.experts-exchange.com/questions/28168567/PHP-Math.html?anchorAnswerId=39279015#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?

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

Select allOpen in new window

* No Trouble Found, Did Not Fail

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

I used number_format and all the math seems to work fine now.

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.

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

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.