I have a MySQL table with floats in it. In PHPMyAdmin, they’re listed as e.g. “55.123”
I used to pull them out using
$sql = "SELECT latitude,longitude FROM table WHERE id=" . $id; $conn->query($sql);
When I print my floats, I get e.g. “55.123” – hardly surprising. For obvious reasons I needed to change this query to a prepared statement:
$sql = "SELECT latitude,longitude FROM table WHERE id=?;";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i",$id);
$stmt->execute();
Now, when I print my float, I get e.g. “55,123” – with a comma instead of with a period.
I suppose that my
setlocale(LC_ALL, 'da_DK.utf8');
might have something to do with this, but I really don’t know what to do here. Because either I should stop changing my queries to prepared statements, or I should convert the floats to strings and replace the commas with periods. Either solution is less than ideal. Suggestions?
Advertisement
Answer
This means that you are using an older version of PHP. Prior to PHP 8.0 locale setting affected the conversion of floats to strings. This means that if you had a float and you used something like echo to output it out, the conversion to string would replace . with , for locales that use , for decimal separator. e.g.
setlocale(LC_ALL, "de_DE.UTF-8"); $number = 42.42; echo $number; // outputs 42,42 in PHP 7
Once you upgrade to PHP 8 or newer, the output should be always the same irrespective of the locale.
As to why there’s a difference between prepared and non-prepared queries in mysqli: mysqli prepared statements use a binary protocol (as opposed to textual) when talking to the database. This means that the data that arrives from MySQL is already an integer, float or string. It was decided that by default there will be no cast to a string. After all, what sense would that make? But to make the textual protocol compatible with binary one, an opt-in setting was added that would ask PHP to automatically cast numerical strings into PHP native integers and floats. You can enable that setting at any time by calling:
$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);
The binary protocol (prepared statements) ignores this setting.
I don’t know why the setting is opt-in and not opt-out. PHP 8.1 changed this in PDO, but not in mysqli.