How to create Google Chart friendly JSON arrays from MySQL?

Question

I think I'm close to completing the passing of MySQL data to Google Charts through JSON/AJAX. I am able to output a JSON string in the correct format but it is not outputting any SQL data. I've searched everywhere for a solution with no results. Anyone see what is missing from the code?

JSON output

{"cols":[{"id":"","label":"projid","type":"string"},{"id":"","label":"hours","type":"number"}],"rows":[{"c":[{"v":""},{"v":0}]},{"c":[{"v":""},{"v":0}]},{"c":[{"v":""},{"v":0}]},{"c":[{"v":""},{"v":0}]},{"c":[{"v":""},{"v":0}]},{"c":[{"v":""},{"v":0}]},{"c":[{"v":""},{"v":0}]},{"c":[{"v":""},{"v":0}]},{"c":[{"v":""},{"v":0}]}]}

PHP->JSON

<?php
// -----> Query MySQL and parse into JSON below. <------

// write your SQL query here (you may use parameters from $_GET or $_POST if you need them)

require_once ("Includes/session.php");
require_once ("Includes/simplecms-config.php");
require_once ("Includes/connectDB.php");

$recId = null;
$projid = null;
$hours = null;

        $recId = $_GET['id'];
        $projid = $_GET['projid'];
        $hours = $_GET['hours'];
        $query = "SELECT projid, hours FROM hours WHERE id = ?";
        $statement = $databaseConnection->prepare($query);
        $statement->bind_param('d', $recId);
        $statement->execute();
        $results = $statement->get_result();

  $rows = array();
  $table = array();
  $table['cols'] = array(
    array('id' => "",'label' => 'projid', 'type' => 'string'),
    array('id' => "",'label' => 'hours', 'type' => 'number')
);


    /* Extract the information from $result */
    while ($r = $results->fetch_assoc()) {
      $temp = array();

      // The following line will be used to slice the Pie chart
      $temp[] = array('v' => (string) $r['projid']); 

      // Values of each slice
      $temp[] = array('v' => (int) $r['hours']); 
      $rows[] = array('c' => $temp);
    }

$table['rows'] = $rows;

// convert data into JSON format
$jsonTable = json_encode($table);
echo $jsonTable;

?>

I assume from your code that you are using PDO's for your database I/O, is that correct, or are you using another system that looks similar to PDO? — asgallant
– asgallant, Commented Aug 6, 2013 at 17:56

Universal · Accepted Answer · 2013-08-08 14:21:32Z

The following code returned the correct array for Google Charts. Google Charts - JSON Data

<?php 

// -----> Query MySQL and parse into JSON below. <------

require_once  ("Includes/connectDB.php");

$result = $databaseConnection->query("SELECT projid, hours FROM alloc_hours");      
    $table = array();
    $table['cols'] = array(
    array('id' => "", 'label' => 'projid', 'pattern' => "", 'type' => 'string'),
    array('id' => "", 'label' => 'hours', 'pattern' => "", 'type' => 'number')
    );
    $rows = array();
    while ($nt = $result->fetch_assoc())
    {
    $temp = array();
    $temp[] = array('v' => $nt['projid'], 'f' =>NULL);
    $temp[] = array('v' => $nt['hours'], 'f' =>NULL);
    $rows[] = array('c' => $temp);
    }
    $table['rows'] = $rows;
    $jsonTable = json_encode($table);
    echo $jsonTable;
?>

Array

{"cols":[{"id":"","label":"projid","pattern":"","type":"string"},{"id":"","label":"hours","pattern":"","type":"number"}],"rows":[{"c":[{"v":"2","f":null},{"v":"8","f":null}]},{"c":[{"v":"1","f":null},{"v":"6","f":null}]},{"c":[{"v":"3","f":null},{"v":"20","f":null}]},{"c":[{"v":"2","f":null},{"v":"10","f":null}]},{"c":[{"v":"4","f":null},{"v":"5","f":null}]},{"c":[{"v":"1","f":null},{"v":"30","f":null}]}]}

asgallant · Accepted Answer · 2013-08-07 16:20:04Z

0

Try replacing this line:

$statement->store_result();

with:

$results = $statement->get_result();

and replace the foreach loop with a while loop:

while ($r = $results->fetch_assoc()) {
    $temp = array();

    // The following line will be used to slice the Pie chart

    $temp[] = array('v' => (string) $r['projid']);

    // Values of the each slice

    $temp[] = array('v' => (int) $r['hours']);
    $rows[] = array('c' => $temp);
}

That should get the query to return results. You don't need the lines:

$statement->bind_result($projid, $hours);
$statement->fetch();

edited Aug 7, 2013 at 16:20

answered Aug 6, 2013 at 23:05

asgallant

26.4k6 gold badges75 silver badges91 bronze badges

3 Comments

Universal Over a year ago

The updates you suggested output: {"cols":[{"id":"","label":"projid","type":"string"},{"id":"","label":"hours","type":"number"}],"rows":[]} What would be causing the query data to not be passed to the array?

asgallant Over a year ago

It might be due to a typo - I missed the "$" that should be in front of $results->fetch_assoc(). Edited my answer above with the fix.

Universal Over a year ago

I did notice that the "$" was missing before testing your solution. I updated the question's code to incorporate your suggestions. Are there any further errors that cause the output to not include the SQL data? Would you suggest a different approach for passing MySQLi queries to JSON?

Collectives™ on Stack Overflow

How to create Google Chart friendly JSON arrays from MySQL?

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related