Trouble adding numbers with 12 decimals in PHP and MySQL

[wsmeyer]

In my database I have numbers stored as decimal(16,12) like this:

1.000100000000
3.000100010001
10.000500020003

If I just grab them and echo them to the screen they return correctly but if I try to get the sum of them either in MySQL using

sum(nums) AS num

or grabbing them and then with PHP looping through them and using

$tot +=

they do not add correctly and it is DRIVING ME CRAZY :)

If anyone has any ideas I'd really appreciate a point in the right direction.

William.

[speed]

I'm using MySQL 4.0.26, I've created a table and entered your test data from above. Running a SELECT directly on the database to sum the values returns the correct number.

Can you try a different install of MySQL say on a different server? If that works then I'd look at the current installations configuration.

[wsmeyer]

Thanks speed, after paying around with it some more I still am having the problem it just isn't exactly where I thought it was.

Some background may help. This is for an eccommerce site that I run that sells coffee mugs. We have three sizes of mugs 14oz, 20oz, and travel mugs. We sell the mugs both individually and in sets that are created on the fly. I have now created a "quantString" for each product and set like:

aaaa.bbbbccccdddd

where...
aaaa = total mugs
bbbb = total 14oz mugs
cccc = total 20oz mugs
dddd = total travel mugs

All is working correctly up to and including the point where it adds up the quantString's for each order. If I query the database for the quantString for a single order it returns correctly. It is where I try and get stats for a number of orders or a given time frame that problems arise.

Here is the code for three different ways I have tried to query the database:

$monthStrt = date("m")-1;
$monthEnd = date("m")-1;
$year = date("Y");
$Month = date("F", mktime(0, 0, 0, $monthStrt, 1, $year));
$numDays = cal_days_in_month( CAL_GREGORIAN, $monthEnd, $year);
$sd = mktime(0, 0, 0, $monthStrt, 01, $year);
$ed = mktime(0, 0, 0, $monthEnd, $numDays, $year);

$query = mysql_query("SELECT ordQuantString FROM orders WHERE ordDate BETWEEN '" . date("Y-m-d", $sd) . "' AND '" . date("Y-m-d", $ed) . " 23:59:59'");
$tot =0;
while($rs = mysql_fetch_assoc($query)){
$string = $rs['ordQuantString'];
$tot += $string;

preg_match('/(\d+)+(\.)+(\d\d\d\d)+(\d\d\d\d)+(\d\d\d\d)/', $string, $match_array2);
$numTot += (int)$match_array2[1];
$num14 += (int)$match_array2[3];
$num20 += (int)$match_array2[4];
$numTM += (int)$match_array2[5];
}

print "quantString: " . $tot . "
";
print "total: " . $numTot . "
";
print "14oz: " . $num14 . "
";
print "20oz: " . $num20 . "
";
print "travel: " . $numTM . "
";

print "
";
print "-----------------------------------";
print "
";

$query = mysql_query("SELECT ordQuantString FROM orders LIMIT 100");
$tot =0;
while($rs = mysql_fetch_assoc($query)){
$string = $rs['ordQuantString'];
$tot += $string;

preg_match('/(\d+)+(\.)+(\d\d\d\d)+(\d\d\d\d)+(\d\d\d\d)/', $string, $match_array2);
$numTot_2 += (int)$match_array2[1];
$num14_2 += (int)$match_array2[3];
$num20_2 += (int)$match_array2[4];
$numTM_2 += (int)$match_array2[5];
}

print "quantString: " . $tot . "
";
print "total: " . $numTot_2 . "
";
print "14oz: " . $num14_2 . "
";
print "20oz: " . $num20_2 . "
";
print "travel: " . $numTM_2 . "
";

print "
";
print "-----------------------------------";
print "
";

$query = mysql_query("SELECT ordQuantString FROM orders LIMIT 10");
$tot =0;
while($rs = mysql_fetch_assoc($query)){
$string = $rs['ordQuantString'];
$tot += $string;

preg_match('/(\d+)+(\.)+(\d\d\d\d)+(\d\d\d\d)+(\d\d\d\d)/', $string, $match_array2);
$numTot_3 += (int)$match_array2[1];
$num14_3 += (int)$match_array2[3];
$num20_3 += (int)$match_array2[4];
$numTM_3 += (int)$match_array2[5];
}

print "quantString: " . $tot . "
";
print "total: " . $numTot_3 . "
";
print "14oz: " . $num14_3 . "
";
print "20oz: " . $num20_3 . "
";
print "travel: " . $numTM_3 . "
";
?>

Combined outputs...

quantString: 600.04920085006
total: 600
14oz: 492
20oz: 85
travel: 59

-----------------------------------
quantString: 385.03010050003
total: 385
14oz: 301
20oz: 50
travel: 34

-----------------------------------
quantString: 29.002000040005
total: 29
14oz: 20
20oz: 4
travel: 5

As you can see the first one doesn't contain enough decimal places.

Second one doesn't have the right digits either, but the reulting total are correct.

third one is correct, but only for 10 entries.

William.

[speed]

I would advise you to break the information into 4 separate columns rather that trying to do it in one.

Manipulation of floating point numbers can generate rounding errors which when abusing them like this can cause strange errors.