Currency formatting to save to database as DECIMAL

Question

I am using a mysql database, the format of the field is Decimal (7,2) and he gets his salary. Based on what the user types I store in the bank so:

str_replace(',','.', $_POST['txtSalario'])

Replace the comma by the dot. The problem is that if it type ex: 3.145,89, the database refuses because it understands that the first point is already decimal. How to treat this data entry to receive typed values such as 3145,89 or 3,145,89?
I’m using the PHP 5.3 and mysql 5.5

Answer 1

In the input field of the edit screen it is necessary to replace the semicolon, so that when saving the function below does not give error.

On the edit screen :

$valor = $valor = str_replace('.', ',',$NumValorDeconto);

And to save I used a function to clean.

public static function moeda($get_valor) {

        $source = array('.', ',');
        $replace = array('', '.');
        $valor = str_replace($source, $replace, $get_valor); //remove os pontos e substitui a virgula pelo ponto
        return $valor; //retorna o valor formatado para gravar no banco
    }

Answer 2

I haven’t tested the code, but you’ve tried removing the point before?

str_replace(',','.', str_replace('.','', $_POST['txtSalario']))

Answer 3

Friend probably you already solved of course né...rs more know that someone will fall on this page doing the same research are follows a simple and functional code!

  function Valor($valor) {
       $verificaPonto = ".";
       if(strpos("[".$valor."]", "$verificaPonto")):
           $valor = str_replace('.','', $valor);
           $valor = str_replace(',','.', $valor);
           else:
             $valor = str_replace(',','.', $valor);   
       endif;

       return $valor;
}

Answer 4

I had this problem and solved using a different approach in the input form.

Instead of having a single input for a float, divide it into two inputs: One for the integer value and one for the decimal value. Make the decimal value receive as default 00. In this case, you guarantee that the value will always be divided between the whole value and the decimal places, and it is easier to manipulate later on the server-side.

Jsfiddle

Answer 5

As money needs an exact representation do not use data types that are only approximated as float. You can use a fixed-point numeric data type, such as

numeric(15,2)

15 is the accuracy (total value length including decimal places) 2 is the number of digits after the decimal point See Types numeric in Mysql :

These types are used when it is important to preserve accuracy such as monetary data.

to validate use something like:

preg_match('!^\d{1,3}(\.\d{3})*(\,\d{2})?$!', $number) || preg_match("!^\d+,\d\d$", $number);