How to properly store the price as integer in DB?

Question

I have a field price like:

<div>
    <label for="price">Price</label>
    <input type="hidden" name="price" id="price"/>
    <input type="number"
           onkeydown="javascript: return event.keyCode == 69 ? false : true"
           min="0" step="any"
           class="form-control"
           value="{{ old('price') }}"
           name="price" id="price" placeholder="Price (Ex: 10,00)"/>
</div>

If the user introduce for exampel "10.15" the $request->all() shows this field like:

  "price" => "10.15"

But in the database the price is stored as 10. I have the column price as intenger.Then I also have a page where the price can be edited and the price in the form field appear as "10", but it should be "10.15".

Do you know how to store the value "10.15" in DB and show the proper value "10.15" in the edit page?

In the controller the price is stored in DB like:

'price' => $request->price,

10.15 isn't an integer, you can change the column type to `decimal` — Kisaragi, Aug 15 '18 at 22:12
But its not better to store in the db as integer instead of decimal? — John, Aug 15 '18 at 22:13
Because it looks like your passing a string for the `price` index. Cast to something else like a float. — Kisaragi, Aug 15 '18 at 22:15

Tarek Adam · Accepted Answer · 2019-05-30T06:13:34.283

3

UPDATE: As much as a few people don't seem to like it, a quick google search indicates that money should be stored in the smallest unit. In the case of dollars, that means pennies. In any case, stack won't let me remove an accepted answer.

If your ORM extends elequent model, then you should use getPriceAttribute() and setPriceAttribute($val) in your model. Also, I disagree with the other answer. I would store price as an integer based on pennies then use the getter and setter to produce the decimal. (FYI: modern commerce solutions like stripe use penny integers rather than decimals)

Product extends Model{
    public function getPriceAttribute(){
        return $this->attributes['price'] /100;
    }
    public function setPriceAttribute($val){
        $this->attributes['price'] = $val * 100;
    }
}

Usage:

$myProduct->price = 69.69;
echo $myProduct->price;

edited May 30 '19 at 06:13

answered Aug 15 '18 at 22:33

Tarek Adam

3,387
3
27
52

Thanks, to insert in db is used Ticket::create(['price'] => $request->ticke_price ]). There are other fields but the price is stored like that. You are saying to create in the Ticket model that 2 methods and then use like Ticket::create(['price'] => setPrice($request->ticke_price )]). ? – John Aug 15 '18 at 22:42
I'm not sure if the $fillables of the model go thru the setter. Something tells me that they don't. You may need to set price with $ticket->price = xx.xx – Tarek Adam Aug 15 '18 at 22:46
So is not possible to use Ticket::create like "Ticket::create([ 'name' => $request->ticket_name, ... 'price' => setPrice($request->registration_type_price), 'c_id' => $c->id ]); $c->save();"? – John Aug 15 '18 at 22:48
Is necessary to first create the ticket with the other fields like name, etc, and then set the price? – John Aug 15 '18 at 22:48
create the ticket with all the fillables other than price, then use $myNewTicket->price = 12.34 and save(). Your db should show 1234 integer. getPriceAttribute() and setPriceAttribute are called auto-magically by laravel when you try to access the attribute. – Tarek Adam Aug 15 '18 at 22:50
Thanks, it works like that. But in the edit page the price field "" shows "10,15". But without the function " public function getPriceAttribute(){ return $this->attributes['price'] / 100; }" it shows "1015". Do you know why it shows the correct value "10,15" with getPriceAttribute? – John Aug 15 '18 at 23:05
$myProduct->some_property can use the getter and setter magic to mutate the value. getSomePropertyAttribute() and setSomePropertyAttribute($value) will intercept any attempt to get or set a db column named some_property. – Tarek Adam Aug 15 '18 at 23:09
also, on a side note, you can fill objects like this... Something::create($request->only(['my_field',my_other_field',''])) as long as the names of request fields and db fields line up, and the model $fillable allows it. – Tarek Adam Aug 15 '18 at 23:12
That's a bad solution. If he have an decimal, then the value should be stored as an decimal, not as integer. – Elias Soares Aug 15 '18 at 23:44
I can't say that I'm an authority on such things... but that's how it's being done these days. – Tarek Adam Aug 15 '18 at 23:45
https://stackoverflow.com/questions/35326710/stripe-currency-are-all-amounts-in-cents-100ths-or-does-it-depend-on-curren – Tarek Adam Aug 15 '18 at 23:47
After a quick google - it seems to be all about the "smallest currency unit". @EliasSoares – Tarek Adam Aug 15 '18 at 23:48

score -2 · Answer 2 · answered Aug 15 '18 at 22:11

-2

monetary values should be stored in the database in a decimal field not an integer.

answered Aug 15 '18 at 22:11

Clint

973
7
18

Thanks, but with the db column as decimal it is also stored "10" instead of "10.15". – John Aug 15 '18 at 22:16
2

It is better to store monetary values as integers in the smallest size possible. For example, US Dollars should be stored as an integer value in pennies. Floating point numbers are not accurate. https://stackoverflow.com/a/225592/9836025 – Jonathan Aug 15 '18 at 22:24
Thanks, but do you know to store in the db as integer 10.15 instead of 10? – John Aug 15 '18 at 22:26
@John Everyone here is telling you to store the number as a decimal instead, and you insist to use integer. When you came to ask help, be open to accept the right answer, not the answer that you want to listen. When you create the decimal field on your migration, you must specify the number of digits: `$table->decimal('value', 12, 2)` (12 digits, being 2 decimal digits and 10 integer digits) – Elias Soares Aug 15 '18 at 23:47

How to properly store the price as integer in DB?

2 Answers2