来自PHP，SQL查询的好奇结果

Question

I'm working on an experiment that works with database and I got this odd result.

System goes like this: I have a button that adds 1 to the counter every time you click it. Once it reach certain number, it will calculate score deduction and update the score

PHP:

$counter = $_POST['counter'];
$user = $_POST['user'];

$sql = "SELECT score FROM board WHERE player = '$user'";
$result = mysqli_query($con,$sql);
$row = mysqli_fetch_array($result);

if($counter == 1)
  $divisor = 0;
else if($counter == 5)
  $divisor = 0.1;
else if($counter == 20)
  $divisor = 0.3;
else if($counter == 50)
  $divisor = 1;}

$deduction = $row['score'] * $divisor;

$sql = "UPDATE board SET score = score - ".$deduction." WHERE player = '$user'";

I started with score = 10 and these are the results

Score: 10
Counter: 1

Divisor = 0; Deduction = 10 * 0;
UPDATE ... score = score - Deduction (0)
New score = 10
-------------------------------------

Score: 10
Counter: 5

Divisor = 0.1; Deduction = 10 * 0.1;
UPDATE ... score = score - Deduction (1)
New score = 9
-------------------------------------

Score: 9
Counter: 20

Divisor = 0.3; Deduction = 9 * 0.3;
UPDATE ... score = score - Deduction (2.7)
New score = 6.3
-------------------------------------

## Here is the curious part

Score: 6.3
Counter: 50

Divisor = 1; Deduction = 6.3 * 1;
UPDATE ... score = score - Deduction (6.3)
New score = 0.000000190735

Any idea as to why I'm getting the 0.000000190735? I'm kinda really confused right now.

B'rgrds,

Answer 1

This is a bit long for a comment.

This is, no doubt, caused by different representations for score and $divisor or by the conversion of $divisor to a string for the query. It is hard to specify exactly where the problem is, because there are several different places.

If you really need to handle this, do the calculations in the database:

UPDATE board
    SET score = score * (1 - ?)
    WHERE player = ?;

Pass the placeholders in as parameters -- rather than munging the query string.