PHP float/double stored as MySQL DECIMAL

Question

i ran into a really strange problem with storing values in MySQL. The premise:

I have a table that uses DECIMAL(15,8) to store monetary values (like the total of order), but when i try to insert for example:

2,45545345

this is stored as

2.00000000

I tried MySQL's FORMAT/CAST functions but still the same output.

This is how the query gets generated:

$db->query("INSERT INTO `random_table_name` SET currency_value = '" . floatval($value) . "'");

i also tried doubleval, but same result. The funny thing is though that this same piece of code was working fine a couple of weeks ago and i can't recall any changes to the db structure or the db class that can cause this.

What locale is it? floatval will convert "2,45545345" to 2.0 or 2.45545345 depending on locale. — Salman Arshad
– Salman Arshad, Commented Jan 21, 2013 at 8:38
It should work in multi-language environment, so that's why i'm looking for a bit more generic approach. The current issue though is with ro_RO Edit:: Setting the locale to en_US does the trick, but is there another (cleaner) way? — Anonymous
– Anonymous, Commented Jan 21, 2013 at 8:42

Vlad Preda · Accepted Answer · 2014-09-10 15:59:43Z

8

Use number_format to replace the , with .

Like this:

number_format($value, 8, '.') // 8 = number of decimals, . = decimal separator

However, your problem seems to be related to the current locale. You need to look into the following: setlocale() and localeconv

setlocale(LC_ALL, 'en_US'); // NOT TESTED, read up on the appropriate syntax

This is the appropriate way of doing this, the alternative would be (as suggested below), to do a str_replace(',', '.'), but you have to do the reverse every time you want to output strings.

There is another option though, you can set the MySQL locale to en_US.

edited Sep 10, 2014 at 15:59

answered Jan 21, 2013 at 8:25

Vlad Preda

9,9808 gold badges40 silver badges63 bronze badges

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

3 Comments

Anonymous Over a year ago

Not a solution as it produces the same result - 2.0000000000

Vlad Preda Over a year ago

Indeed. You need to set the appropriate locale first. US locale should do the trick, not sure about the syntax, so I added the links so you can look it up.

Anonymous Over a year ago

Thank you Vlad, setlocale as suggest by Salman is the culprit here!

Peon · Accepted Answer · 2013-01-21 08:35:48Z

3

Replace the , with . before inserting into db:

$value = str_replace( ',', '.', $value);

This will create a valid number, that can be safely inserted into the database. Or just add it just inside your query:

INSERT INTO `random_table_name` SET currency_value = '" . str_replace( ',', '.', $value ) . "'

edited Jan 21, 2013 at 8:35

answered Jan 21, 2013 at 8:29

Peon

8,0417 gold badges63 silver badges104 bronze badges

3 Comments

Anonymous Over a year ago

One of the first things i tried - doesn't work, the value gets rounded to 2 by the DB

Peon Over a year ago

str_replace can't round your number to 2

Anonymous Over a year ago

str_replace does not, but the DB stores the value as 2.000000 even when using MySQL's CAST/FORMAT functions

Collectives™ on Stack Overflow

PHP float/double stored as MySQL DECIMAL

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related