How to import data from mysql table with Highchart in PHP
Hello Friends,
In this post we will learn to create
PHP page which imports data from mysql table and generate chart.
We will use PHP, JQuery, highchart
framework in this tutorial.
First of all create mysql table
names 'sales' in database 'Account'
* Sql Syntex
CREATE TABLE IF NOT EXISTS `sales` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`month` varchar(200) DEFAULT NULL,
`amount` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=118 ;
* Insert
Data in table 'sales'
INSERT INTO `sales` (`id`, `month`, `amount`) VALUES
(24, 'Apr', '15'),
(25, 'May', '40'),
(26, 'Jun', '26'),
(27, 'Jul', '31'),
(28, 'Aug', '39'),
(29, 'Sep', '25'),
(30, 'Oct', '27'),
(31, 'Nov', ' 32'),
(32, 'Dec', NULL);
Table with data will look like
 |
| `sales` |
create php or HTML file with name
index in root folder
* index.php
<head>
<meta name="Gopal Joshi"
content="Highchart with Mysql" />
<meta http-equiv="content-type"
content="text/html; charset=utf-8" />
<title>Highchart with Mysql
Database</title>
<script type="text/javascript"
src="js/jquery.min.js"></script>
<script type="text/javascript"
src="js/setup.js"></script>
<script type="text/javascript"
src="js/test.js"></script>
</head>
<body>
<script
src="js/highcharts.js"></script>
<div id="sales"
style="min-width: 310px; height: 400px; margin: 0
auto"></div>
</body>
Download jquery file required for
highchart Download
past required file to js folder in
root of our site.
* setup.js
var chart;
$(document).ready(function() {
var cursan = {
chart: {
renderTo: 'sales',
defaultSeriesType:
'area',
marginRight: 10,
marginBottom: 20
},
title: {
text: 'Highchart With
Mysql',
},
subtitle: {
text:
'www.spjoshis.blogspot.com',
},
xAxis: {
categories: ['Apr',
'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb', 'Mar']
},
yAxis: {
title: {
text: 'Average'
},
plotLines: [{
value: 0,
width: 1,
color: '#808080'
}]
},
tooltip: {
crosshairs: true,
shared:
true
},
legend: {
layout: 'vertical',
align: 'right',
verticalAlign: 'top',
x: -10,
y: 30,
borderWidth: 0
},
plotOptions: {
series: {
cursor: 'pointer',
marker: {
lineWidth: 1
}
}
},
series: [{
color:
Highcharts.getOptions().colors[2],
name: 'Test Colomn',
marker: {
fillColor: '#FFFFFF',
lineWidth: 3,
lineColor: null // inherit from series
},
dataLabels: {
enabled: true,
rotation: 0,
color: '#666666',
align: 'top',
x: -10,
y: -10,
style: {
fontSize: '9px',
fontFamily: 'Verdana, sans-serif',
textShadow: '0 0 0px black'
}
}
}],
}
//Fetch MySql Records
jQuery.get('js/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 = line[0] ;
amo=parseFloat(line[1].replace(',', ''));
if
(isNaN(amo)) {
amo = null;
}
traffic.push([
date,
amo
]);
});
} catch (e) { }
cursan.series[0].data =
traffic;
chart = new
Highcharts.Chart(cursan);
});
});
it will get data from mysql using
data.php file placed in js folder
* data.php
$con=mysql_connect('localhost','root','');
mysql_select_db("test", $con);
$result=mysql_query('select * from sales order by id');
while($row = mysql_fetch_array($result)) {
echo $row['month'] . "\t" . $row['amount'].
"\n";
}
that's it our chart is generated.
Here is Output
 |
| Output |
Highchart is free for non-commercial
use.
Download Source from Click
here
