I like Highcharts. There are many JavaScript-based charting components out there but I particularly enjoyed using this JavaScript library.

In this post, I want to step you through how to use Highcharts with PHP and MySQL.

1. Download Highcharts and jQuery libraries.

I prefer jQuery as the prerequisite library but it supports Prototype and Mootools as well.
Highcharts – http://www.highcharts.com/download
jQuery – http://docs.jquery.com/Downloading_jQuery

At this point, I usually create a /js folder under my project folder. In this folder, you want to include jQuery js file, Highcharts js file and Highcharts themes folder.

 

Once you have the libraries, you can create the presentation php

2. Create a presentation php

Let’s call this index.php. First, add references to JavaScript files

<script type="text/javascript" src="js/jquery-1.7.1.min.js" ></script>
<script type="text/javascript" src="js/highcharts.js" ></script>
<script type="text/javascript" src="js/themes/gray.js"></script>

Then, include Highcharts definitions

<script type="text/javascript">
var chart;
$(document).ready(function() {
var options = {
chart: {
renderTo: 'container',
defaultSeriesType: 'line',
marginRight: 130,
marginBottom: 25
},
title: {
text: 'Hourly Visits',
x: -20 //center
},
subtitle: {
text: '',
x: -20
},
xAxis: {
type: 'datetime',
tickInterval: 3600 * 1000, // one hour
tickWidth: 0,
gridLineWidth: 1,
labels: {
align: 'center',
x: -3,
y: 20,
formatter: function() {
return Highcharts.dateFormat('%l%p', this.value);
}
}
},
yAxis: {
title: {
text: 'Visits'
},
plotLines: [{
value: 0,
width: 1,
color: '#808080'
}]
},
tooltip: {
formatter: function() {
return Highcharts.dateFormat('%l%p', this.x-(1000*3600)) +'-'+ Highcharts.dateFormat('%l%p', this.x) +': <b>'+ this.y + '</b>';
}
},
legend: {
layout: 'vertical',
align: 'right',
verticalAlign: 'top',
x: -10,
y: 100,
borderWidth: 0
},
series: [{
name: 'Count'
}]
}
// Load data asynchronously using jQuery. On success, add the data
// to the options and initiate the chart.
// This data is obtained by exporting a GA custom report to TSV.
// http://api.jquery.com/jQuery.get/
jQuery.get('data.php', null, function(tsv) {
var lines = [];
traffic = [];
try {
// split the data return into lines and parse them
tsv = tsv.split(/\n/g);
jQuery.each(tsv, function(i, line) {
line = line.split(/\t/);
date = Date.parse(line[0] +' UTC');
traffic.push([
date,
parseInt(line[1].replace(',', ''), 10)
]);
});
} catch (e) {  }
options.series[0].data = traffic;
chart = new Highcharts.Chart(options);
});
});
</script>

3. Create tables and populate with data

Use your favorite MySQL editor like PhpMyAdmin or SQLyog to run the sample data sql script. If you already have data in your database, take note what columns you want to connect Highcharts to.

My tables looks something like this:

4. Create a data php

Now create data.php. This page will read the table data and display it as tab separated values.

<!--?php $con = mysql_connect("localhost","user",""); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("highcharts", $con); $result = mysql_query("SELECT * FROM highcharts_php"); while($row = mysql_fetch_array($result)) { echo $row['timespan'] . "\t" . $row['visits']. "\n"; } mysql_close($con); ?--><?php
$con = <a href="http://www.php.net/mysql_connect">mysql_connect</a>("localhost","user","");

if (!$con) {
<a href="http://www.php.net/die">die</a>('Could not connect: ' . <a href="http://www.php.net/mysql_error">mysql_error</a>());
}

<a href="http://www.php.net/mysql_select_db">mysql_select_db</a>("highcharts", $con);

$result = <a href="http://www.php.net/mysql_query">mysql_query</a>("SELECT * FROM highcharts_php");

while($row = <a href="http://www.php.net/mysql_fetch_array">mysql_fetch_array</a>($result)) {
echo $row['timespan'] . "\t" . $row['visits']. "\n";
}

<a href="http://www.php.net/mysql_close">mysql_close</a>($con);
?>

The result should look like this:

Friday, July 1, 2011 00:00:00 1274
Friday, July 1, 2011 01:00:00 1288
Friday, July 1, 2011 02:00:00 1275
Friday, July 1, 2011 03:00:00 1270
Friday, July 1, 2011 04:00:00 1279
Friday, July 1, 2011 05:00:00 1268
Friday, July 1, 2011 06:00:00 1267
Friday, July 1, 2011 07:00:00 1271
Friday, July 1, 2011 08:00:00 1287
Friday, July 1, 2011 09:00:00 1276
Friday, July 1, 2011 10:00:00 1278
Friday, July 1, 2011 11:00:00 1272
Friday, July 1, 2011 12:00:00 1288
Friday, July 1, 2011 13:00:00 1264
Friday, July 1, 2011 14:00:00 1280
Friday, July 1, 2011 15:00:00 1277
Friday, July 1, 2011 16:00:00 1278
Friday, July 1, 2011 17:00:00 1279
Friday, July 1, 2011 18:00:00 1277
Friday, July 1, 2011 19:00:00 1270
Friday, July 1, 2011 20:00:00 1264
Friday, July 1, 2011 21:00:00 1278
Friday, July 1, 2011 22:00:00 1284
Friday, July 1, 2011 23:00:00 1272

You can generate the same results using web frame such as CodeIgniter or CakePHP. I am showing you the most basic way to generate data from MySQL.

5. Putting it all together

Here’s how to connect Highcharts with data.php. In index.php, edit the following sections of the code to point to data.php:

[javasccript]
jQuery.get(‘data.php’, null, function(tsv) {
var lines = [];
traffic = [];
[/javascript]

Voila!

You can download the source code from this tutorial in zip format (code, MySQL sql script and Highchart and jQuery javascripts) and also view live demo of this example.

Next time, I will show you how you can pass parameter values to make the chart more interactive.

UPDATE: another way of grabbing data from database is to use JSON. Full example here.