AUTHORED BY
Andrew Cross
DATE
01/15/2015
WORD COUNT
678
REV
0
REFERENCE IMAGE Connecting a Google Chart to a MySQL database
NOTES
  1. Google Chart documentation isn't very thorough on using a database backend
  2. JSON format is used extensively in this method
SOCIAL REACH

Google Charts have seemingly taken over the world of online data visualization. Everyone from big-time content publishers to little technical blogs (like mine!) has turned to the convenient, easy to use, and shockingly complete charting package. I’ve been using these charts more and more often, so I decided to share some of the more esoteric portions of the Google Chart package that I’ve discovered. In this series of posts, I’ll be explaining how to utilize a database back-end to generate truly dynamic charts. You can find the second part of this series here.

Example Google Chart

Taking a quick step back, if you’re not familiar with Google Charts, they currently offer over 25 different types of generalized visualizations. That number includes familiar graphs such as bar, area, and line charts, but the platform also features several niche charts that are extremely useful in certain situations such as gauges, timelines, and maps. Staying true to the Google ethos, the developers have done a tremendous job documenting their efforts and offering workable examples that actually make their platform accessible and easy to implement. Beyond just “basic” usage, more advanced formatting options are also covered, albeit with slightly less clarity.

Despite the excellent documentation on tweaking the charts’ looks, there isn’t much infor on how to actually format and input the data. The page that covers the use of [Population] Server-Side Code is particularly sparse. This isn’t surprising, as I’d venture a guess that the majority of Google Chart implementations are static one-off pieces. For these cases, it makes perfect sense to simply hard-code the basis data straight into Javascript. For example, take a look at the pie chart below (taken straight from the example page) and the accompanying code that goes with it.


<script src="http://ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>
<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 data = google.visualization.arrayToDataTable([
[‘Task’, ‘Hours per Day’],
[‘Work’, 11],
[‘Eat’, 2],
[‘Commute’, 2],
[‘Watch TV’, 2],
[‘Sleep’, 7]
]);

var options = {
title: ‘My Daily Activities’
};

var chart = new google.visualization.PieChart(document.getElementById(‘piechart’));

chart.draw(data, options);
}
</script>

<div id="piechart" style="width: 620px; height: 500px;"></div>

DataTable Class

Notice how the variable data is an arrayToDataTable object that’s defined by an array? This is how a Google chart stores its data. Arrays are essentially converted to DataTable objects before being graphed, regardless of which particular type of graph is being used. Keep in mind, this static array that will later be dynamically generated from a MySQL database query.

Before I get into the explaining the dynamic mechanism, I should mention how conveniently flexible a Google Chart is. The platform allows a developer to construct DataTables in a number of different ways. The official documentation describes the method that was used above as simply ‘Javascipt’. Understandably, they recommend that this method be used when you’re looking to enter information manually. Moving forward, however, we’re going to use Javascript’s object literal notation, or JSON. It’s far more efficient to generate through code than the above, although the syntax is more…involved. JSON is essentially tailor-made for turning database data into a Javascript-readable table, which is exactly what we need.

Hand-Coded Javascript

var data = new google.visualization.DataTable();
  data.addColumn('string', 'Task');
  data.addColumn('number', 'Hours per Day');
  data.addRows([
    ['Work', 11],
    ['Eat', 2],
    ['Commute', 2],
    ['Watch TV', 2],
    ['Sleep', {v:7, f:'7.000'}]
  ]);

Object Literal Notation (JSON)

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'}]}
          ]
    }, 0.6);

Ok. We covered how a graph object is defined. We also touched on the DataTable class, and came to an understanding that using JSON is the easiest way feed a Google Chart database data. The tricky part is, how to generate the JSON. That’s covered in Part 2!

Part 1 | Part 2

Profile picture of Andrew standing at the Southern-most point in the United States.
Andrew Cross

Andrew is currently a mechanical R&D engineer for a medical imaging company. He enjoys good food, motivated people, and road biking. He has still not completely come to terms with the fact he will never play center field for the Kansas City Royals.