Iam using PHP (5.6.8) to act as the middle tier between a MySQL database and returning the results in JSON format.

We are using the PHP extension (PDO) to do the database interaction, but have noticed that all numeric values get returned as a string. The database itself is running on a Linux installation, but during development, both the client and middle tier are running on Windows.

I set the connection parameter "ATTR_STRINGIFY_FETCHES" to false but that made no difference. e.g. $dbConnection->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

This is the PHP function that I am using to retrieve data and it is tied to the RESTful interface.

function getAllObjects() 
{
    $sql = "SELECT * FROM Tbl_Objects ORDER BY 1";
    try {
	$db = getDB();
	$stmt = $db->query($sql);  

	$objects = $stmt->fetchAll(PDO::FETCH_OBJ);
	$db = null;
	echo json_encode($objects);
    } catch(PDOException $e) {
    echo '{"error":{"text":'. $e->getMessage() .'}}'; 
    }
}

I also tried "mysqli" but I get the same results in that the numeric values are all converted to Strings. If I use the "cast_query_results($rs)" function on the net, I do get numeric converted correctly, but it seems to me that this is rather a fudge, rather than a solution.

Any ideas on how to get this to work natively please?

    I'm not seeing why numbers coming from the database would be converted to JSON string literals (another fudge would be to set JSON_NUMERIC_CHECK in [man]json_encode[/man]). Have you narrowed down at what point the type goes wrong (is it during JSON encoding, or are they recorded as strings inside the objects returned by fetchAll, or are they the wrong type in the database to begin with)?

      The data-types in the database are defined as INT so that would appear to be correct. The JSON_NUMERIC_CHECK seems to do the trick.

      Best Regards

        PDO and other DB access layers (like mysqli, oci) return data fetched from the database as strings. If you don't convert it from a string before making json, it will stay a string when creating the json.

          Write a Reply...