In this post, I will show how to set up a Pie Chart using Highcharts, PHP, JSON and MySQL.

This example displays the percentage breakdown of web marketing efforts.


 

1. Create MySQL table

The SQL Script:

CREATE TABLE `web_marketing` (
`name` varchar(50) DEFAULT NULL,
`val` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `web_marketing` */

insert into `web_marketing`(`name`,`val`) values ('Direct Sales','20.00');
insert into `web_marketing`(`name`,`val`) values ('Search Engine Marketing','15.00');
insert into `web_marketing`(`name`,`val`) values ('PPC Advertising','15.00');
insert into `web_marketing`(`name`,`val`) values ('Website Marketing','10.00');
insert into `web_marketing`(`name`,`val`) values ('Blog Marketing','10.00');
insert into `web_marketing`(`name`,`val`) values ('Social Media Marketing','10.00');
insert into `web_marketing`(`name`,`val`) values ('Email Marketing','10.00');
insert into `web_marketing`(`name`,`val`) values ('Online PR','2.50');
insert into `web_marketing`(`name`,`val`) values ('Multimedia Marketing','2.50');
insert into `web_marketing`(`name`,`val`) values ('Mobile Marketing','2.50');
insert into `web_marketing`(`name`,`val`) values ('Display Advertising','2.50');

 

 

2. Retrieve data as JSON using PHP

<!--?php $con = mysql_connect("localhost","root","QWdF94"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("bfsdemo", $con); $result = mysql_query("SELECT name, val FROM web_marketing"); $rows = array(); while($r = mysql_fetch_array($result)) { $row[0] = $r[0]; $row[1] = $r[1]; array_push($rows,$row); } print json_encode($rows, JSON_NUMERIC_CHECK); mysql_close($con); ?--><?php
$con = <a href="http://www.php.net/mysql_connect">mysql_connect</a>("localhost","root","QWdF94");

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>("bfsdemo", $con);

$result = <a href="http://www.php.net/mysql_query">mysql_query</a>("SELECT name, val FROM web_marketing");

$rows = <a href="http://www.php.net/array">array</a>();
while($r = <a href="http://www.php.net/mysql_fetch_array">mysql_fetch_array</a>($result)) {
$row[0] = $r[0];
$row[1] = $r[1];
<a href="http://www.php.net/array_push">array_push</a>($rows,$row);
}

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

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

The JSON results should look like this:

[
["Direct Sales",20],
["Search Engine Marketing",15],
["PPC Advertising",15],
["Website Marketing",10],
["Blog Marketing",10],
["Social Media Marketing",10],
["Email Marketing",10],
["Online PR",2.5],
["Multimedia Marketing",2.5],
["Mobile Marketing",2.5],
["Display Advertising",2.5]
]

 

3. Set up chart options

Moving onto UI, set up the options for a pie chart. We’ll populate the series data later using JSON call.

var options = {
chart: {
renderTo: 'container',
plotBackgroundColor: null,
plotBorderWidth: null,
plotShadow: false
},
title: {
text: 'Web Sales & Marketing Efforts'
},
tooltip: {
formatter: function() {
return '<b>'+ this.point.name +'</b>: '+ this.percentage +' %';
}
},
plotOptions: {
pie: {
allowPointSelect: true,
cursor: 'pointer',
dataLabels: {
enabled: true,
color: '#000000',
connectorColor: '#000000',
formatter: function() {
return '<b>'+ this.point.name +'</b>: '+ this.percentage +' %';
}
}
}
},
series: [{
type: 'pie',
name: 'Browser share',
data: []
}]
}

 

4. Populate the chart with JSON data

$.getJSON("data.php", function(json) {
options.series[0].data = json;
chart = new Highcharts.Chart(options);
});

 

5. Put it all together

<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Highcharts Pie Chart</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
var options = {
chart: {
renderTo: 'container',
plotBackgroundColor: null,
plotBorderWidth: null,
plotShadow: false
},
title: {
text: 'Web Sales & Marketing Efforts'
},
tooltip: {
formatter: function() {
return '<b>'+ this.point.name +'</b>: '+ this.percentage +' %';
}
},
plotOptions: {
pie: {
allowPointSelect: true,
cursor: 'pointer',
dataLabels: {
enabled: true,
color: '#000000',
connectorColor: '#000000',
formatter: function() {
return '<b>'+ this.point.name +'</b>: '+ this.percentage +' %';
}
}
}
},
series: [{
type: 'pie',
name: 'Browser share',
data: []
}]
}

$.getJSON("data.php", function(json) {
options.series[0].data = json;
chart = new Highcharts.Chart(options);
});



});
</script>
<script src="http://code.highcharts.com/highcharts.js"></script>
<script src="http://code.highcharts.com/modules/exporting.js"></script>
</head>
<body>
<div id="container" style="min-width: 400px; height: 400px; margin: 0 auto"></div>
</body>
</html>