array of arrays PHP mySQL JSON

[Andrew Koper]

I am using Google Chart API to make a line chart.  The data is in a mySQL database.  Google Chart API expects a JSON object of data that is an array of arrays.  I am struggling to generate a JSON object in this format with PHP.  There should be an example of this on the Google Chart API documentation, but they read in a pre-formated text file in their JSON/AJAX example, this never exists, and storing data in a mySQL server is common.  Frustrating!  mysql_fetch_array($result) is adding double quotes in a way that is creating a problem.  json_encode() encodes what it gets. GIGO.  What is the proper way to create a JSON object that is an array of arrays with PHP from data in a mySQL db?

[asgallant]

This is what I use (I use PDO's rather than PHP's mysql functions, but you can adapt this to your needs):

$query = $db->prepare($queryStr);

$query->execute($params);

$results = $query->fetchAll(PDO::FETCH_ASSOC);

// $cols is an array of MySQL column names mapped to DataTable type and label

foreach ($cols as $colKey => $col) {

if ($col['type'] == 'tooltip') {

$output['dataTable']['cols'][] = array ('type' => 'string', 'role' => 'tooltip', 'p' => array ('role' => 'tooltip'));

}

else {

$output['dataTable']['cols'][] = array ('type' => $col['type'], 'label' => $col['label']);

}

}

foreach ($results as $row) {

$temp = array ();

foreach ($cols as $colKey => $col) {

$temp[] = array('v' => ($col['type'] == 'number') ? (float) str_replace(',', '', $row[$colKey]) : $row[$colKey]);

}

$output['dataTable']['rows'][] = array ('c' => $temp);

}

$output['options'] = $setup['options'];

$output['chartType'] = $setup['chartType'];

// set up header and output json (first two prevent IE from caching queries)

header('Cache-Control: no-cache, must-revalidate');

header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');

header('Content-type: application/json');

echo json_encode($output);

[ui]

asgallant, can you please post an example json file and associated $params. It would really help me understand your code.

Thank you

[asgallant]

This script is for pulling data from a SQL database, not from a file, so there isn't any example JSON file to show you.

$params is an array of bound parameters used in a SQL query run through PHP's PDO library (a single, unified query interface for multiple SQL databases (MySQL, Oracle, etc)) - see http://us.php.net/manual/en/book.pdo.php for details.  Essentially, bound parameters help defend against SQL injection attacks.  As an example, you might have a query string like this:

$queryStr = "SELECT foo, bar, baz FROM myTable WHERE qud = {$_GET['qud']}";

$query = $db->prepare($queryStr);

$query->execute();

which is vulnerable to SQL injection (ie, someone enters "1; DROP TABLE myTable" into the GET parameter 'qud', which would then cause your database to drop the table when executed).  To defend against this, you could use a bound parameter:

$queryStr = "SELECT foo, bar, baz FROM myTable WHERE qud = :qudValue";

$params['qudValue'] = $_GET['qud'];

$query = $db->prepare($queryStr);

$query->execute($params);

which marks ":qudValue" as a query parameter, and so whatever is matched to it (via the $params array) gets interpreted as a value, not an addition to the SQL statement.  So in the case of the previous injection attack, this would attempt to match the string "1; DROP TABLE myTable" to the column qud, which would likely result in 0 matches, rather than a dropped table.

You can, of course, retrieve your data using the mysql library (or whatever was built for your database) rather than PDO's if you're more comfortable with that.