Google Charts Image API Integration

Part 1 – Initial desires & decisions

A short while ago one of the clients I work with expressed a desire to display data on their website. The basic idea was to show weekly data over a yearly period. Nothing too complicated in terms of presentation. This information was to be pulled off a MySQL server. This meant that the method had to allow a dynamic set of data to be displayed.

I decided to use the Google Charts Tool to create my custom chart. It allows a significant amount of customisation and is very logical to integrate into your website. The data is displayed as an image, and this means that it is robust and cross-browser compatible.

Part 2 – Choose the chart style

First of all I chose the type of chart I wanted to modify. The line chart suited my needs. I wrote down the kind of data I was going to be dealing with. On my Y Axis would be a integer. On my X Axis would be two tiers of information, firstly the week in numbers, this would correlate with the integer, and secondly the months of the year, so that the week numbers can be placed in context.

Part 3 – Integrating the data

The data will be pulled out of an MySQL database. I am going to use some fake sample data (widget prices) in this example. Here is the SQL you can use in your own example:

CREATE TABLE IF NOT EXISTS `widget_prices` (
  `week` varchar(2) COLLATE latin1_general_ci NOT NULL,
  `price` varchar(5) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`week`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `widget_prices` (`week`, `price`) VALUES
('1', '62.32'),
('2', '52.45'),
('3', '52.20'),
('4', '51.28'),
('5', '52.20'),
('6', '53.20'),
('7', '51.70'),
('8', '52.61'),
('9', '56.48'),
('10', '56.48'),
('11', '58.08'),
('12', '58.52'),
('13', '57.75'),
('14', '58.32'),
('15', '59.95'),
('16', '59.45'),
('17', '59.45'),
('18', '60.04'),
('19', '55.37'),
('20', '55.13'),
('21', '55.99'),
('22', '55.99'),
('23', '57.08'),
('24', '57.55'),
('25', '57.55'),
('26', '54.05'),
('27', '56.45'),
('28', '56.85'),
('29', '57.45'),
('30', '57.45'),
('31', '53.45');

As you can see I have a table called ‘widget_prices’ and inside it contains two columns – price & week.

Part 4 – Connect to the server and gather the data

So the first operation I need to perform is to get this data from the database and manipulate it into something useful. In this instance that will be an array that contains all the data. So I state the database variables to allow a connection, please note it is best practice to keep these details in a seperate file, and to simply include them. For now here is the code:

//Enter your details - these are fake!
$db_host = 'localhost';
$db_database = 'test_db';
$db_user = 'testuser';
$db_password = 'test';

$db = mysql_connect($db_host, $db_user, $db_password);
mysql_select_db($db_database);

I am going to assume you have knowledge of how to connect to MySQL databases. A full explanation for those that don’t can be found at Tizag. After this initial set-up, I then want to query my MySQL table to get the data. I start off my beginning to write my primary function generateGoogleChart(), that will get the data, process it and eventually output it as a URL that will generate my chart.

The first part of the generateGoogleChart() function is written like this:

function generateGoogleChart()
{
    //Get the data from the database
    $sqlQuery = "SELECT week, price FROM widget_prices";
    $sqlResult = mysql_query($sqlQuery);
    while ($row = mysql_fetch_assoc($sqlResult)) {
        $priceResults[] = $row;
    }
}

This provides me an associative array – $priceResults – that now contains all the data I require for the chart. To see the data at this stage you can use PHP’s inbuilt function print_r() or you can use this alternative that outputs the array wrapped in HTML pre tags:

function displayArray($val)
{
    echo "<pre>";
    print_r($val);
    echo "</pre>";
    return;
}

Simply call displayArray($averageResult); to see all the data in the array and make sure everything is working as expected. You will see a formatted list of prices.

Part 5 – Beginning to build the chart

Next I need to start to gather information about our data and start to format it in a way that the Google Image Charts API expects. I want to know how many weeks worth of widget prices I have in my database. The easiest way to do this is to count the number of items in the array and set it to a variable:

$maxWeeks = count($priceResults);

This gives a value of 31. So I know that I have 31 weeks worth of prices. I can use this value to accurately control how I set-up the raw data for the chart when I begin to iterate over it. Data is provided to the chart via the chd parameter. The chd parameter takes a basic text string formatted as so:

chd=t:val,val,val|val,val,val...

The ‘t’ character represents plain text format, ‘val’ must be a floating point value (0-100). The pipe character (|) is used to seperate multiple series of data. I shall be using the pipe character later to seperate my week data. To set-up my chd values I use the following code:

//Start to compile the prices data
for ($row = 0; $row < $limit; $row++) {
	//Check for a value if one exists, add to $chd
    if(isset($priceResults[$row]['price']))
    {
        $chd .= $priceResults[$row]['price'];
    }
	//Check to see if row exceeds $maxWeeks
    if ($row < $maxWeeks) {
    	//It doesn't, so add a comma and add the price to array $scaleValues
        $chd .= ',';
        $scaleValues[] = $priceResults[$row]['price'];
    } else if ($row >= $maxWeeks && $row < ($limit - 1)) {
    	//Row exceeds, so add null value with comma
        $chd .= '_,';
    } else {
    	//Row exceeds and is last required value, so just add a null value
        $chd .= '_';
    }
}

This code in essence produces 52 values, all collected together with a comma (,) to seperate the values. The underscore character (_) represents a null value, so that is included whenever a price is not available. We know from earlier on that there will be 31 sets of prices, so the remaining 21 will all be underscores. The final part of the for loop adds an underscore without a comma to represent that the data set is completed. We want 52 sets of data so that the data is properly spaced along the X axis of the chart. If we did not add the null values, then only 31 sets of data would appear on the graph and would be evenly spaced along it. I set the $chd value to '' so that the values created via the for loop have a container to exist within.

You may have noticed the $scaleValues[] array in my example. I will explain this next.

Part 6 - Controlling the Y Axis

There may be peaks and troughs in my widgets data that I am unable to predict. So I can not set a fixed scale of 0 - 100 pence on my Y Axis. So I need a dynamic 'buffer' for my prices, that will always allow them to appear on my chart and importantly to never exceed my scale. I achieve this via the following code;

$YScaleMax = round(max($scaleValues)) + 5;
$YScaleMin = round(min($scaleValues)) - 5;

My scale 'buffer' is +/- 5. This data is contained in my array from earlier $scaleValues. I perform a calculation with some of PHP's inbuilt functions and store it as a variable. First of all I use the max/min functions to find those respective values in my array. I then round this value to a whole number to make my scale easier. It is then a case of adding/subtracting 5 from that value. I now have my Y Axis dynamic scale. Now onto the X Axis.

Part 7 - Setting up the X Axis

It is time to set-up my X Axis with the correct custom labels. In this instance it is the numbers of the week followed by a seperator value. This will mean that the the data points on the line chart match up with their respective week numbers. This data is set using the chxl parameter. All I need is a sequence of numbers, so I wrote a short function to provide that to me using the following code:

function generateSequence($min, $max, $seperator = "")
{
    $output = '';
    for ($i = $min; $i <= $max; $i++)
    {
        $output .= $i . $seperator;
    }
    return $output;
}

I can then call this function, $graphSequence = generateSequence(1, 52, "|"); and get all the week numbers for my year in a format that meets the API's requirements. If I echo $graphSequence then the result looks like this:

1|2|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18|19|20|21|22|23|24|25|26|27|28|29|30|31|32|33|34|35|36|37|38|39|40|41|42|43|44|45|46|47|48|49|50|51|52|

So that is all the labels for my X Axis sorted out, and I can feel confident that a widget price on my chart will line up with with it's relevent week number.

Part 8 - Google API Parameters

So I have dealt with accessing my data and manipulating it into a format that meets the image charts API parameters. There are more parameters that need to be considered to fully form the line chart. For a full explanation of each one I recommend visiting Google Image Charts API and looking at the examples. These really allow you to customise your chart in terms of presentation and style. I have kept my line chart quite clean looking, as you will see in the live example.

I will provide the next bit of PHP code that sets some more of the parameters so that the chart can begin to take form:

//Set the Google Image Chart API parameters
$cht = 'lc';//Set the chart type
$chxl = '1:|' . $graphSequence . '2:|Price+(pence/widget)|3:|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sept|Oct|Nov|Dec||4:|Weeks+and+Months';//custom axis labels
$chxp = '2,50|4,50';//Axis Label Positions
$chxr = '0,' . $YScaleMin . ',' . $YScaleMax . '|1,1,52|3,1,12|5,' . $YScaleMin . ',' . $YScaleMax . '';//Axis Range
$chxs = '0,252525,10,1,l,676767|1,252525,10,0,l,676767|2,03619d,13|4,03619d,13|5,252525,10,1,l,676767';//Axis Label Styles
$chxtc = '0,5|1,5|5,5';//Axis Tick Mark Styles
$chxt = 'y,x,y,x,x,r';//Visible Axes
$chs = '918x300';//Chart Size in px
$chco = 'FF0000';//Series Colours
$chds = '' . $YScaleMin . ',' . $YScaleMax . '';//Custom Scaling
$chg = '-1,-1,1,5';//Grid lines
$chls = '1';//line styles
$chm = 'o,252525,0,-1,3';//Shape Markers

As you can see I have set-up a number of variables that I then assign values and data to. The first $cht sets the chart type, in my example I have used 'lc' which is the reference code for line chart. The next variable $chxl uses the $grapSequence variable we set previously. $chxr makes use of the scale 'min' & 'max values I set in Part 6.

The only other parameter that needs some brief explaining here is $chds. Google defines chds as - 'A set of one or more minimum and maximum permitted values for each data series, separated by commas' - what this means is the scale that the line chart uses to display the data is seperate from the physical scale you see written on the Y axis. I already set the Y-Scale in the $chxr and I want the $chds value to match up to this scale, so '60' on my chart matches '60' on the Y axis. I re-use the variables I set early, $YScaleMin and $YScaleMax, thus allowing the data on the graph to match the corresponding value on the Y axis.

Part 9 - Build the final URL & output the image chart code

//Build the URL
$googleUrl = 'http://chart.apis.google.com/chart?';
$rawUrl = $googleUrl . 'cht=' . $cht . '&chxl=' . $chxl . '&chxp=' . $chxp . '&chxr=' . $chxr . '&chxs=' . $chxs . '&chxtc=' . $chxtc . '&chxt=' . $chxt . '&chs=' . $chs . '&chco=' . $chco . '&chd=t:' . $chd . '&chds=' . $chds . '&chg=' . $chg . '&chls=' . $chls . '&chm=' . $chm;

$output = $rawUrl;

return $output;

This is the final part of the generateGoogleChart(); function. The process here is to take all the data that has been set-up in this function and chain it together to form a URL. This URL can then be referenced within your HTML. The $googleUrl variable is the base Google Chart URL that all requests must use. As you can see it ends with a '?'. What follows is a GET method, as the various parameters are sent to Google as a query (formed via rawUrl, and our chart is returned as an image.

Please note: URLs are limited to 2K in length, so a POST method may need to be used. This is not covered in this tutorial

Finally the function returns the URL to be used later to display the chart. I will explain this in the next part.

Part 10 - Display the chart in your HTML

Now all the hard work has been completed. It is a matter of calling the generateGoogleChart() function and outputting the correct HTML to display the chart.

$chart = generateGoogleChart();

$html = '<div id="chart">';
$html .= '<img src="' . $chart . '" alt="Widget weekly average price chart for 2011" />';
$html .= '</div>';

echo $html;

The $chart variable is used to store the data that the generateGoogleChart() outputs. Another variable $html is created to store the HTML code that will be output when a user runs this script. I have kept the HTML to a minimum for my example, this would work successfully as a snippet included within another PHP template file.