Create Google Charts using MySQL data

While trying to create my own Google charts using MySQL data, the closest solution I found was this in Google’s documentation on how to do something similar. The code used there is:

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

      // Create our data table out of JSON data loaded from server.
      var data = new google.visualization.DataTable(jsonData);

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

I disagree with the approach there because of one line in their code:

async: false

That bit goes against the whole idea of AJAX. AJAX loads a certain section of your page Asynchronously; the line of code above stops that from happening. Everything is loaded synchronously meaning that your browser will wait while your Google chart is being generated. The code though won’t work if you change it to async:true because the variable jsonData will still be null by the time var data = new google.visualization.DataTable(jsonData); runs.

Secondly, in the example, data is read from a file.

$string = file_get_contents("sampleData.json");
echo $string;

It is very unlikely you’ll load dynamic data from a file. How can we do this better?

Two things:

1. Your Json data has to be formatted in a particular way
2. The JavaScript shouldn’t use async: false

Here’s what I used:

1. RETRIEVING AND FORMATTING THE DATA

In my case, I was drawing a line chart. The data I’d retrieve from the database needed to be formatted to look like this.

{
cols: [{id: 'A', label: 'NEW A', type: 'string'},
{id: 'B', label: 'B-label', type: 'number'},
{id: 'C', label: 'C-label', type: 'date'}
],
rows: [{c:[{v: 'a'},
{v: 1.0, f: 'One'},
{v: new Date(2008, 1, 28, 0, 31, 26), f: '2/28/08 12:31 AM'}
]},
{c:[{v: 'b'},
{v: 2.0, f: 'Two'},
{v: new Date(2008, 2, 30, 0, 31, 26), f: '3/30/08 12:31 AM'}
]},
{c:[{v: 'c'},
{v: 3.0, f: 'Three'},
{v: new Date(2008, 3, 30, 0, 31, 26), f: '4/30/08 12:31 AM'}
]}
],
p: {foo: 'hello', bar: 'world!'}
}

That’s a JavaScript object describing a table with three columns and three rows. The data you retrieve has to be formatted into something similar. Using PHP:

$output_array = array();
			$output_array["cols"] = array (
                            array(
                                "label"=>"Your Y Axis Label",
                                "type"=>"number"),
                            array(
                                "label"=>"Your X Axis Label",
                                "type"=>"number")
                        );
                        $output_array["rows"] = array ();
//Add MySQL connect & all that other stuff that connects and retrieves Objects from your Db
                        foreach( $retrieved_values as $value) {
                             $output_array["rows"][] = array("c"=>array(array(
                            "v"=>(float)$value->y_axis_field_being_plotted,
                            "f"=>null
                            ),array(
                            "v"=>$value->x_axis_field,
                            "f"=>null
                            )));
                        }
echo json_encode( $output_array );
//NB: Without casting $value->y_axis_field_being_plotted to float (or int or whichever), 
//your output values will be escaped as Strings and graphing won't work. If you don't want to cast, 
//you could change echo json_encode( $output_array ) to echo json_encode( $output_array, JSON_NUMERIC_CHECK ) 
//but that only works for PHP >=5.3

UPDATE: I discovered that you could retrieve the array simply by using:

$output_array = array();
                        $output_array[] = array( $y_axis_label,$x_axis_label );
                        //Connect to database, retrieve Objects
			foreach ( $retrieved_values as $value) {
				$output_array[] = array ( date_format(date_create($value->x_axis_field),'d-m-Y') ,(float)$value->y_axis_field_being_plotted);
			}
                        echo json_encode( $output_array );

2. THE JAVASCRIPT

google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(DrawGraph);
                    function DrawGraph() {
                        jQuery.post( "yourPostURL.php", //If in WordPress, it'd be admin_url( 'admin-ajax.php')
                                {   dataValueOne : 'Any_info_to_send',
                                    dataValueTwo : 'More_info_to_send'
                                },
                                function( response ) {
                                    var respObj = JSON.parse(response);
                                    if ( 'undefined' !== typeof(respObj.error) ){
                                        jQuery('#IDofChart').html( respObj.error.message );//Display an error message if something went wrong. You can display a generic message instead of respObj.error.message
                                        return ;
                                    }
                                    var chartData =  google.visualization.arrayToDataTable( respObj );
                                    var chartOptions = {
                                        title: "Your Chart Title"
                                                };//You can add other chart options
                                    var YourGoogleChart = new google.visualization.LineChart(document.getElementById('IDofChart'));
                                    YourGoogleChart.draw( chartData, chartOptions );
                        });
                    }

This worked to create the line graph below: