MySQL decimal fields returned as strings in PHP

Question

By default mysqli returns all values as strings, the MYSQLI_OPT_INT_AND_FLOAT_NATIVE option allows you to convert ints and floats to their appropriate types. Though this does not affect decimal fields.

Is there a way to automatically cast all decimal fields to a php float type without manually calling $value = (float) $row->some_decimal_field?

score 8 · Accepted Answer · answered Aug 21 '13 at 16:37

8

I highly doubt it. Decimals use fixed point math, and there is no data type in PHP that can provide this. Floats come close, but they are in fact rounded, meaning that assigning 2 to a float could result in 1.99999999999999999 instead. So even if MySQL offers a way to get a decimal into a PHP float, you are risking loss of data by casting from a decimal to a float.

To handle this cleanly, you'd need something like GMP, but as you can probably guess MySQL can't provide that for you automatically. You will need to do it manually in PHP.

answered Aug 21 '13 at 16:37

Jeroen van den Broek

855
5
16

3

The answer is correct (+1), however the example of the value 2 is not right. The common IEEE 754 floating point representation (which [PHP uses](http://php.net/manual/en/language.types.float.php)) can represent 2 precisely. A better example would be 0.1 which cannot be exactly represented and acts more like 0.10000000000000009 – trincot Mar 14 '19 at 08:04

amaster · Answer 2 · 2013-08-21T17:00:21.907

Here is something that you can do with PHP to solve the problem:

function string_to_float($foo){
    if($foo*1==$foo && !empty($foo)){
        return $foo*1;
    } else {
        return $foo;
    }
}

string_to_float($row->some_decimal_field);

The real question is, Why do you need to convert the type of the decimal string? If you are trying to use it in math, php will make the conversion automatically for you. As a decimal string is equivalent to a float with the same value. Here is a simple test:

$foo = "1.2";
$bar = 1.2;


if($foo===$bar){
  $equivalent = "is";
} else {
  $equivalent = "is not";
}

print '"1.2" '.$equivalent.' equal to 1.2 in type and value<br />';

if($foo==$bar){
  $equivalent = "is";
} else {
  $equivalent = "is not";
}

print '"1.2" '.$equivalent.' equal to 1.2 in value<br />';

$foo = "1.2"*1;
$bar = 1.2;

if($foo===$bar){
  $equivalent = "is";
} else {
  $equivalent = "is not";
}

print '"1.2"*1 '.$equivalent.' equal to 1.2 in type and value<br />';

which will return:

"1.2" is not equal to 1.2 in type and value
"1.2" is equal to 1.2 in value
"1.2"*1 is equal to 1.2 in type and value

You would be casting a fixed point data type to a floating point data type if you do any math on the string using normal operators. You will get strange results or weird approximations of your numbers if you don't use GMP as mentioned above. — Jeroen van den Broek, Aug 21 '13 at 18:56
OK your right, it may not be the right answer, but it is an answer that may help others looking to do likewise. — amaster, Aug 21 '13 at 19:56

MySQL decimal fields returned as strings in PHP

2 Answers2

Related