PHP/SQL Maths with crazy results

Question

Here is the code;


    //calculate parts total
    $sqlparts = "SELECT jobNumber, SUM(unitPrice*quantity) AS c FROM s_partOrders WHERE jobNumber = $jobNumber";
    $rowparts = mysqli_query($con, $sqlparts) or die('Action failed: ' . mysqli_error($con));
    $rsparts = mysqli_fetch_assoc($rowparts);
    $partsTotal = $rsparts[c];
    if(empty($rsparts[c]))
    {
        $partsTotal = 0;
    }

    // calculate payments total
    $serviceTotal = $row[costLabour]+$row[costCallout]+$row[costDelivery]+$partsTotal;
    $sqlpayments = "SELECT jobNumber, amount, SUM(amount) AS sumAmount FROM s_payments WHERE jobNumber = $jobNumber";
    $rowpayments = mysqli_query($con, $sqlpayments) or die('Action failed: ' . mysqli_error($con));
    $rspayments = mysqli_fetch_assoc($rowpayments);
    $paymentsTotal = $rspayments[sumAmount];
    if(empty($rspayments[sumAmount]))
    {
        $paymentsTotal = 0;
    }
    $amountDue = $serviceTotal - $paymentsTotal;
    $gst = round($serviceTotal/11, 2);
    $exGST = $serviceTotal-$gst;

    echo "($partsTotal + $row[costLabour] + $row[costCallout] + $row[costDelivery]) - $paymentsTotal = $amountDue";

This is the output result!

(1850 + 1321.14 + 0 + 0) - 3171.14 = 4.54747350886E-13

It only happens on some records though. Others calculate perfectly. Kinda strange! All values stored in the database as double.

**WARNING**: When using `mysqli` you should be using parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation to accomplish this because you will create severe [SQL injection bugs](http://bobby-tables.com/). — tadman, Dec 19 '14 at 04:39
tadman, I have used `mysqli_real_escape_string()` on all user entered data. Is that not sufficient? — MrBig, Dec 19 '14 at 05:10
Calling it manually is extremely error prone. It's much better to use prepared statements with placeholders. That way it takes several mistakes to cause a serious problem. — tadman, Dec 19 '14 at 16:30

score 1 · Accepted Answer · answered Dec 19 '14 at 03:29

1

Lol, sorry but it's you ,not the results, being crazy :)

4.54747350886E-13 is essentially 0 (ignoring precision down to 12th decimal value) and that's what (1850 + 1321.14 + 0 + 0) - 3171.14 calculates to. There is nothing wrong with that result.

4.54747350886E-13 = 0.0000000000005

answered Dec 19 '14 at 03:29

Hanky Panky

46,730
8
72
95

"4.54747350886E-13 is essentially 0" --- it's not. If I ask you to send me me essentially the left number of dollars `10^20` times - you won't do that. – zerkms Dec 19 '14 at 03:32
Updated already to note for that :) – Hanky Panky Dec 19 '14 at 03:32
Yeah I feel crazy right now haha. So how come some add up to 0 and some to crazy numbers? A second example; 84 + 468.42 + 0 + 0 = 1.13686837722E-13 Basically, the question is "Why does it do that?" – MrBig Dec 19 '14 at 03:33
2

@MrBig: just don't store prices as floats/doubles, but decimals. – zerkms Dec 19 '14 at 03:34
it should be noted that `sprintf("%.20f",$value);` will print the number as a decimal like string, where 20 is the desired places after the decimal point the user would like to maintain. – r3wt Dec 19 '14 at 03:35
@MrBig its scientific notation. – r3wt Dec 19 '14 at 03:37

PHP/SQL Maths with crazy results

1 Answers1