How to format numerical data extracted from the database for monetary values?

Question

I’m using the library jquery.maskMoney.js to format the system’s monetary data. But when data is saved in the database and returned to the system screen formatting is lost. How to resolve this problem? I would like the formatting to remain, follow some system screens that exemplify this problem.

1. Data with monetary formatting: inserting the data into the forms

2. Data without monetary formatting: data being loaded from the database
3. Excerpt from the page code edita.blade.php

<div class="col-md-5 form-group">
<label class="control-label">Valor Solicitado (R$)</label>

<input type="text" class="form-control"  id="vlSolicitado" name="vlSolicitado" value="{{str_replace('.',',',$projeto->valor_solicitado)}}" data-thousands="." data-decimal="," data-prefix="R$ " maxlength="18"  required>  
</div>

4. Result of Database Consultation

Answer 1

I’ll direct you to process the data before entering it into the database.

1. I suppose you’re using PHP to process the information before inserting it into the database (Mysql).

2. I also assume that your database is properly structured to receive information from your forms.

For monetary values the field must be of the type DECIMAL (7,2), two digits after the point and believe that 7 digits before the point is sufficient.

As you are formatting the value with a Jquery mask, as soon as you pass the value to PHP, they also come formatted. Example: 2,000.00.

You need to delete this formatting because your database will not accept the characters: "." and "," thus generating an error.

If form values are being sent via POST, do so:

// Valor recebido do formulário, campo valor solicitado (2.000,00)
$valor = $_POST['valorSolicitado'];

// Removo da variável o "."
$valor = str_replace('.' '', $valor);
// Substituo a "," pelo "." pois é esse formato que o campo do seu banco de dados vai aceitar
$valor = str_replace(',' '.', $valor);

// Esta variável irá imprimir:
// 2000.00

When you recover the value of your data flock, format it using a PHP function called number_format:

// Valor recuperado do banco de dados (2000.00)
$valor = $valor_do_banco_de_dados;

$valor = number_format($valor, 2, ',', '.');

// Esta variável irá imprimir:
// 2.000,00

Answer 2

Hello @Ruama, try to make a php explode at the value obtained by the bd select, before displaying it in the form. For example:

$sql = "SELECT * FROM TABELA WHERE id=$id_escolhido";
$rs = mysqli_query($con, $sql);

while ($row= mysqli_fetch_array($rs)){
   $valor = $row['valor'];
   $valor_explode = explode(",", $valor);
}