Problem when saving decimal values (monetary figures) in a mysql database

Question

Problem when saving decimal values (monetary figures) in a mysql database

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (1 votes)

1

At the moment I am saving several values that represent monetary figures, these values are extracted from a csv file and inserted into a database mysql . Some of these values are found as follows:

// pueden llegar con el punto de mil
// siete mil treinta y cuatro 
7.034 
// o sin punto de mil pero con valores decimales
// cuatro mil treinta y cuatro con treinta y cuatro décimas
4034.34

The problem occurs when when I make the sum of these, leaving the point in any of the cases the system tends to round the figures which makes the total is very imprecise, trying to solve this before doing the insertion I eliminate the points.

The field in which I save the data is ' DECIMAL (10,2) ' although I have tried it with NUMERIC or FLOAT but it is still the same.

//
7034 
// 
403434

In the case of the first value, adding it gives a more accurate result by not having decimals, which does not occur with the second value if it has decimals, since it interprets the last two digits as if it were part of this is to say " four hundred and three thousand, four hundred and thirty-four.

I hope to make myself understood

mysql php

asked by Alan 29.12.2016 в 15:29

source

3 answers

that returns an AND between integers? Redux: When to spend props and when to take props from the state?

score 2 · Answer 1

Since you use php, why not treat the data? In the data that comes from a CSV where the point . is used for units of thousand, use the function str_replace () , so that you have consistency in all the data and you can treat them systematically, otherwise it will be impossible, because mysql will never know if that point is a thousand or decimal unit.

First with the decimal (n, 0) feature in sql you return the numbers without decimals, then you will know that this point is for the units of a thousand.

// pueden llegar con el punto de mil
// siete mil treinta y cuatro 
7.034 
$csv1 = str_replace('.','','7.034');
(float) $csv1;
// o sin punto de mil pero con valores decimales
// cuatro mil treinta y cuatro con treinta y cuatro décimas
4034.34

score 1 · Answer 2

I can think of options to respect the decimal if you need to be exact operations or otherwise, round.

Respect decimals:

Use settype to convert the value to the data type you want, especially useful in your case if the values you get are actually strings.

// Antes de sumar
settype($cifra_monetaria, 'double');
var_dump($cifra_monetaria); // Te dirá el tipo de variable

Round decimals

Use round :

echo round(3.6, 0);      // 4
echo round(1.95583, 2);  // 1.96

score 1 · Answer 3

You are attacking the problem from the wrong side. You should establish a unique format between your data. You should not store them ever as varchar , always in a numeric format. If a protocol can not be established to receive the well-formatted data, a converter is implemented, and it is established in the protocol that, if it comes from those sources, it must be passed through the converter before inserting them.

However, if decimals are never 3, we can think of a somewhat "dirty" solution:
delete all points that are after 1 digit and followed by 3 digits (neither more nor less).

function eliminar_puntos_tres_digitos($valor){
    return preg_replace('/(\d)\.(?=\d{3}(?!\d))/', '$1', $valor);
}

Note that this method would fail with any number that has 3 decimal places.

Result:

Original: 123.00        Resultado: 123.00
Original: 123.0001      Resultado: 123.0001
Original: 123.456       Resultado: 123456
Original: 123.456.789   Resultado: 123456789
Original: 123.          Resultado: 123.
Original: .123          Resultado: .123
Original: 123.456.7     Resultado: 123456.7
Original: 123.456.789.1 Resultado: 123456789.1

Demo: link