19

I am trying to save money format in laravel 5.1.

Here is table price define:

$table->decimal(price,6,2);

For instance ; when 1.000,50 Turkish Liras saving to MySQL this format 1.00

How can solve this issue?

badman
  • 279
  • 1
  • 5
  • 13
  • This already has answers at: [Creating a Column for Price in Laravel Schema](https://stackoverflow.com/questions/44985011/creating-a-column-for-price-in-a-laravel-schema) – Top-Master Mar 10 '22 at 05:55

3 Answers3

23

I would suggest not using a float value to store currency as decimals, since floats don't act exactly as you would expect them to, due to the way they are stored in the system.

You would be much better off storing the value in "kuruş" (the subunit of Turkish Lira), as it will be much, much easier in the long run.

In other words, storing the lowest unit you think will be ever required, like storing Centi-meters instead of Meters (Centi is originally Greekish name for "0.01" number).

Secondly, if you're using Eloquent you can use mutators/accessors on the Model e.g.

public function getPriceAttribute($price)
{
    return $price / 100;
}

public function setPriceAttribute($price)
{
    $this->attributes['price'] = $price * 100;
}

That way you don't have to manually convert the price.

Update

If you're using Laravel 9 or above, you can use the new Attribute syntax instead:

use Illuminate\Database\Eloquent\Casts\Attribute;

protected function price(): Attribute
{
    return Attribute::make(
        get: fn ($price) => $price / 100,
        set: fn ($price) => $price * 100,
    );
}
Rwd
  • 34,180
  • 6
  • 64
  • 78
  • I think this is the best way to go. Other solutions (as far as I know) will cause trouble in the future if you want for example send the data as json response. Then you will have to use JSON_NUMERIC_CHECK as option which may solve your problem. But let's say you want to use 3 decimal places, then you are in big trpuble because the JSON_NUMERIC_CHECK option will only pass 2 and unfortunately you can't combine it with JSON_PRESERVE_ZERO_FRACTION to preserve the places. But using accessors and mutators like in @Rwd's answer then you won't experience that pain. – Skeletor Oct 30 '20 at 11:08
  • @Rwd what do you mean by "storing the value in kuruş" ? What does kuruş mean? Typo? – Sam Bellerose Nov 19 '21 at 19:02
  • 2
    @SamBellerose kuruş is the subunit of Lira i.e. 100 kuruş = 1 lira. Like how pence is to pounds or cents is to dollars. https://en.m.wikipedia.org/wiki/Turkish_lira – Rwd Nov 20 '21 at 09:10
22

You can try defining your price like this

$table->decimal('price',9,3);

Where,

9 is the precision, ie 1234567.89 has a precision of 9

3 is the number of decimal places, ie 123456.789 has a scale of 3

In other words, if we use less decimal-places than 3, we can use remaining for real-number places.

You can refer to this link for about precision and scale of database How do I interpret precision and scale of a number in a database?

Top-Master
  • 7,611
  • 5
  • 39
  • 71
Saad
  • 1,155
  • 3
  • 16
  • 36
2

Illuminate blueprints do not support money columns. AFAIK, the money column type itself is only supported in a couple of DBMSes.

What you can do is issue an ALTER TABLE statement to the database after the initial CREATE statement:

Schema::create('my_table', function(Blueprint $table) {
    $table->decimal('my_money_column', 999, 2);
    // ... the rest of the column definitions
});

DB::statement("
    ALTER TABLE my_table ALTER COLUMN my_money_column
        SET DATA TYPE MONEY;
");

Beware, though, as this will (probably) break cross-DBMS compatibility of your migration scripts.

amphetamachine
  • 27,620
  • 12
  • 60
  • 72