Create Dynamic Chart Using Mysql and Highcharts
Highcharts
is one of the best chart solution to create web based attractive chart. Highcharts has wide range of charts and options to customize. In this tutorial we will show step by step how to create realtime chart using highcharts. We will get data from database and update charts accordingly. Its very easy and short steps. We will use Highcharts, PHP, MYSQL database, jQuery in this tutorial.
Lets start by downloading highcharts JS from official site. It also offers you to build customized JavaScript as per your requirement.
index.php
includes
-- database.php
JS
-- jquery.min.js
-- highcharts.min.js
CSS
-- style.css
We are creating chart of companies monthly sales. For which, data of purchase has been stored date wise in table tbl_account.
|
1
2
3
4
5
6
|
CREATE TABLE IF NOT EXISTS `tbl_account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`purchase_date` datetime NOT NULL,
`total_purchase` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
|
Insert data Queries
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
INSERT INTO `wp_rnd`.`tbl_account` (`id`, `purchase_date`, `total_purchase`) VALUES (NULL, '2016-01-02 00:00:00', '90'), (NULL, '2016-01-02 00:00:00', '7');
INSERT INTO `wp_rnd`.`tbl_account` (`id`, `purchase_date`, `total_purchase`) VALUES (NULL, '2016-01-03 00:00:00', '14'), (NULL, '2016-01-02 00:00:00', '104');
INSERT INTO `wp_rnd`.`tbl_account` (`id`, `purchase_date`, `total_purchase`) VALUES (NULL, '2016-01-04 00:00:00', '02'), (NULL, '2016-01-02 00:00:00', '70');
INSERT INTO `wp_rnd`.`tbl_account` (`id`, `purchase_date`, `total_purchase`) VALUES (NULL, '2016-01-05 00:00:00', '37'), (NULL, '2016-01-02 00:00:00', '64');
INSERT INTO `wp_rnd`.`tbl_account` (`id`, `purchase_date`, `total_purchase`) VALUES (NULL, '2016-01-06 00:00:00', '50'), (NULL, '2016-01-02 00:00:00', '50');
INSERT INTO `wp_rnd`.`tbl_account` (`id`, `purchase_date`, `total_purchase`) VALUES (NULL, '2016-01-07 00:00:00', '22'), (NULL, '2016-01-02 00:00:00', '7');
INSERT INTO `wp_rnd`.`tbl_account` (`id`, `purchase_date`, `total_purchase`) VALUES (NULL, '2016-01-08 00:00:00', '82'), (NULL, '2016-01-02 00:00:00', '21');
|
database.php
Manage database connection. We have used mysqli extension.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
<?php
$server = '<Servcer name>'; // localhost
$db_user = '<Database User>'; // DB user
$db_password = '<Database Password>'; // DB password
$db_name = '<Database Name>'; // DB Name
$con = mysqli_connect($server,$db_user,$db_password,$db_name);
// Check connection
if (mysqli_connect_errno()){
echo "Failed to connect to database: " . mysqli_connect_error();
}
?>
|
index.php
Includes html and chart initialization and fetching data from mysql and updating chart. We have used column chart you can change it by changing type parameter. Refer (http://www.highcharts.com/demo) for more charts.
categories: – convert php array to javaScript array and updating xAxis
data: – convert php array to javaScript array and updating yAxis
Full Source
|
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
53
54
55
|
<?php
require_once('includes/database.php');
$stmt = mysqli_prepare($con, "SELECT date_format(purchase_date, '%d-%b-%y') as pDate, sum(total_purchase) as pAmount FROM tbl_account GROUP BY pDate ASC");
$result = array('day' => array(), 'amount' => array());
if ($stmt) {
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $day, $amount);
while (mysqli_stmt_fetch($stmt)) {
$result['day'][] = $day;
$result['amount'][] = (int)$amount;
}
mysqli_stmt_close($stmt);
}
?>
<body>
<div id="div-chart"></div>
<script src="includes/js/jquery.min.js"></script>
<script src="includes/js/highcharts.js"></script>
<script>
$(function () {
$('#div-chart').highcharts({
chart: {
type: 'column'
},
title: {
text: 'Average Purchase'
},
subtitle: {
text: 'Source: sgeek.org'
},
xAxis: {
categories: <?php echo json_encode($result['day']) ?>,
crosshair: true
},
yAxis: {
min: 0,
title: {
text: 'Amount (Millions)'
}
},
plotOptions: {
column: {
pointPadding: 0.2,
borderWidth: 0
}
},
series: [{
name: 'Purchase',
data: <?php echo json_encode($result['amount']) ?>
}]
});
});
</script>
</body>
|
Result
Renato
Dear!
I really enjoyed your post and am wanting to learn to work with this type of Graphical javascritp. Can I create a local database on my in MYSQL or need a server?
Gopal Joshi
Thank you @Renato,
You do not need server for it. You can create database in local as well.
Renato Lazaro Chirefa
I tried your example, but the screen only returns blank without infirm information. I would like to help me in some way via remote access TeamViewer?
Fritz
Hi,
I got the same result as Renato. Any help ?
After I used diff. highcharts.js and jquery.min.js I got a black rectangle at the top of my browser window.
I used diff browser, Opera and Firefox . Which is the right version for highcharts.js and jquery.min.js ?
Best regard Fritz
Gopal Joshi
Please share code you have tried on jsfiddle.net or you can send me code you have tried on . I will glad to help you.
Albert
Say, does the array “day” and “amount”, “know” what are they are called for, I mean, if I change “day” for “year”, does it only display the year? I´m trying to apply in a MVC way for Codeigniter, is kinda fuzzy since I´m no expert. Would you mind doing a tutorial for Codeigniter, or maybe is asking too much :P.
Gopal Joshi
@Albert, Yes you can change “day” to “year” but you also need to change variable name in JavaScript as well. I’ve use this data only for demo purpose you can change it as per your requirement. Will post tutorial in CoreIgniter in short time.
Col
This article looks like what I need. I am learning and want to get Dates and temperatures from MYSQL and send it to Highcharts.
I will spend a week or two and see if I can do it.
Thanks for posting this tutorial
KimG
Thank you for the nice tutorial, I’m trying to create the pie chart following the same instruction. it worked but the tooltip doesn’t appear. Can you help please.?
Gopal Joshi
Hi,
Please share your source code of chart at https://pastebin.com/
Kelvin
Hello Sir,
I blog quite often and I genuinely thank you.
This article has truly peaked my interest. I’m going to take a note
of your site and keep checking for new details about once per week.
I subscribed to your RSS feed too.
Abdullah
Hello Sir, I need your help the screen is showing blank when i run it
Gopal Joshi
I think it’s due to JavaScript errors. Please check with browser console and comment here which errors you are showing there.
faisal
How create 2 or 5 series in highchart?