decimal precision for vat taxes in php and mysql

Question

i have a question that i cannot resolve by myself, and im was thinking that i can be pushed here to get the best precision calculating vat prices in php and mysql.

For example:

I have the gross price = 1199
The TAX Amount is      = 24
to extract the vat     = 1199 / 1.24 = 966.93548387096 round(966.93548387096,2) = 966.94
ok
now this value will be saved in mysql with decimal 12,2

Now, if you add the vat = 966.94 * 24 / 100 = 232.0656 round(232.0656,2) = 232.07
Final                   = 966.94 + 232.07 = 1199.01

My question is, how can i get 1199 without floating ?

Any help is apreciated.!

UPDATE:

The value saved in database is 966.94 from where is applying the tax vat amount.

an option can be to save the value with 4 decimals when i introduce the prices in the database, but for that i will need to override an array to extact the column and take out the vat.

Like:

$arrResult = array();
            $handle = fopen("uploads/csv/".$csv, "r");
            if( $handle ) {
            while (($row = fgetcsv($handle, 1000, ",")) !== FALSE) {
            $arrResult[] = $row;
            }
            fclose($handle);
            }
            $titles = array_shift($arrResult);
            // need to take out the price and apply price / 1.24 and save the value as 4 decimal in mysql
            $keys = array('code', 'price');

            $final = array();

                    foreach ( $arrResult as $key => $value ) {
                                $final[] = array_combine($keys, $value);
                    }

score 1 · Answer 1 · answered Jan 22 '13 at 16:05

1

You can use CAST() function of MySQL.

Example:

mysql> select 1199.01 as floating, cast( 1199.01 as signed ) as non_floating;
+----------+--------------+
| floating | non_floating |
+----------+--------------+
|  1199.01 |         1199 |
+----------+--------------+
1 row in set (0.00 sec)

Refer to:
Type Casting in MySQL

CAST can be used to convert to the following types:

 1. BINARY[(N)]
 2. CHAR[(N)] DATE
 3. DATETIME DECI
 4. MAL[(M[,D])]
 5. SIGNED [INTEGER]
 6. TIME
 7. UNSIGNED [INTEGER]

answered Jan 22 '13 at 16:05

Ravinder Reddy

23,692
6
52
82

Good, is to complicated to get the values from db that are saved with 01 difference, what i want is to save in mysql with the correct value. – user2000260 Jan 22 '13 at 16:09
@user2000260 You can save the original value in db table as is. And for display purpose you can retrieve it as shown in example. – Ravinder Reddy Jan 22 '13 at 16:10
@user2000260 If you want to show some values in full and some values you should apply your own strategy. Your requirement in the above 1st comment is unclear. Update your posting with proper example values, like what you want to be in db and what you want to display. – Ravinder Reddy Jan 22 '13 at 16:21
i have updated the question and added the start, need to take out price from array and apply = price / 1.24 to the gross value from csv. – user2000260 Jan 22 '13 at 16:30

score 0 · Answer 2 · answered Nov 28 '20 at 18:55

0

This is an old question, but you should never use floats for monetary amounts.

This is because floats causes a lot of troubles on calculations (if you search the web, you will find tons of articles that explain this problem).

So, the solution is to use a value object like https://github.com/moneyphp/money

Then you will save in the database the base amount that is a simple integer in the smallest unit.

This means that 100,00 Euro is represented as 10000 cents.

Operating only on integers (and using strings internally) this library is very very precise!

answered Nov 28 '20 at 18:55

Aerendir

6,152
9
55
108

Or you can simply use `DECIMAL`, you've dug up a very old question and not really added any knowledge, you've not explained *why* `FLOAT` data types are bad, you've simply said they are, and you've gone for a solution that is needlessly nuanced and requires the programmer (and others) to remember the value is in cent units rather than the more usual currency unit. – Martin Nov 28 '20 at 19:01
1. DECIMAL is a field type in MySQL and not a type in PHP; 2. The object of this discussion is not to demonstrate why floats are not reliable; 3. The use of a value object is the most reliable way to manage monetary values; 4. You didn’t spent a minute to read how the value object I suggested works, otherwise you wouldn’t have commented. – Aerendir Nov 28 '20 at 21:43

decimal precision for vat taxes in php and mysql

2 Answers2