Passing empty string to decimal field results in default of 0?

I have a migration with a column like this:


$table->decimal('budget', 19, 4)->after('description')->nullable();

However, when I submit an empty form field to this, it sets the value in my MySQL database to 0.0000, even though the field is clearly nullable.

I even tried this:


$table->decimal('budget', 19, 4)->after('description')->nullable()->default(NULL);

But got the same result. Anyone else dealt with this? I could always intercept the input field and manually set it to NULL if it's empty, but that seems like a poor solution. Is decimal not a good way to store US currency?

thepsion5

11 years ago

Best Answer

Level 25

The best practice (as far as I know) to store currency to use an integer field and store the number of cents.

As far as I know, if you want something to be null in the database you'll need to explicitly set it as null. You could do that with a mutator on your model class that does a strict check for an empty string and sets the attribute to null.

mikebronner

11 years ago

Level 16

If you look at the database structure using Sequel Pro or another MySQL Admin client, what is it defined as there? That should give you some hints as to why your database is acting differently than you expect.

Just looking at what you're describing, I am suspecting that you are inserting the value as a string into the database. Unfortunately MySQL will convert an empty string to 0 when attempting the insert because of the type conversion. My recommendation would be to check for empty string then set to NULL (not 'NULL') before saving. See the discussion here http://board.phpbuilder.com/showthread.php?10378196-RESOLVED-Updating-float-column-to-NULL-stores-0 that looks at this problem as well.

Hope this helps. :)

mikebronner

11 years ago

Level 16

@thepsion5 is right: store moneys in integers, to avoid precision errors with floating point numbers. Financially you also would most likely want a 0 instead of a NULL, unless you are trying to deduce a state of the transaction based on the NULL value. In that case i would recommend a dedicated field to track that, as database upgrades, scripts, migrations, etc. may end up changing the NULL value. Try to avoid deduction logic and attempt to make everything explicit. It's more maintainable and logical that way.

imJohnBon

11 years ago

Level 5

Thank you both for the advice and suggestions! This is all great.

Please or to participate in this conversation.