Geek asks and answers

How to store European currency in MySQL?

In the new project I'm working on, I have CSV data to import into mysql table. One of the columns is the price field, which stores the currency in the European format, i.e. 345.83. I have saving this decimal separator. In most European currencies, the decimal separator is ",", but when I try to insert a decimal number in a field (for example, 345.83), I get the following error: "Data is truncated for column" column_name "in row" row # "". If I use '.' instead of "," it works fine. Could you help me how to save this format in mysql?

0
source share
7 answers

you can save it as a regular decimal field in the database and format the European style number when displaying it

edit: just added an example of how this can be achieved

$european_numbers = array('123.345,78', '123 456,78', ',78');

foreach($european_numbers as $number) {

    echo "$number was converted to ".convert_european_to_decimal($number)."\n";
    // save in database now
}

function convert_european_to_decimal($number) {
    // i am sure there are better was of doing this, but this is nice and simple example
    $number = str_replace('.', '', $number); // remove fullstop
    $number = str_replace(' ', '', $number); // remove spaces
    $number = str_replace(',', '.', $number); // change comma to fullstop

    return $number;
}
+13
source

Use number_format or money_format , this is pretty much what you prefer.

+4
source

, . 1234.56 :

  • 1234,56
  • 1 234,56 ( )
  • 1.234,56 ( )

.net , , , . , PHP, .

+3

VARCHAR, DECIMAL, , . , MySQL- .

UPDATE <<table>>
    SET <<decimal-currency-col>> = REPLACE(<<varchar-currency-col>>, ',', '.');
+1

SQL Server MySQL. CURRENCY: MySQL () CURRENCY, DECIMAL (19,4) . MSSQL Unicode , nCHAR nVARCHAR, MySQL , , , Unicode.

from http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

0
source

You can also consider multiplying by 100 and saving it as an INT.

Before inserting a price into the database:

$price = (int)$price*100;

After receiving a price from the database:

$price = number_format($price, 2, ',', ' ');
0
source 
Try replacing the "," with "."?

$price = str_replace(",", ".", $price);
-1
source

More articles:


All Articles