mardi 5 mai 2015

How to change data type when running query (not inside PHP)

Explanation

MySQL handles strings as Float and not Fixed for example if we have following column in database (amount type is DECIMAL(43,20))

+--------------------------+
|         amount           |
+--------------------------+
|  0.20000000000000000000  |
+--------------------------+

running the following PHP code

$amount_to_add = "0.1";
$stmt = $dbh->prepare("UPDATE table SET amount = amount + :amount");
$stmt->bindValue("amount", $amount_to_add);
$stmt->execute();

would result this:

+--------------------------+
|         amount           |
+--------------------------+
|  0.30000000000000004000  |
+--------------------------+

notice the additional 0.00000000000000004000

its because $amount_to_add was string

As i need to use BC Math function, its output is always string

And i can't turn it to double in PHP because it will lose precision and decimal points

Questions

How should i pass this to MySQL to treat it as fixed and not string (float) which doesn't need changing string type in PHP doesn't matter if we use mysql_query or pdo or mysqli or anything else

is something like declaring the amount type when running query possible? so it changes string to decimal inside mysql

Aucun commentaire:

Enregistrer un commentaire