For money field integer or decimal?

Published 2 years ago by Khudadad

I'm trying to save amount of money in the database as integer, but when I want to display it, it gets rounded, How can I maintain integrity of amount of money.

    ie. 28.8808 rounded to 29
Best Answer (As Selected By Khudadad)
Snapey

Check out Accessors and Mutators https://laravel.com/docs/5.2/eloquent-mutators#accessors-and-mutators

For retrieving the value;

Set the column to Integer and add to the model;

    public function getPriceAsCurrencyAttribute(){
        if(isset($this->attributes['price']))
        {
            return money_format('%i', $this->attributes['price']/100);
        }
        return False;        
    }

Use in view etc, $item->priceAsCurrency

See money_format http://php.net/manual/en/function.money-format.php

I used /100 because I only need to support two decimal places. If you need to support more then change as appropriate

to save a new price

Add to model

    public function setPriceFromCurrencyAttribute($value) {

        $this->attributes['price'] = $value*100; 
    }

saving price;

$item->priceFromCurrency = '14.99';
AlexYa
AlexYa
2 years ago (24,750 XP)

The best way is to use decimal field for money.

But if you have to use integer field:

  1. before saving value into db multiply it on 100.
  2. after loading value from db divide it on 100.
Snapey
Snapey
2 years ago (898,985 XP)

What I do is store it as Integer and then add new accessors to the model so that I can get it out in currency format

for example $item->price will give me 4599 but $item->priceGBP will give me 45.99 because GBP works with two decimal places.

Khudadad

I used decimal but when I inserting into database it was getting changed. ie. 55400 to 55.0000 , format was (10,4)

Khudadad

Thanks @Snapey , could you explain a little more with example I really need it. Thanks

Snapey
Snapey
2 years ago (898,985 XP)

Check out Accessors and Mutators https://laravel.com/docs/5.2/eloquent-mutators#accessors-and-mutators

For retrieving the value;

Set the column to Integer and add to the model;

    public function getPriceAsCurrencyAttribute(){
        if(isset($this->attributes['price']))
        {
            return money_format('%i', $this->attributes['price']/100);
        }
        return False;        
    }

Use in view etc, $item->priceAsCurrency

See money_format http://php.net/manual/en/function.money-format.php

I used /100 because I only need to support two decimal places. If you need to support more then change as appropriate

to save a new price

Add to model

    public function setPriceFromCurrencyAttribute($value) {

        $this->attributes['price'] = $value*100; 
    }

saving price;

$item->priceFromCurrency = '14.99';
jekinney
jekinney
2 years ago (212,765 XP)

I know this is answered, but the possibility of an issue with using decimal or float is rounding. If your always going to use one type of currency (i.e.: USD) then what ever. But once you don't you'll have issues.

Please sign in or create an account to participate in this conversation.