DawnP DawnP - 3 months ago 16
JSON Question

Google Table Chart from php created Json

I am trying to create a Google Table Chart with information I have exported from my database with php and encoded to json. (momJson.php) I am getting a blank page and I am not sure why. Any help would be very much appreciated!!

PS I can make a table straight from the mysql database with php, but I want to use the same information to make multiple charts, so I thought I would start with an easy one.

<html>
<head>
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script type="text/javascript">

// Load the Visualization API and the piechart package.
google.charts.load('current', {'packages':['table']});

// Set a callback to run when the Google Visualization API is loaded.
google.charts.setOnLoadCallback(drawTable);

function drawTable() {

var jsonData = $.ajax({
url: "momJson.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 table = new google.visualization.Table(document.getElementById('table_div'));

table.draw(data, {showRowNumber: true, width: '100%', height: '100%'});
}
</script>
</head>

<body>
<!--Div that will hold the pie chart-->
<div id="table_div"></div>
</body>
</html>


JSON Data (I did validate at JSONLint.com)

[{"schoolid":"10007","prizeprogram":"APP","datereceived":"20160415","numberstudents":"292","participatingstudents":"69","studentresponse":"0.23","numberbooklets":"91","numbernames":"762","numberorders":"43","orderresponse":"0.05","tov":"1606.52","numbersubscriptions":"64","saleweek":"160122","vpn":"2.10","vps":"5.50","schoolcat":"7","lowgrade":"K","highgrade":"5","schooltype":"E","schoolname":"Iaeger Elementary School","schoolcity":"Iaeger","schoolstate":"WV","dateupdated":"2016-06-06 11:57:32","testcode":"A","mdrcode":"1\r"},{"schoolid":"10013","prizeprogram":"RSR","datereceived":"20160208","numberstudents":"256","participatingstudents":"58","studentresponse":"0.22","numberbooklets":"78","numbernames":"638","numberorders":"54","orderresponse":"0.08","tov":"2111.23","numbersubscriptions":"80","saleweek":"160108","vpn":"3.30","vps":"8.24","schoolcat":"7","lowgrade":"PK","highgrade":"5","schooltype":"E","schoolname":"Mount Hope Elementary School","schoolcity":"Mount Hope","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"","mdrcode":"1\r"},{"schoolid":"10027","prizeprogram":"RBR","datereceived":"20150914","numberstudents":"315","participatingstudents":"49","studentresponse":"0.15","numberbooklets":"57","numbernames":"540","numberorders":"52","orderresponse":"0.09","tov":"1716.61","numbersubscriptions":"73","saleweek":"150904","vpn":"3.17","vps":"5.44","schoolcat":"7","lowgrade":"PK","highgrade":"8","schooltype":"E","schoolname":"Webster Springs Elementary School","schoolcity":"Webster Springs","schoolstate":"WV","dateupdated":"2016-06-06 11:57:32","testcode":"","mdrcode":"1\r"},{"schoolid":"10051","prizeprogram":"WCR","datereceived":"20150923","numberstudents":"240","participatingstudents":"41","studentresponse":"0.17","numberbooklets":"53","numbernames":"459","numberorders":"56","orderresponse":"0.12","tov":"1745.10","numbersubscriptions":"69","saleweek":"150911","vpn":"3.80","vps":"7.27","schoolcat":"7","lowgrade":"PK","highgrade":"4","schooltype":"E","schoolname":"Sutton Elementary School","schoolcity":"Sutton","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"","mdrcode":"1\r"},{"schoolid":"10052","prizeprogram":"RSR","datereceived":"20160217","numberstudents":"675","participatingstudents":"212","studentresponse":"0.31","numberbooklets":"241","numbernames":"2338","numberorders":"287","orderresponse":"0.12","tov":"10415.68","numbersubscriptions":"407","saleweek":"160129","vpn":"4.45","vps":"15.43","schoolcat":"7","lowgrade":"K","highgrade":"5","schooltype":"E","schoolname":"Daniels Elementary School","schoolcity":"Daniels","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"5","mdrcode":"1\r"},{"schoolid":"10053","prizeprogram":"RSWB","datereceived":"20160205","numberstudents":"351","participatingstudents":"204","studentresponse":"0.58","numberbooklets":"231","numbernames":"2250","numberorders":"391","orderresponse":"0.17","tov":"14056.06","numbersubscriptions":"545","saleweek":"160115","vpn":"6.24","vps":"40.04","schoolcat":"7","lowgrade":"K","highgrade":"5","schooltype":"E","schoolname":"Arthur I Boreman Elementary School","schoolcity":"Middlebourne","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"","mdrcode":"1\r"},{"schoolid":"10057","prizeprogram":"RSWS","datereceived":"20160302","numberstudents":"265","participatingstudents":"64","studentresponse":"0.24","numberbooklets":"84","numbernames":"707","numberorders":"106","orderresponse":"0.14","tov":"3932.80","numbersubscriptions":"158","saleweek":"160205","vpn":"5.56","vps":"14.84","schoolcat":"7","lowgrade":"K","highgrade":"5","schooltype":"E","schoolname":"Eastbrook Elementary School","schoolcity":"Winfield","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"","mdrcode":"1\r"},{"schoolid":"10065","prizeprogram":"RBR","datereceived":"20150921","numberstudents":"210","participatingstudents":"26","studentresponse":"0.12","numberbooklets":"34","numbernames":"290","numberorders":"42","orderresponse":"0.14","tov":"1539.27","numbersubscriptions":"60","saleweek":"150911","vpn":"5.30","vps":"7.32","schoolcat":"7","lowgrade":"PK","highgrade":"5","schooltype":"E","schoolname":"Salt Rock Elementary School","schoolcity":"Salt Rock","schoolstate":"WV","dateupdated":"2016-06-06 11:57:32","testcode":"","mdrcode":"1\r"},{"schoolid":"10066","prizeprogram":"WCR","datereceived":"20150828","numberstudents":"220","participatingstudents":"67","studentresponse":"0.30","numberbooklets":"94","numbernames":"743","numberorders":"66","orderresponse":"0.08","tov":"2371.85","numbersubscriptions":"96","saleweek":"150814","vpn":"3.19","vps":"10.78","schoolcat":"7","lowgrade":"PK","highgrade":"5","schooltype":"E","schoolname":"Cox Landing Elementary School","schoolcity":"Lesage","schoolstate":"WV","dateupdated":"2016-06-06 11:57:34","testcode":"","mdrcode":"1\r"},{"schoolid":"10095","prizeprogram":"APP","datereceived":"20160223","numberstudents":"300","participatingstudents":"50","studentresponse":"0.16","numberbooklets":"63","numbernames":"552","numberorders":"49","orderresponse":"0.08","tov":"1857.94","numbersubscriptions":"77","saleweek":"160122","vpn":"3.36","vps":"6.19","schoolcat":"7","lowgrade":"PK","highgrade":"6","schooltype":"E","schoolname":"Beale Elementary School","schoolcity":"Gallipolis Ferry","schoolstate":"WV","dateupdated":"2016-06-06 11:57:32","testcode":"A","mdrcode":"1\r"},{"schoolid":"10104","prizeprogram":"WCR","datereceived":"20150929","numberstudents":"80","participatingstudents":"35","studentresponse":"0.43","numberbooklets":"45","numbernames":"386","numberorders":"48","orderresponse":"0.12","tov":"1708.43","numbersubscriptions":"67","saleweek":"150918","vpn":"4.42","vps":"21.35","schoolcat":"7","lowgrade":"PK","highgrade":"5","schooltype":"E","schoolname":"Hillsboro Elementary School","schoolcity":"Hillsboro","schoolstate":"WV","dateupdated":"2016-06-06 11:57:34","testcode":"","mdrcode":"1\r"},{"schoolid":"10143","prizeprogram":"WCR","datereceived":"20150915","numberstudents":"400","participatingstudents":"172","studentresponse":"0.43","numberbooklets":"206","numbernames":"1899","numberorders":"193","orderresponse":"0.10","tov":"6286.01","numbersubscriptions":"251","saleweek":"150821","vpn":"3.31","vps":"15.71","schoolcat":"7","lowgrade":"PK","highgrade":"2","schooltype":"E","schoolname":"Moorefield Elementary School","schoolcity":"Moorefield","schoolstate":"WV","dateupdated":"2016-06-06 11:57:34","testcode":"","mdrcode":"1\r"},{"schoolid":"10159","prizeprogram":"RSR","datereceived":"20160203","numberstudents":"165","participatingstudents":"68","studentresponse":"0.41","numberbooklets":"89","numbernames":"756","numberorders":"48","orderresponse":"0.06","tov":"1588.47","numbersubscriptions":"65","saleweek":"160108","vpn":"2.10","vps":"9.62","schoolcat":"7","lowgrade":"K","highgrade":"5","schooltype":"E","schoolname":"Prichard Elementary School","schoolcity":"Prichard","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"","mdrcode":"1\r"}]

Answer

in order to create a DataTable directly from the jsonData,
it must be in a format google understands

following is an example of the standard format,
found in the examples under the reference for the DataTable constructor method

var data = new google.visualization.DataTable({
  cols: [
    {id: 'task', label: 'Task', type: 'string'},
    {id: 'hours', label: 'Hours per Day', type: 'number'}
  ],
  rows: [
    {c:[{v: 'Work'}, {v: 11}]},
    {c:[{v: 'Eat'}, {v: 2}]},
    {c:[{v: 'Commute'}, {v: 2}]},
    {c:[{v: 'Watch TV'}, {v:2}]},
    {c:[{v: 'Sleep'}, {v:7, f:'7.000'}]}
  ]
});

if the jsonData is not in this format, then it must be manipulated accordingly

there are several ways to add columns and rows to a DataTable

in the following working snippet,
the keys from the first object {} in the jsonData are used to create the columns

also, recommend not using async: false

google.charts.load('current', {
  callback: function () {

    // get json
    $.ajax({
      url: "momJson.php",
      dataType:"json"
    }).done(function (jsonData) {
      // success
      drawTable(jsonData);
    }).fail(function (jq, text) {
      // failure
      //console.log(text);

      // included here for example purposes
      drawTable([{"schoolid":"10007","prizeprogram":"APP","datereceived":"20160415","numberstudents":"292","participatingstudents":"69","studentresponse":"0.23","numberbooklets":"91","numbernames":"762","numberorders":"43","orderresponse":"0.05","tov":"1606.52","numbersubscriptions":"64","saleweek":"160122","vpn":"2.10","vps":"5.50","schoolcat":"7","lowgrade":"K","highgrade":"5","schooltype":"E","schoolname":"Iaeger Elementary School","schoolcity":"Iaeger","schoolstate":"WV","dateupdated":"2016-06-06 11:57:32","testcode":"A","mdrcode":"1\r"},{"schoolid":"10013","prizeprogram":"RSR","datereceived":"20160208","numberstudents":"256","participatingstudents":"58","studentresponse":"0.22","numberbooklets":"78","numbernames":"638","numberorders":"54","orderresponse":"0.08","tov":"2111.23","numbersubscriptions":"80","saleweek":"160108","vpn":"3.30","vps":"8.24","schoolcat":"7","lowgrade":"PK","highgrade":"5","schooltype":"E","schoolname":"Mount Hope Elementary School","schoolcity":"Mount Hope","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"","mdrcode":"1\r"},{"schoolid":"10027","prizeprogram":"RBR","datereceived":"20150914","numberstudents":"315","participatingstudents":"49","studentresponse":"0.15","numberbooklets":"57","numbernames":"540","numberorders":"52","orderresponse":"0.09","tov":"1716.61","numbersubscriptions":"73","saleweek":"150904","vpn":"3.17","vps":"5.44","schoolcat":"7","lowgrade":"PK","highgrade":"8","schooltype":"E","schoolname":"Webster Springs Elementary School","schoolcity":"Webster Springs","schoolstate":"WV","dateupdated":"2016-06-06 11:57:32","testcode":"","mdrcode":"1\r"},{"schoolid":"10051","prizeprogram":"WCR","datereceived":"20150923","numberstudents":"240","participatingstudents":"41","studentresponse":"0.17","numberbooklets":"53","numbernames":"459","numberorders":"56","orderresponse":"0.12","tov":"1745.10","numbersubscriptions":"69","saleweek":"150911","vpn":"3.80","vps":"7.27","schoolcat":"7","lowgrade":"PK","highgrade":"4","schooltype":"E","schoolname":"Sutton Elementary School","schoolcity":"Sutton","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"","mdrcode":"1\r"},{"schoolid":"10052","prizeprogram":"RSR","datereceived":"20160217","numberstudents":"675","participatingstudents":"212","studentresponse":"0.31","numberbooklets":"241","numbernames":"2338","numberorders":"287","orderresponse":"0.12","tov":"10415.68","numbersubscriptions":"407","saleweek":"160129","vpn":"4.45","vps":"15.43","schoolcat":"7","lowgrade":"K","highgrade":"5","schooltype":"E","schoolname":"Daniels Elementary School","schoolcity":"Daniels","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"5","mdrcode":"1\r"},{"schoolid":"10053","prizeprogram":"RSWB","datereceived":"20160205","numberstudents":"351","participatingstudents":"204","studentresponse":"0.58","numberbooklets":"231","numbernames":"2250","numberorders":"391","orderresponse":"0.17","tov":"14056.06","numbersubscriptions":"545","saleweek":"160115","vpn":"6.24","vps":"40.04","schoolcat":"7","lowgrade":"K","highgrade":"5","schooltype":"E","schoolname":"Arthur I Boreman Elementary School","schoolcity":"Middlebourne","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"","mdrcode":"1\r"},{"schoolid":"10057","prizeprogram":"RSWS","datereceived":"20160302","numberstudents":"265","participatingstudents":"64","studentresponse":"0.24","numberbooklets":"84","numbernames":"707","numberorders":"106","orderresponse":"0.14","tov":"3932.80","numbersubscriptions":"158","saleweek":"160205","vpn":"5.56","vps":"14.84","schoolcat":"7","lowgrade":"K","highgrade":"5","schooltype":"E","schoolname":"Eastbrook Elementary School","schoolcity":"Winfield","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"","mdrcode":"1\r"},{"schoolid":"10065","prizeprogram":"RBR","datereceived":"20150921","numberstudents":"210","participatingstudents":"26","studentresponse":"0.12","numberbooklets":"34","numbernames":"290","numberorders":"42","orderresponse":"0.14","tov":"1539.27","numbersubscriptions":"60","saleweek":"150911","vpn":"5.30","vps":"7.32","schoolcat":"7","lowgrade":"PK","highgrade":"5","schooltype":"E","schoolname":"Salt Rock Elementary School","schoolcity":"Salt Rock","schoolstate":"WV","dateupdated":"2016-06-06 11:57:32","testcode":"","mdrcode":"1\r"},{"schoolid":"10066","prizeprogram":"WCR","datereceived":"20150828","numberstudents":"220","participatingstudents":"67","studentresponse":"0.30","numberbooklets":"94","numbernames":"743","numberorders":"66","orderresponse":"0.08","tov":"2371.85","numbersubscriptions":"96","saleweek":"150814","vpn":"3.19","vps":"10.78","schoolcat":"7","lowgrade":"PK","highgrade":"5","schooltype":"E","schoolname":"Cox Landing Elementary School","schoolcity":"Lesage","schoolstate":"WV","dateupdated":"2016-06-06 11:57:34","testcode":"","mdrcode":"1\r"},{"schoolid":"10095","prizeprogram":"APP","datereceived":"20160223","numberstudents":"300","participatingstudents":"50","studentresponse":"0.16","numberbooklets":"63","numbernames":"552","numberorders":"49","orderresponse":"0.08","tov":"1857.94","numbersubscriptions":"77","saleweek":"160122","vpn":"3.36","vps":"6.19","schoolcat":"7","lowgrade":"PK","highgrade":"6","schooltype":"E","schoolname":"Beale Elementary School","schoolcity":"Gallipolis Ferry","schoolstate":"WV","dateupdated":"2016-06-06 11:57:32","testcode":"A","mdrcode":"1\r"},{"schoolid":"10104","prizeprogram":"WCR","datereceived":"20150929","numberstudents":"80","participatingstudents":"35","studentresponse":"0.43","numberbooklets":"45","numbernames":"386","numberorders":"48","orderresponse":"0.12","tov":"1708.43","numbersubscriptions":"67","saleweek":"150918","vpn":"4.42","vps":"21.35","schoolcat":"7","lowgrade":"PK","highgrade":"5","schooltype":"E","schoolname":"Hillsboro Elementary School","schoolcity":"Hillsboro","schoolstate":"WV","dateupdated":"2016-06-06 11:57:34","testcode":"","mdrcode":"1\r"},{"schoolid":"10143","prizeprogram":"WCR","datereceived":"20150915","numberstudents":"400","participatingstudents":"172","studentresponse":"0.43","numberbooklets":"206","numbernames":"1899","numberorders":"193","orderresponse":"0.10","tov":"6286.01","numbersubscriptions":"251","saleweek":"150821","vpn":"3.31","vps":"15.71","schoolcat":"7","lowgrade":"PK","highgrade":"2","schooltype":"E","schoolname":"Moorefield Elementary School","schoolcity":"Moorefield","schoolstate":"WV","dateupdated":"2016-06-06 11:57:34","testcode":"","mdrcode":"1\r"},{"schoolid":"10159","prizeprogram":"RSR","datereceived":"20160203","numberstudents":"165","participatingstudents":"68","studentresponse":"0.41","numberbooklets":"89","numbernames":"756","numberorders":"48","orderresponse":"0.06","tov":"1588.47","numbersubscriptions":"65","saleweek":"160108","vpn":"2.10","vps":"9.62","schoolcat":"7","lowgrade":"K","highgrade":"5","schooltype":"E","schoolname":"Prichard Elementary School","schoolcity":"Prichard","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"","mdrcode":"1\r"}]);
    });

  },
  packages:['table']
});

function drawTable(jsonData) {
  var data = new google.visualization.DataTable();

  jsonData.forEach(function (row, index) {
    // load columns
    if (index === 0) {
      Object.keys(row).forEach(function (key) {
        data.addColumn({
          label: key,
          type: 'string'
        });
      });
    }

    // load rows
    var dataRow = [];
    // load value for each column
    for (var i = 0; i < data.getNumberOfColumns(); i++) {
      // ensure each row has value for key
      if (row.hasOwnProperty(data.getColumnLabel(i))) {
        dataRow.push(row[data.getColumnLabel(i)]);
      } else {
        dataRow.push(null);
      }
    }
    data.addRow(dataRow);
  });

  var table = new google.visualization.Table(document.getElementById('table_div'));

  table.draw(data, {showRowNumber: true, width: '100%', height: '100%'});
}
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="table_div"></div>