Parsing JSON array into Google Charts DataTable

Question

I've done quite a bit of searching and have found solutions for issues similar to my problem but nothing that has worked so far. Any help is appreciated.

I am attempting to create a Google Line Chart from data I am querying from my database and then parsing to JSON. My query and JSON looks good but when I insert it into the DataTable the web page is blank.

This the JSON output I have:

[["date","percentage"],["0000-00-00 00:00:00",28.18],["2015-01-06 06:00:01",93.65],["2015-01-07 06:00:01",85.9],["2015-01-08 06:08:43",89.25],["2015-01-09 06:08:42",99.26],["2015-01-10 14:50:37",99.48],["2015-02-06 06:00:01",93.88],["2015-02-07 06:00:01",89.15],["2015-02-08 06:08:51",89.55],["2015-02-09 06:09:25",98.94],["2015-02-09 17:26:50",98.94],["2015-02-10 06:10:55",99.06],["2015-03-07 06:11:32",85.7],["2015-03-08 06:07:58",89.86],["2015-03-10 06:11:04",99.06],["2015-04-06 14:19:26",82.17],["2015-04-07 06:11:47",85.8],["2015-04-08 06:10:01",89.76],["2015-04-10 06:10:53",100],["2015-05-06 16:25:01",91.21],["2015-05-07 06:11:12",85.7],["2015-05-08 06:08:53",89.25],["2015-05-09 22:35:40",97.77],["2015-05-10 06:10:39",99.17],["2015-05-10 21:28:01",98.85],["2015-06-06 06:00:02",90.84],["2015-06-07 06:11:05",85.9],["2015-06-09 06:09:32",97.88],["2015-06-10 06:10:55",99.79],["2015-07-06 06:00:01",91.33],["2015-07-07 06:10:35",85.09],["2015-07-08 06:09:36",89.45],["2015-07-09 06:08:55",98.2],["2015-07-10 06:12:44",5.94],["2015-08-06 06:00:01",91.58],["2015-08-08 06:09:03",89.05],["2015-08-09 06:09:23",97.03],["2015-08-09 15:54:27",97.67],["2015-09-06 06:00:01",90.96],["2015-09-07 06:12:33",84.99],["2015-09-08 06:08:54",89.15],["2015-09-09 06:12:48",99.26],["2015-09-09 14:29:39",95.02],["2015-10-06 06:00:01",91.09],["2015-10-07 06:13:13",89.35],["2015-10-08 06:08:42",89.25],["2015-10-09 06:09:40",99.47],["2015-10-09 17:14:20",99.47],["2015-11-05 06:00:01",97.41],["2015-11-06 06:00:01",85.47],["2015-11-07 06:12:17",89.45],["2015-11-08 06:10:45",89.45],["2015-12-05 06:00:01",96.7],["2015-12-06 06:00:01",90.72],["2015-12-07 06:12:22",88.74],["2015-12-09 06:09:39",99.58]]

When I insert this into the JSON Validator at https://jsonformatter.curiousconcept.com it says it is valid JSON and when I hard code it in everything seems to work..

Here is my code:

  <?php
    $dbhost="localhost";
    $dblogin="root";
    $dbpwd="password!";
    $dbname="qaDB";

    $db =  mysql_connect($dbhost,$dblogin,$dbpwd);
    mysql_select_db($dbname);    


    $SQLString = "SELECT date, percentage FROM reports WHERE RTYPE_ID=1";       

    $result = mysql_query($SQLString);    
    $num = mysql_num_rows($result);   

# set heading   
    $data[0] = array('date','percentage');      
    for ($i=1; $i<($num+1); $i++)
    {
        $data[$i] = array(substr(mysql_result($result, $i-1, "date"), 0, 20),
            (float) mysql_result($result, $i-1, "percentage"));
    }   
    echo json_encode($data);
    mysql_close($db);
?>

Here is the page to create the Google line chart.

 <html>
    <head>
        <title>JSON Chart Test</title>
        <!-- Load jQuery -->
        <script language="javascript" type="text/javascript" 
        src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js">
    </script>
    <!-- Load Google JSAPI -->
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
        google.load("visualization", "1", { packages: ["corechart"] });
        google.setOnLoadCallback(drawChart);

    function drawChart() {
     // var newWin = window.open();
        var jsonData = $.ajax({
            url: "legacyDashboardJSON.php",
            dataType: "json",
            async: false
        }).responseText;


//var newStr = jsonData.substring(1, jsonData .length-1);
 //newWin.document.write(jsonData);
// var parsedData = JSON.parse(newStr);

//var obj = JSON.stringify(newStr);


var data = google.visualization.DataTable(jsonData);

var options = {
    title: 'Stats'
};

var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
chart.draw(data, options);
}

</script>
</head>
<body>
    <div id="chart_div">

    </div>
</body>
</html>

Thanks!

Post the answer as an anwer and accept the answer, this will help reviews and other users. — Petter Friberg
– Petter Friberg, Commented Oct 27, 2015 at 16:04

Dylan Pearson · Accepted Answer · 2015-10-28 01:30:00Z

UPDATE Found the solution.

Here's the code:

<?php



$DB_NAME = 'qaDB';


$DB_HOST = 'localhost';


$DB_USER = 'root';
$DB_PASS = '!';





  $mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);

  if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
  }


  $result = $mysqli->query('SELECT date,percentage FROM reports');



  $rows = array();
  $table = array();
  $table['cols'] = array(


    array('label' => 'date', 'type' => 'string'),
    array('label' => 'percentage', 'type' => 'number')

);
    foreach($result as $r) {

      $temp = array();


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


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

$table['rows'] = $rows;

$jsonTable = json_encode($table);
echo $jsonTable;

mysql_close($mysqli);
?>

<html>
  <head>
    <!--Load the Ajax API-->
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
    <script type="text/javascript">

    // Load the Visualization API and the piechart package.
    google.load('visualization', '1', {'packages':['corechart']});

    // Set a callback to run when the Google Visualization API is loaded.
    google.setOnLoadCallback(drawChart);
    function drawChart() {

        var jsonData = $.ajax({
            url: "legacyDashboardJSON.php",
            dataType: "json",
            async: false
        }).responseText;

      // Create our data table out of JSON data loaded from server.
      var data = new google.visualization.DataTable(jsonData);
      var options = {
           title: 'Metrics',
          width: 800,
          height: 600
        };

      // Instantiate and draw our chart, passing in some options.
      // Do not forget to check your div ID
      var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
      chart.draw(data, options);
    }
    </script>
  </head>

  <body>
    <!--this is the div that will hold the pie chart-->
    <div id="chart_div"></div>
  </body>
</html>

Cruiser · Accepted Answer · 2015-10-27 15:09:34Z

0

You need to provide a success function in your .ajax call that will execute when the data returns. Anything that needs the data should be placed inside this function, or passed from within the success function to another function. You could also use .done() on the end of your .ajax call and place the rest of your script in there.

answered Oct 27, 2015 at 15:09

Cruiser

1,6162 gold badges16 silver badges20 bronze badges

Collectives™ on Stack Overflow

Parsing JSON array into Google Charts DataTable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related