PHP > MySQL Floating point separator localisation issue

Question

I've seen a few questions on people trying to get MySQL to use ',' as the floating point separator - what I'm trying to do is stop PHP from using it on a website running under the 'nl_NL' locale.

So in the code PHP is writing an SQL query ending like:

" ... HAVING `relevance` >= {$fFloatingPointNumber}";

The problem is, because PHP's locale is running as 'nl_NL' when it converts that floating point number to a string it's using ',' as the separator (e.g. 1,5).

What I'm doing to prevent this currently is:

" ... HAVING `relevance` >= " . number_format($fFloatingPointNumber, 2, '.', '');

Is there a better way of doing this - or is this my best bet?

Answer 1

Solution 1

I will post my answer in case it is the only solution apart from @Pete one.

I would suggest switching the locale to GB or some other period separated float/double locale before each query then switching back to the correct locale after. I can not think of any other way around this.

Solution 2 (Best Bet)

You could always use the number_format method as follows

$stringversion = number_format($theFloat, 2, ".","");

Pretty sure this would work, the documentation is here http://php.net/manual/en/function.number-format.php