Format real (br) value for decimal (10,2) of mysql

2

Good afternoon, guys! How to format Real (br) value for numeric (10,2) of mysql using php number_format?

I tried to use:

$num="89,90"; ou $num="1.089,90";
number_format($num, 2, '.', '');

But it's zeroing the cents.

    
asked by anonymous 13.12.2018 / 20:39

3 answers

4

The number_format of php requires that the first parameter ( $_POST['valor'] in your case) is a float.

  

string number_format (float $ number [ int $ decimals])

So if your entry is 20,05 this will not be considered float and will be converted in the wrong way. With the following error:

Notice: A well-formed numeric value encountered

You can do this:

number_format(str_replace(",",".",str_replace(".","","1.089,90")), 2, '.', '');

In this way you guarantee that it will be delivered in the correct format.

It is interesting to note that the number_format function casts this value to float. And if you do the same thing:

var_dump((float)"20,05");

You will see that the result will be:

float(20)

And not 20.05 as expected.

    
13.12.2018 / 20:55
1
Since MySQL / MariaDB expects a value of type float for columns type decimal() , you must format its number before inserting into db.

Respecting this, I did this (structured) function:

function brl2decimal($brl, $casasDecimais = 2) {
    // Se já estiver no formato USD, retorna como float e formatado
    if(preg_match('/^\d+\.{1}\d+$/', $brl))
        return (float) number_format($brl, $casasDecimais, '.', '');
    // Tira tudo que não for número, ponto ou vírgula
    $brl = preg_replace('/[^\d\.\,]+/', '', $brl);
    // Tira o ponto
    $decimal = str_replace('.', '', $brl);
    // Troca a vírgula por ponto
    $decimal = str_replace(',', '.', $decimal);
    return (float) number_format($decimal, $casasDecimais, '.', '');
}

var_dump(brl2decimal('150.99', 2)); // float(150.99)
var_dump(brl2decimal('10.123456789', 3)); // float(10.123)
var_dump(brl2decimal('R$ 10,99', 2)); // float(10.99)
var_dump(brl2decimal('89,999', 3)); // float(89.999)
var_dump(brl2decimal('1.089,90')); // float(1089.9)
var_dump(brl2decimal('1.089,99')); // float(1089.99)

And I also made a class (oo): GitHub / lipespry - Currency Class

Inserting into database with column type decimal(10, 2) :

mysql> INSERT INTO 'decimal' VALUES (10.99), (89.999), (1089.9), (1089.99);
Query OK, 4 rows affected, 1 warning (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM 'decimal';
+---------+
| valor   |
+---------+
|   10.99 |
|   90.00 |
| 1089.90 |
| 1089.99 |
+---------+
4 rows in set (0.00 sec)

@edit: Enhanced function: If already in USD format, returns as float and formatted @ edit2: Added link to class in GitHub .

    
13.12.2018 / 22:36
0

It's quite simple, let's declare a sample variable.

$num = 100000.50;

The standard in the American format USD.

number_format($num, 2); // $100,000.50

Our standard BRL

number_format($num, 2, ',', '.'); // R$100.000,50
    
13.12.2018 / 20:54