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
– Nigel Ren, Commented Dec 28, 2018 at 14:50
As @Curious_Mind said, you should use PDO::PARAM_STR, check this post: stackoverflow.com/questions/2718628/pdoparam-for-type-decimal Maybe your database config is wrong? — dimasdmm
– dimasdmm, Commented Dec 28, 2018 at 14:50

rpm192 · Accepted Answer · 2018-12-28 15:12:54Z

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, 2018 at 15:12

rpm192

2,4593 gold badges22 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

profm2 Over a year ago

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. dev.mysql.com/doc/refman/5.7/en/…

A.Nikolov · Accepted Answer · 2018-12-28 15:00:46Z

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, 2018 at 15:00

A.Nikolov

235 bronze badges

Comments

A l w a y s S u n n y · Accepted Answer · 2018-12-28 15:05:32Z

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, 2018 at 15:05

answered Dec 28, 2018 at 14:44

A l w a y s S u n n y

38.8k9 gold badges68 silver badges120 bronze badges

3 Comments

A l w a y s S u n n y Over a year ago

Then it is not related with PDO, it's your db column issue

Kodr.F Over a year ago

the db column type set as Float , i've tried double also same XD

dimasdmm Over a year ago

Yep, I think the same as Curious_Mind. Something is wrong in the database ;)

Collectives™ on Stack Overflow

PHP MYSQL saving float in database 0.01?

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related