PHP float/double stored as MySQL DECIMAL
Solution 1
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
.
Solution 2
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 ) . "'
Anonymous
Updated on July 12, 2022Comments
-
Anonymous almost 2 years
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.