Format Real value (br) to decimal (10.2) mysql

Question

Good afternoon, you guys!
How to format Real value (br) to mysql Numeric(10.2) using php number_format?

I tried to use:

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

But this zero the pennies.

Answer 1

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

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

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

Notice: A non well Formed Numeric value encountered

You can do it:

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

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

It is interesting to notice that the function number_format makes a cast of this value for 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.

Answer 2

whereas the Mysql/Mariadb expect a value of the type float for columns type decimal(), you must format your number before inserting in db.

Respecting this, I did this function (structured):

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 the 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 on Github.

Answer 3

I like to do it this way;

This first method removes all special characters from your string.

/**
 * @param $str
 *
 * @return null|string|string[]
 */
public static function onlyNumbers($str)
{
    $str = preg_replace('/\D/', '', $str);
    return $str;
}

This second one, gets a strnig, and returns the decimal value for use in the database. I usually put this in a helper, or something like.

/**
 * @param $str
 * @return string
 */
public static function formatMoneyDb($str)
{
    $str = number_format((self::onlyNumbers($str) / 100), 2);
    $str = str_replace(',', '', $str);
    return $str;
}

Answer 4

It’s quite simple, let’s declare an example variable.

$num = 100000.50;

The standard in American USD format.

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

Our BRL standard

number_format($num, 2, ',', '.'); // R$100.000,50