Decimal point "." changed to "," in base of setlocale()

[Alessio Dal Bianco]

Hi all,

I have experienced some weird problems when using the Float field combined with the translation of setlocale() arguments.

It seems that with specific setlocale() (in my case setlocale('it_IT')) leads to transform the decimal separator from "."to ","  (for example "2.566" is transformed to "2,566") so all queries contains a "," and mysql stores only the integer part (without any errors).

Good query:

INSERT INTO `field_testfloat` (pages_id, data) VALUES('1024', '24.534') ON DUPLICATE KEY UPDATE data=VALUES(data)

Bad query:

INSERT INTO `field_testfloat` (pages_id, data) VALUES('1024', '24,534') ON DUPLICATE KEY UPDATE data=VALUES(data)

The naive solution is to leave blank setlocale(), but i wonder if it is possibile to correct this behaviour.

[ryan]

I'm not really sure what the best solution is to this, and how many other locale rules we might have to potentially account for (if any?), but one solution would be for FieldtypeFloat::sleepValue to replace commas with periods. Do you want to try adding the following to your /wire/modules/Fieldtype/FieldtypeFloat.module, or replace with the attached file?

        public function ___sleepValue(Page $page, Field $field, $value) {
                if(strpos($value, ',') !== false) $value = str_replace(',', '.', $value); 
                $value = (float) $value;
                return $value; 
        }

FieldtypeFloat.module

[Alessio Dal Bianco]

I'm not really sure what the best solution is to this, and how many other locale rules we might have to potentially account for (if any?), but one solution would be for FieldtypeFloat::sleepValue to replace commas with periods. Do you want to try adding the following to your /wire/modules/Fieldtype/FieldtypeFloat.module, or replace with the attached file?

        public function ___sleepValue(Page $page, Field $field, $value) {
                if(strpos($value, ',') !== false) $value = str_replace(',', '.', $value); 
                $value = (float) $value;
                return $value; 
        }

I've tried with your patch but it seems that when you make the (float) cast the "," returns

public function ___sleepValue(Page $page, Field $field, $value) {

        $this->message("pre fix - ".$value); // print : 84,3

        // handle potential issue with other locales
        if(strpos($value, ',') !== false) $value = str_replace(',', '.', $value);

        $this->message("after fix - ".$value); // print : 84.3

        $value = (float) $value;

        $this->message("after cast - ".$value); // print again : 84,3

        return $value; 
}

Another solution seems to add "setlocale(LC_NUMERIC, 'en_US');" in LanguageSupport.module:

wire('config')->dateFormat = $this->_('Y-m-d H:i:s'); // Sortable date format used in the admin
$locale = $this->_('C'); // Value to pass to PHP's setlocale(LC_ALL, 'value') function when initializing this language // Default is 'C'. Specify '0' to skip the setlocale() call (and carry on system default).
		
if($locale != '0') setlocale(LC_ALL, $locale);
        
// Fix for float number with incorrect decimal separator.
setlocale(LC_NUMERIC, 'en_US'); 

Not sure if is the correct way to avoid this problems...

[ryan]

Thanks for trying it out. I didn't realize that locale settings went as far as typecasting. Seems like typecasting should be about maintaining a type for the language, not for display. While we could set the LC_NUMERIC, and maybe that's the right way to go (I'm not yet sure), I'd be worried about the situations when someone wants to override the numeric stuff. Perhaps FieldtypeFloat needs to do this in the sleepValue method: save the locale, set it to something compatible with PHP and MySQL, then set it back to whatever it was. That seems like maybe the way to add this with least impact. 

[kixe]

I have different languages in the backend, a template with a field of type float. I cannot save a float in other languages (polish and german) than default (english). The floats are rounded to full integer value and stored like this.


added 30 October 2013 09:37 AM
To prevent language troubles I am using a workaround now:
input type text instead of float with a regex (settings > pattern):

\d{1,4}([\.]\d{1,3})?

For validation of a weight of max. 10,000 kg (9999.999 kg) with max. 3 decimal places, only the dot is allowed as seperator.

 

[ryan]

If anyone knows of a good permanent solution for this issue that doesn't break in one language or another, please let me know or submit PR or patch. It seems like PHP has setup numerous tripwires on this one for us. Until we get this one figured out, I think Kixe's solution is probably the best way to go if you need to reliably store floats across languages that use different things for decimals. But I'd like to get a permanent solution in place for FieldtypeFloat, as text is not the ideal way to store these numbers from the DB, or make them searchable. But at least it's reliable. 

[Alessio Dal Bianco]

What about to use "number_format"  in FieldtypeFloat.module ?

It should be reliable because it is language independed and (maybe) when the queries are constructed all the values are treated as strings, so you dont'need to have a "float" value anymore.

    public function ___sleepValue(Page $page, Field $field, $value) {
        // Field converted in string with number format.
        $value = number_format($value, $field->precision, ".","");
        return $value; 
    } 

FieldtypeFloat.module

[doolak]

What about to use "number_format"  in FieldtypeFloat.module ?

It should be reliable because it is language independed and (maybe) when the queries are constructed all the values are treated as strings, so you dont'need to have a "float" value anymore.

    public function ___sleepValue(Page $page, Field $field, $value) {
        // Field converted in string with number format.
        $value = number_format($value, $field->precision, ".","");
        return $value; 
    } 

FieldtypeFloat.module

Anybody tried this out?

[Alessio Dal Bianco]

Hi doolak,

i've tested it and works fine.

But i don't know if there are drawbacks since this function return a string instead of float, and is "hookable" by other modules.

[ryan]

Alessio, i think your solution might be the one we've been looking for. While sleepValue() would ideally return a float, that's just not possible here unless MySQL recognizes the same float format as the PHP locale (which apparently it does not). So converting to a string actually seems like quite a good idea. I am going to make this change on the dev branch and test it some here, but so far I think this seems like the best solution. 

[kixe]

I started a new topic here:
https://processwire.com/talk/topic/7542-development-fieldtypefloat-fieldtypedecimal/

[pine3ree]

The simpler solution is to always use: setlocale(LC_NUMERIC, 'C') after setting LC_ALL to custom locale. 

This helps avoiding a lot of issues as LC_NUMERIC tells the code parser how to output numeric expressions. For instance with:

setlocale(LC_ALL, 'it_IT.UTF-8');

1.234 will be written as 1,234 when used as string, like in string concatenation (as in db query builders) . But database and most other data endpoints still expect 1.234 for decimals. 

It is always better to use LC_NUMERIC 'C' and call numeric/monetary output formatting helpers explicitly in templates.