PHP MYSQL saving float in database 0.01?

Question

i am trying to save number of cents in MySql table as float , but the database is taking it wrong , for example i am saving 0.01, its saved as 0.1 !

is it possible to make this happen ?

My Code ;

$return["reward"] = 0.05;

$api->user['balance']  += $return["reward"];

$q = $api->pdo->prepare("UPDATE " . DB_PREFIX . "user
                         SET 
                         balance = :balance
                         WHERE 
                         userid = :userid");

$q->bindParam(":balance" , $api->user['balance']  , PDO::PARAM_INT );
$q->bindParam(":userid" , $api->user['userid'] , PDO::PARAM_INT);

the balance column TYPE float in database.

Using `PDO::PARAM_INT` will save it using an integer, so not sure how this then comes out as 0.1. I would have expected either 0 or 1. — Nigel Ren, Dec 28 '18 at 14:50
As @Curious_Mind said, you should use `PDO::PARAM_STR`, check this post: https://stackoverflow.com/questions/2718628/pdoparam-for-type-decimal Maybe your database config is wrong? — dimasdmm, Dec 28 '18 at 14:50
(not a solution but) I wouldn't do it the way you are trying, I would add the value using SQL - `balance = balance + :reward`. — Nigel Ren, Dec 28 '18 at 14:57
If I am right, the default "number of decimals" in a float column is one. So your number is rounded up to 1 decimal place: `0.05 => 0.1` — dimasdmm, Dec 28 '18 at 14:57
@dimasdmm how i could achieve this ? by saving the cents like 0.05 — Kodr.F, Dec 28 '18 at 15:00
Totally agreed with @NigelRen, with your existing code it would have 0 or 1. — A l w a y s S u n n y, Dec 28 '18 at 15:01
You should change the precision of your column (I don't know the syntax exactly). I think that the MySQL doc may help you: https://dev.mysql.com/doc/refman/8.0/en/problems-with-float.html — dimasdmm, Dec 28 '18 at 15:07
Check my answer below and tell me if it works for you or not. — rpm192, Dec 28 '18 at 15:16

A l w a y s S u n n y · Answer 1 · 2018-12-28T15:05:32.910

1

Try with PDO::PARAM_STR instead of PDO::PARAM_INT.

$q->bindParam(":balance" , $return["reward"]  , PDO::PARAM_STR);

From Documentation

MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, (M,D) means than values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001

Also see : https://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html

edited Dec 28 '18 at 15:05

answered Dec 28 '18 at 14:44

A l w a y s S u n n y

36,497
8
60
103

Then it is not related with PDO, it's your db column issue – A l w a y s S u n n y Dec 28 '18 at 14:54
the db column type set as Float , i've tried double also same XD – Kodr.F Dec 28 '18 at 14:55
Yep, I think the same as Curious_Mind. Something is wrong in the database ;) – dimasdmm Dec 28 '18 at 14:58

score 1 · Answer 2 · answered Dec 28 '18 at 15:00

1

Try to change column type to decimal and set Length to 7.2 . Which will be seven numbers before the dot and 2 after it

answered Dec 28 '18 at 15:00

A.Nikolov

23
5

score 1 · Accepted Answer · answered Dec 28 '18 at 15:12

1

This fault is not related to your code.

You're using the data type float which shouldn't be used to store monetary values.

Change your data type to decimal which is considered the best practise for storing monetary values.

You can change it through the webinterface of phpMyAdmin or with a query like so:

ALTER TABLE tablename MODIFY columnname DECIMAL(5,2);

This will allow you to store 5 digits before the comma and 2 after, change it to your needs.

answered Dec 28 '18 at 15:12

rpm192

2,630
3
20
38

Your description of the digits before the comma is incorrect. DECIMAL(5,2) has a total number of digits of 5, with 2 for after the decimal point. https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html – profm2 Nov 10 '20 at 15:59

PHP MYSQL saving float in database 0.01?

3 Answers3