I changed my PHP MySQL statement to a prepared statement, and floats changed decimal point from peri-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

When you use the prepared statement, you're also using the MySQL Native Driver (MYSQLND). When it fetches MySQL FLOAT columns, by default it returns them as PHP floats rather than strings. So PHP's locale is used when formatting them.

This is controlled by the option MYSQLI_OPT_INT_AND_FLOAT_NATIVE. You can unset this to get strings as before.

$conn->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, false);

Note that this has to be called between creating the mysqli object and connecting to the server. So you can't use new mysqli, you have to call mysqli_init() and $conn->real_connect():

$conn = mysqli_init();
$conn->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 10);
$conn->real_connect('server', 'user', 'pass', 'database');