Null value in editor when update row?

ztevie

I have a field in the database which contains a float value.
When I show that value in Datatables I show the float value if it's more than 0, otherwise I show nothing.
When I update/Create a row and set a value to for example 2.3, and then change it back to nothing, when I open the update/create editor the next time it will show the number 0 in the input field.
I see in the database that the value is set to 0 even if I have the default value "null" there. Is there anyway to return a null value to database so it accepts it as null and the input field is always empty in the editor when the value is not over 0?
I use the numeric validation on the server side so I guess it will update database with 0 if empty...?

tangerine

This isn't a DataTables issue.
A numeric field in MySQL cannot default to null.

ztevie

Oh? But when I create new entry in database this field will be null if I don't set a value?
I have set this field to default to null in phpmyadmin... And the field is set to float.

tangerine

I don't use any floats, so I had to look twice at this. It seems that a float field will accept a null default provided that it is not indexed.
In all my own usage of numeric fields, the default will always be zero even if I (wrongly) set them to default to null. Probably all my own numeric fields are indexed, so the anomaly never arose.
If your float is indexed, then we're back where we started: zero, empty, or null will all be saved as zero. I think....

allan

I suspect it is a quirk of MySQL when you attempt to save an empty string to a float. Rather than rejecting it, which I feel it should do (I haven't actually checked this, so it is possible it does...) it might be setting that as 0.

What to do is to use the ifEmpty setFormatter. That can tell the Editor libraries to write a null to the database if an empty string is submitted (since you can't truly represent null in an HTTP parameter - we'd need to use JSON or similar for that).

Allan

ztevie

Ah, I knew you'd have thought of this...
I'll try it...
But in your link you have nullEmpty as well which is even more fitting in this case

allan

Yeah, the nullEmpty function was added around 1.4 I think, while ifEmpty was added in 1.5 (or thereabouts) and it much more generic. I haven't deprecated nullEmpty, but my personal preference is ifEmpty - I think it reads a little easier.

Allan

ztevie

It worked 100% perfect! Thanks!