Building array and formatting JSON for Google Charting API

Question

I am working on a project where I am making use of the Google Charting API and I want to populate the chart using json to build the data table.

As a test I am trying to build a simple array before I try and do it with dynamic data from a database but I am having a problem getting the json in the correct format.

In the google documentation it says the json content should be in the following:

{
  "cols": [
        {"id":"","label":"Topping","pattern":"","type":"string"},
        {"id":"","label":"Slices","pattern":"","type":"number"}
      ],
  "rows": [
        {"c":[{"v":"Mushrooms","f":null},{"v":3,"f":null}]},
        {"c":[{"v":"Onions","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Olives","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Zucchini","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Pepperoni","f":null},{"v":2,"f":null}]}
      ]
}

I am calling a function which returns the json code.

Below is how the function is called

print json_encode(test());

and the test function is

function test()
{
    $array = array();
    $array['cols'][] = "20-01-13";
    $array['cols'][] = "21-01-13";
    $array['cols'][] = "22-01-13";
    $array['rows'][] = 22;
    $array['rows'][] = 26;
    $array['rows'][] = 12;

    return $array;
}

The javascript that generates the chart is as follows

<script>
            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: "loadGraph.php",
                 dataType:"json",
                 async: false
                }).responseText;

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

      // Instantiate and draw our chart, passing in some options.
      var chart = new google.visualization.PieChart(document.getElementById('lineGraph'));
      chart.draw(data, {width: 400, height: 240});
            }
        </script>

When I echo the json it is being returned in the following format

{"cols":["20-01-13","21-01-13","22-01-13"],"rows":[22,26,12]}

and if I try and use this for the datasetfor the google chart I get the following message

Cannot read property of '1' of undefined

I am only building a simple line chart which will just contain a date along the x axis and a count of how many times something happened on that date along the y axis.

How do I nee to build up the array to get it in the correct format for the google api chart.

Thanks for any help you can provide.

Solution 1

You need to specify the parameter type for cols. Refer to Google Charts JSON Format

Your PHP code should look similar to:

function test()
{
    $array['cols'][] = array('type' => 'string');
    $array['cols'][] = array('type' => 'string');
    $array['cols'][] = array('type' => 'string');

    $array['rows'][] = array('c' => array( array('v'=>'20-01-13'), array('v'=>22)) );
    $array['rows'][] = array('c' => array( array('v'=>'21-01-13'), array('v'=>26)));
    $array['rows'][] = array('c' => array( array('v'=>'22-01-13'), array('v'=>12)));

    return $array;
}

print json_encode(test());

Your json code would look more like:

{
  "cols": [
    {"type": "string"},
    {"type": "string"},
    {"type": "string"}
    ],
  "rows": [
    {"c":[{"v":"20-01-13"}, {"v":22} ]},
    {"c":[{"v":"21-01-13"}, {"v":26} ]},
    {"c":[{"v":"22-01-13"}, {"v":12} ]}
  ]
}

Solution 2

This may come a bit late but anyway, here's what worked for me (needed to create a barchart).

1. Get the server code to return the json string representing an array e.g. something like:

   [{
     "score": 12,
     "subject": "Computer Graphics"
   }, {
     "score": 65,
     "subject": "Entreprenuership"
   }, {
     "score": 82,
     "subject": "C++Programming"
   }]
   

2. Parse the string using eval() or JSON.parse() to get the JavaScript object.

   var chartData = JSON.parse(jsonStringFromServer);
   

3. Create a DataTable object as such:

   var dTable = new google.visualization.DataTable();
   

4. Add columns to the dataTable :

   dTable.addColumn('string','Subject'); 
   dTable.addColumn('number','Score'); 
   //more columns if needed...
   

5. Loop through the javascript array (chartData) and for each object in the array, add a new row to the dataTable:

   for(i=0;i<chartData.length;i++){
       var currentObj = chartData[i];
       dTable.addRow([currentObj.subject,currentObj.score]);
   }
   //set options for the chart e.g title, width etc
   // create the chart ...
   

Comments

[dlaliberte]

There is nothing obviously wrong with the data as you have shown it. The problem may be in how you use it, so you should provide a more complete example. Set up a jsfiddle that demonstrates the problem.

[Boardy]

@dlaliberte i've added more information

[dlaliberte]

The json being returned by your php code is obviously not the same as what you expect to output, and what the chart expects as data. So this is a problem with your php test() function that generates the wrong structure to start with. The error from google charts is rather confusing, but beside the point. Sorry I can't help you with the php code.

[Boardy]

Thanks, but I know what format the JSON needs to be in, I included that in the question, the problem is I am not sure how to create the array so when it is json encoded it is in the correct format

[Boardy]

No sorry, SO, didn't give me the notification that it was updated. I'll give it a try tonight after work and get back to you

[Boardy]

Thanks so much this does work. The only thing I don't understand is what the "c" and "v" means within the array.

[Fabi]

follow the reference to Google Charts JSON Format in the answer, it explains all the variables there

[Zafer]

There is a small problem in code. It should be like that: function test() { $array['cols'][] = array('type' => 'string'); $array['cols'][] = array('type' => 'number'); $array['rows'][] = array('c' => array( array('v'=>'20-01-13'), array('v'=>22))); $array['rows'][] = array('c' => array( array('v'=>'21-01-13'), array('v'=>26))); $array['rows'][] = array('c' => array( array('v'=>'22-01-13'), array('v'=>12))); return $array; }