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 ....."
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
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):
[
[
1118966400000,
10.01,
101
],
[
1119225600000,
20.02,
202
],
Edit using suggested fetch(PDO::FETCH_NUM)
:
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] );
$x++;
}
return $array;
PDO::query() returns a PDOStatement object, or FALSE on failure.
To prepare and execute a single SQL statement that accepts no input parameters, use the PDO::exec or PDO::query method. Use the PDO::exec method to execute a statement that returns no result set. Use the PDO::query method to execute a statement that returns one or more result sets.
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With