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

Question

With a relative novelty to AJAX, and now, just starting to learn PDO, the added level of ReSTler completely clogs me. While modeling the code below from Restler samples, I don’t know how to change the output format from which PDO returns to what Retler and Highcharts expects.

How can I change this code to get from the current format to the required format? (Results will typically be 5K-10K entries if this is a factor in processing 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" }, 

The format of the required output (numeric and without column names):

 [ [ 1118966400000, 10.01, 101 ], [ 1119225600000, 20.02, 202 ], 

Edit using suggested fetch(PDO::FETCH_NUM) :

In response from @Ricardo Lohmann, I tried fetch (PDO :: FETCH_NUM), which DID removes the column names, but all the returned columns seem to be string rather than numeric, since there really is data, so try this by giving me the correct data type - Is this part of PDO for one-way row return?

 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; 

Answer 1

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

The MySQL PDO driver always returns rows for numeric columns, which is a known error , but unfortunately one of the many errors that PHP developers explicitly ignore as "thank you, but this is not an error."

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

Answer 2

Try setting fetchall instead of fetchall, for example:

 return $stmt->fetch(PDO::FETCH_COLUMN); 

You can see the link.

Answer 3

The only way I got around this problem (using the SQLSRV driver) is to force SQL to send all data types as a string in the OUTPUT parameter. And then converting all the values ​​using the correct data type. This can be difficult for a large dataset.

Despite the fact that PDO developers say that this is a problem with the driver (and not their error), I never had this problem using the SQLSRV driver without PDO ...

Answer 4

I was hoping there was a built-in way to do this, so I did a google search: v.

I am creating my own custom CMS application, which I think is "Drupal-like" (I never used Drupal, but I use a custom system based on Drupal). By this, I mean that I have a table in my database that basically describes all the fields in other tables, and when I extract fields from these other tables, I load the descriptions and data types of my data.

eg.

tablex Fields: Name, age, old enough

tabledescriptions :

 fieldname | datatype | minlength | parent ___________|____________|_____________|________ Name | String | 5 | tablex Age | Int | 1 | tablex Old Enough | Boolean | 1 | tablex 

So when I load data from tablex , I look in tabledescription for things where parent = 'tablex', and uses the switch statement for datatype data

 foreach ( ... ) { switch ($desc->datatype) { case 'int': (int) $tablex->data; break; } } etc.