How do I stop my database from rounding to 16 decimal places?

Question

I'm trying to make a webpage, where when the user clicks a payment button, a set amount it subtracted from an existing value in the database table. Just to confirm: My page does do this. But the unexpected behaviour of my code is it subtracts the amount, but it doesn't round it. It rounds it to 16 decimal places and I only want 2. See the image below (the bottom row) to see what I mean.

I have tried using the number_format() and round() functions in PHP but I'm not sure why they don't work. Am I using them incorrectly?

This is the full code below:

if(isset($_REQUEST['submit'])) {
  $SQL_query = "SELECT ROUND(SUM(Total), 2) AS sum FROM `order_details` WHERE `OrderID` = 95";
  $query_result = mysqli_query($connection, $SQL_query);

  while($row = mysqli_fetch_assoc($query_result)) {
  $total = $row['sum'];
  $number = floatval($total);
  }

  $SQL_Balance = "UPDATE Users SET Balance=(Balance-$number) WHERE UserID = 7";
  mysqli_query($connection, $SQL_Balance);


}

That's due to floating point precision. See https://www.php.net/manual/en/language.types.float.php. — Obsidian Age, Mar 31 '21 at 23:01
Beyond simple display issues like this, this is why you should never use floating point types to represent currency. — Sammitch, Mar 31 '21 at 23:02
@Sammitch Thanks. So what would you recommend I use instead? — Harry Wok, Mar 31 '21 at 23:09
An integer representing the base currency unit [eg: one cent] or a fixed-point DECIMAL type. — Sammitch, Mar 31 '21 at 23:48
Related: [Is floating point maths broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) — Tangentially Perpendicular, Apr 01 '21 at 00:49
**DO NOT USE FLOAT FOR CURRENCY** Use the dedicated decimal type — Dharman, Apr 01 '21 at 14:38

score 1 · Accepted Answer · answered Mar 31 '21 at 23:08

1

To get a two decimal number you can use ROUND with the following query:

UPDATE Users SET Balance=(ROUND(Balance-$number, 2)) WHERE UserID = 7

answered Mar 31 '21 at 23:08

code-a1

71
1
5

How do I stop my database from rounding to 16 decimal places?

1 Answers1