- Use a JSON validator to make sure the data is formatted properly for Google Charts
- MySQL is the backend discussed, but any database will work
Continuing on from Part 1 of the series, this post will discuss the formulation of PHP code that will be capable of dynamically updating a Google Chart. The code will first query a MySQL database, then return those query results as Google-Chart-compliant JSON. As JSON, the data can be used by any type of Google Chart to create interesting and unique data visualizations.
Google Chart’s JSON Format
If you’re not already familiar with it, the JSON format that Google Charts uses is easy to conceptualize. It’s basically just a heavily-structured 2D table. Take a look at the example below taken straight from the official documentation.
{
"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}]}
]
}
As you can see, the first col array defines a series of data labels. Similar to a database table, each column has attributes associated with it. In this case, those attributes include an id, label, pattern, and type. After the labels are defined, data are populated row by row by further nesting of arrays. The example above busy, but’s fortunately more general than we need it to be at the moment. Blank and null attributes are unnecessary here, so I’ve trimmed the above code down to something more useful below. Note that I left the id attribute in the definition of the data labels. This is a requirement, even if the id is blank.
{
"cols": [
{"id":"","label":"Topping","type":"string"},
{"id":"","label":"Slices","type":"number"}
],
"rows": [
{"c":[{"v":"Mushrooms"},{"v":3}]},
{"c":[{"v":"Onions"},{"v":1}]},
{"c":[{"v":"Olives"},{"v":1}]},
{"c":[{"v":"Zucchini"},{"v":1}]},
{"c":[{"v":"Pepperoni"},{"v":2}]}
]
}
MySQL Example Data
Let’s continue the code’s development by establishing an example database we can reference. We’ll make a table called pizza that includes the same topping types and slices that are used in the official example. To save time, use the SQL code below to duplicate my table if you’d like to work along. I’m using MySQL, but the code should be similar for any SQL-based database.
CREATE TABLE pizza (
id INT NOT NULL AUTO_INCREMENT,
topping VARCHAR(30) NOT NULL,
slices INT,
PRIMARY KEY (id)
);
INSERT INTO pizza (topping, slices) VALUES
(‘Mushrooms’,3),(‘Onions’,1),(‘Olives’,1),(‘Zucchini’,1),(‘Pepperoni’,2);
Using PHP to Generate JSON
Now we can dig into the PHP that does the actual querying and JSON generating! There are many ways to open a database connection with PHP, but I’ll stick with the newest mysqli method. For this example, I’m running my server on localhost without a password, and the database is named ‘master’. Once the connection is established, the simply query is executed, and the result is stored in the $result variable before the database connection is closed.
$con = mysqli_connect(‘localhost’,’root’,”,’master’);
if (!$con) {
die(‘Could not connect: ‘ . mysqli_error($con));
}
$qry = "SELECT topping, slices FROM `pizza`"
$result = mysqli_query($con,$qry);
mysqli_close($con);
Here’s where this gets fun. Since the query above was stringently written to return only the topping and slices columns, I’m not concerned with defining the columns procedurally. In a sense, I’ll hard-code the data labels. This is accomplished by establishing an associative array with a key named cols, then nesting additional associative arrays for each of the data’s columns.
$table = array();
$table[‘cols’] = array(
//Labels for the chart, these represent the column titles
array(‘id’ => ”, ‘label’ => ‘Topping’, ‘type’ => ‘string’),
array(‘id’ => ”, ‘label’ => ‘Slices’, ‘type’ => ‘number’)
);
All we’re doing is creating a php array, then using a tightly defined set of associative keys. The same basic method is followed to place the queried data into JSON rows, but unlike the data labels (column headers), we want a procedural method for filling in these rows. This can be accomplished by iterating over the $result variable, and extracting the necessary information where needed. Before tackling the iteration, take a look at the JSON reformatted for clarity below. Try to identify the “pattern” in the JSON before moving on to the next section.
"rows": [
{"c": [
{"v":"Mushrooms"},
{"v":3}
]},
{"c": [
{"v":"Onions"},
{"v":1}
]},
{"c": [
{"v":"Olives"},
{"v":1}
]}
]
Iterating Nested Arrays
You’ll see that each nested array is keyed by the letter ‘c’. I can’t say for certain what the developers were thinking when they came up with this key, but I tend to think the key is short for ‘cell’. Notably, each “cell” contains additional associative arrays with ‘v’, or “value”, keys. Each “cell”, or row, has as many ‘v’ values in it as there are columns. Read that again if it didn’t make sense, as an understanding of this structure is critical for developing your own code. For this case, a foreach loop iterates over each row within $result, then builds a temporary array of ‘v’ values. Once all ‘v’ values are added to the temporary array, it’s added to a ‘c’ array.
$rows = array();
foreach($result as $row){
$temp = array();
//Values
$temp[] = array(‘v’ => (string) $row[‘topping’]);
$temp[] = array(‘v’ => (float) $row[‘slices’]);
$rows[] = array(‘c’ => $temp);
}
After the above code runs, we’ll have obtained two useful variables: $table and $rows. As you might have already ascertained, $rows is a portion of the larger $table array, so it’s quickly added in. The next row is where the magic happens, as the php function json_encode is used to convert the array to the JSON format we’re looking for! Don’t forget to lastly echo the JSON, or the PHP code won’t return anything when called.
$result->free();
$table[‘rows’] = $rows;
$jsonTable = json_encode($table, true);
echo $jsonTable;
JSON Validation
A quick word here about working with JSON. As you’ve no doubt gathered, it can be an unwieldy format that’s prone to including mistakes. It’s always a good idea to make use of several useful tools when going through this process. One recommendation is to validate the JSON to make sure that it’s built correctly. If you’re missing a bracket somewhere, or a comma is out of place, you can be assured that your Google Chart won’t like it and nothing will be displayed. Make use of any of the online tools that allow you to copy/paste in your JSON and get immediate feedback in terms of its compliance.
Another tip is to visually validate your JSON. Use pre tags and echo the json_encode results while you’re developing the code. This makes the JSON far more legible. Just don’t forget to comment it out when you’ve completed your debugging!
//echo ‘<pre>’;
//echo json_encode($table, JSON_PRETTY_PRINT);
//echo ‘</pre>’;
Google Chart Javascript Initialization
The querying and formatting has now all been completed, so how does the JSON get shoe-horned into the Google Charts? First, let’s make sure that the PHP developed above is located in the proper place. Since PHP is a server-side code, it needs to reside on your server. In this example, I’ve named my code getData.php and placed it in the same web directory as the webpage that includes my Google Chart. There are a number of ways to retrieve getData.php’s data to use with your chart, but the most sensical is to pull it in asynchronously with AJAX. If you’ve followed along since Part 1 of this series, the only task left is to substitute some code. When defining the data variable in Google Chart’s javascript definition, substituted in the following code.
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);
If you have any questions, or have trouble putting this all together, feel free to ask away. I’d encourage you to examine Part 1 for a working example if you get stuck from these directions. Stay tuned, too, because several more posts that will build off of the concepts covered here are already in the pipeline! Thanks for reading.