In my previous post “Using Highcharts with PHP and MySQL”, the database table results were output as tab-separated values.

Since JSON is more widely used data format, it makes sense to provide sample code that uses JSON.

Additionally, this post will show Highcharts with multiple series.

 

1. Database table

I’ve included the sql file to create the table and populate with sample data in the code sample package.

 

2. JSON Data

In data.php, we want the following output:

[{
"name": "Revenue",
"data": [23987, 24784, 25899, 25569, 25897, 25668, 24114, 23899, 24987, 25111, 25899, 23221]
}, {
"name": "Overhead",
"data": [21990, 22365, 21987, 22369, 22558, 22987, 23521, 23003, 22756, 23112, 22987, 22897]
}]

This requires a JSON encoded array of two arrays each containing series information. In this example, the series are Revenue and Overhead.

First, you need to get the Revenue data.

$sth = mysql_query("SELECT revenue FROM projections_sample");
$rows = array();
$rows['name'] = 'Revenue';
while($r = mysql_fetch_array($sth)) {
$rows['data'][] = $r['revenue'];
}

Then, grab the Overhead data.

$sth = mysql_query("SELECT overhead FROM projections_sample");
$rows1 = array();
$rows1['name'] = 'Overhead';
while($rr = mysql_fetch_assoc($sth)) {
$rows1['data'][] = $rr['overhead'];
}

Finally, put them in a master array and output the JSON encoded string

$result = array();
array_push($result,$rows);
array_push($result,$rows1);
print json_encode($result, JSON_NUMERIC_CHECK);

Full data.php code

<!--?php $con = mysql_connect("localhost","root",""); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("highcharts", $con); $sth = mysql_query("SELECT revenue FROM projections_sample"); $rows = array(); $rows['name'] = 'Revenue'; while($r = mysql_fetch_array($sth)) { $rows['data'][] = $r['revenue']; } $sth = mysql_query("SELECT overhead FROM projections_sample"); $rows1 = array(); $rows1['name'] = 'Overhead'; while($rr = mysql_fetch_assoc($sth)) { $rows1['data'][] = $rr['overhead']; } $result = array(); array_push($result,$rows); array_push($result,$rows1); print json_encode($result, JSON_NUMERIC_CHECK); mysql_close($con); ?--><?php
$con = <a href="http://www.php.net/mysql_connect">mysql_connect</a>("localhost","root","");

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);

$sth = <a href="http://www.php.net/mysql_query">mysql_query</a>("SELECT revenue FROM projections_sample");
$rows = <a href="http://www.php.net/array">array</a>();
$rows['name'] = 'Revenue';
while($r = <a href="http://www.php.net/mysql_fetch_array">mysql_fetch_array</a>($sth)) {
$rows['data'][] = $r['revenue'];
}

$sth = <a href="http://www.php.net/mysql_query">mysql_query</a>("SELECT overhead FROM projections_sample");
$rows1 = <a href="http://www.php.net/array">array</a>();
$rows1['name'] = 'Overhead';
while($rr = <a href="http://www.php.net/mysql_fetch_assoc">mysql_fetch_assoc</a>($sth)) {
$rows1['data'][] = $rr['overhead'];
}

$result = <a href="http://www.php.net/array">array</a>();
<a href="http://www.php.net/array_push">array_push</a>($result,$rows);
<a href="http://www.php.net/array_push">array_push</a>($result,$rows1);

print <a href="http://www.php.net/json_encode">json_encode</a>($result, JSON_NUMERIC_CHECK);

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

 

3. Presentation PHP

The part that connects Highcharts to JSON back-end data is as follows:

$(document).ready(function() {
$.getJSON("data.php", function(json) {
chart = new Highcharts.Chart({
series: json

Full code


<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Highcharts Example</title>

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<script type="text/javascript">
$(function () {
var chart;
$(document).ready(function() {
$.getJSON("data.php", function(json) {

chart = new Highcharts.Chart({
chart: {
renderTo: 'container',
type: 'line',
marginRight: 130,
marginBottom: 25
},
title: {
text: 'Revenue vs. Overhead',
x: -20 //center
},
subtitle: {
text: '',
x: -20
},
xAxis: {
categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
},
yAxis: {
title: {
text: 'Amount'
},
plotLines: [{
value: 0,
width: 1,
color: '#808080'
}]
},
tooltip: {
formatter: function() {
return '<b>'+ this.series.name +'</b><br/>'+
this.x +': '+ this.y;
}
},
legend: {
layout: 'vertical',
align: 'right',
verticalAlign: 'top',
x: -10,
y: 100,
borderWidth: 0
},
series: json
});
});

});

});
</script>
</head>
<body>
<script src="http://code.highcharts.com/highcharts.js"></script>
<script src="http://code.highcharts.com/modules/exporting.js"></script>

<div id="container" style="min-width: 400px; height: 400px; margin: 0 auto"></div>

</body>
</html>

 

4. Tips and Tricks

As I was testing the code, I noticed that my numeric values had double quotes around them and Highcharts would not render the line chart.

[{
"name": "Revenue",
"data": ["23987", "24784", "25899", "25569", "25897", "25668", "24114", "23899", "24987", "25111", "25899", "23221"]
}, {
"name": "Overhead",
"data": ["21990", "22365", "21987", "22369", "22558", "22987", "23521", "23003", "22756", "23112", "22987", "22897"]
}]

I tracked the problem to json_encode function. Apparently, this is a known issue in PHP and can be resolved by using the JSON_NUMERIC_CHECK flag.

json_encode($result, JSON_NUMERIC_CHECK);