Solved

PHP Math

Posted on 2013-06-26
26
475 Views
Last Modified: 2013-07-15
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?
0
Comment
Question by:the-miz
  • 6
  • 5
  • 4
  • +4
26 Comments
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39278963
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
 
LVL 4

Expert Comment

by:ScorchD
ID: 39278984
If you just try something very basic like the following?

$num1 = -64.05;
$num2 = 64.05;
echo $num1 + $num2;
0
 
LVL 4

Expert Comment

by:ScorchD
ID: 39278989
Apologies was writing that at the same time as Chris
0
 
LVL 6

Expert Comment

by:Robert Saylor
ID: 39279009
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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39279015
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
 

Author Comment

by:the-miz
ID: 39279077
$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
 
LVL 4

Expert Comment

by:ScorchD
ID: 39279110
if you print_r on $row do you see what you would expect?
0
 

Author Comment

by:the-miz
ID: 39279117
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
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39279126
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
 

Author Comment

by:the-miz
ID: 39279147
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
 
LVL 6

Expert Comment

by:Robert Saylor
ID: 39279183
use substr to test if the last number is a 0 then remove it and try the math again.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39279184
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
 

Author Comment

by:the-miz
ID: 39279194
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39279250
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
 

Author Comment

by:the-miz
ID: 39279309
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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39279386
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
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39279467
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
 
LVL 6

Expert Comment

by:Robert Saylor
ID: 39279676
Try dumping the numbers without using MySQL sum
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39279882
+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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39279905
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
 
LVL 8

Expert Comment

by:ShannonEE
ID: 39280356
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
 

Author Comment

by:the-miz
ID: 39282172
I used number_format and all the math seems to work fine now.
0
 
LVL 6

Expert Comment

by:Robert Saylor
ID: 39282243
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39282510
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
 
LVL 8

Expert Comment

by:ShannonEE
ID: 39283303
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
 
LVL 8

Accepted Solution

by:
ShannonEE earned 500 total points
ID: 39291803
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now