GDP GDP - 1 year ago 63
JSON Question

How to properly format PDO results? - numeric results returned as string?

With relative newness to AJAX, and now just starting to learn PDO, the added level of ReSTler has me completely boggled. Modeling the below code from Restler samples, I don't know how to change the output format from what PDO is returning to what Restler and Highcharts is expecting.

How do I change this code to get from the current format to the required format? (The results will generally be 5K-10K records, if that's a factor in handling the MySQL result.)

ReSTler API Code Snippet:

$sql = "SELECT ....."
try {
$stmt = $this->db->query($sql);
return $stmt->fetchAll();
} catch (PDOException $e) {
throw new RestException(502, 'Listing History: ' . $e->getMessage());

Current Output Format (includes unwanted column names):

"chart_date": "1118966400000",
"bandwidth": "10.01",
"views": "101"
"chart_date": "1119225600000",
"bandwidth": "20.02",
"views": "101"

Desired Output Format (numeric and without column names):


Edit using suggested

Per the answer from @Ricardo Lohmann, I tried fetch(PDO::FETCH_NUM), which DID remove the column names, but all columns returned seem to be string, not numeric, as the data actually is, so try this, giving me the right data type - is this part of PDO to unilaterally return string?

while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
$array[$x][0] = intval( $row[0] );
$array[$x][1] = intval( $row[1] );
$array[$x][2] = intval( $row[2] );
return $array;

Answer Source

PDO::FETCH_NUM is the way to go, though it does not mean numeric columns will remain numeric, it only means you will get an indexed array instead of an associative array (thus, it only accomplishes the omission of the column names).

The MySQL PDO driver always returns strings for numeric columns, which is a known bug, but unfortunately, one of the many bugs that PHP devs blatantly disregard as "thank you but this is not a bug".

You can force json_encode to use actual numbers for values that look like numbers: json_encode($data, JSON_NUMERIC_CHECK) (since PHP 5.3.3).