Creating Column Chart With Data from a Database
We can find many libaries to draw a chart in our website, one of them is highcharts. It offers an easy way of adding interactive charts to our web site which is written in pure JavaScript. Many developer implement charts in their web site by using highcharts library.
Besides highchart supports many type of charts, it also can be used freely and is compatible with various browser (all modern mobile and dekstop browsers including the iPhone/iPad and Internet Explorer from version 6).
In this post, we are goin to create an example of column chart about user's browser statistics as show by figure.1 below :
As we see in Fig.1, the chart which shown is representative value of each data to browsers : Chrome, Firefox, Safari and Opera.
Steps to create a column chart
Follow the steps below to create a column chart using library highcharts, PHP, Database MySQL on Bootstrap layout :1. Create Database
Give name to database as dbgraph, where it contain three fields and data as shown by script sql below :CREATE TABLE IF NOT EXISTS `browser` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`browsername` varchar(30) NOT NULL,
`total` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
INSERT INTO `browser` (`id`, `browsername`, `total`) VALUES
(1, 'Chrome', 700),
(2, 'Firefox', 450),
(3, 'Safari', 220),
(4, 'Opera', 180);2. Create file connection.php
This file to make an connection establishment to MySQL database. We use localhost as MySQL Server, root as username and no password as password that will be used by our script.<?php
$con=mysqli_connect("localhost","root","","dbgraph");
?>3. Call library highcharts.js
Surely you have downloaded the library highcharts,js and put it in your website project. In this project, we put the library in folder assets/js/highcharts.js. So we call it like script below :<script src="assets/js/highcharts.js"></script>4. Set chart properties and retrieve data from database
In this step-4, we set the title for chart, id unique to rendered later in tag body (html), label to x Axis and y Axis. We also set the type of chart and call data from database to displayed it in the column chart.<script>
var chart1;
$(document).ready(function() {
chart1 = new Highcharts.Chart({
chart: {
renderTo: 'mygraph',
type: 'column'
},
title: {
text: 'Internet Browser Statistics '
},
xAxis: {
categories: ['Browser']
},
yAxis: {
title: {
text: 'Total Browser Usage'
}
},
series:
[
<?php
include "connection.php";
$sql = "SELECT browsername FROM browser";
$query = mysqli_query( $con, $sql ) or die(mysqli_error());
while( $temp = mysqli_fetch_array( $query ) )
{
$trendbrowser=$temp['browsername'];
$sql_total = "SELECT total FROM browser WHERE browsername='$trendbrowser'";
$query_total = mysqli_query($con,$sql_total ) or die(mysql_error());
while( $data = mysqli_fetch_array( $query_total ) )
{
$total = $data['total'];
}
?>
{
name: '<?php echo $trendbrowser; ?>',
data: [<?php echo $total; ?>]
},
<?php
} ?>
]
});
});
</script>5. Call id unique
Place id unique that we have created on the step-4 (on part of renderTo) between tag . Because we use Bootstrap and we want to display chart inside bootstrap class panel, the script shown as follow :<div class="container" style="margin-top:20px">
<div class="col-md-7">
<div class="panel panel-primary">
<div class="panel-heading">The Graph of Browser Trends January 2015</div>
<div class="panel-body">
<div id ="mygraph"></div>
</div>
</div>
</div>
</div>Complete Source Code
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="author" content="ilmu-detil.blogspot.com">
<title>Bootstrap Graph Using Highcharts </title>
<!-- Bagian css -->
<link rel="stylesheet" href="assets/css/bootstrap.min.css">
<link rel="stylesheet" href="assets/css/ilmudetil.css">
<script src="assets/js/jquery-1.10.1.min.js"></script>
<script src="assets/js/highcharts.js"></script>
<script>
var chart1;
$(document).ready(function() {
chart1 = new Highcharts.Chart({
chart: {
renderTo: 'mygraph',
type: 'column'
},
title: {
text: 'Internet Browser Statistics '
},
xAxis: {
categories: ['Browser']
},
yAxis: {
title: {
text: 'Total Browser Usage'
}
},
series:
[
<?php
include "connection.php";
$sql = "SELECT browsername FROM browser";
$query = mysqli_query( $con, $sql ) or die(mysqli_error());
while( $temp = mysqli_fetch_array( $query ) )
{
$trendbrowser=$temp['browsername'];
$sql_total = "SELECT total FROM browser WHERE browsername='$trendbrowser'";
$query_total = mysqli_query($con,$sql_total ) or die(mysql_error());
while( $data = mysqli_fetch_array( $query_total ) )
{
$total = $data['total'];
}
?>
{
name: '<?php echo $trendbrowser; ?>',
data: [<?php echo $total; ?>]
},
<?php
} ?>
]
});
});
</script>
</head>
<body>
<nav class="navbar navbar-default navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
<span class="icon-bar"></span><span class="icon-bar"></span><span class="icon-bar"></span>
</button>
<a class="navbar-brand" href="index.html">
Pusat Ilmu Secara Detil</a>
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav navbar-left">
<li class="clr1 active"><a href="index.html">Home</a></li>
<li class="clr2"><a href="">Programming</a></li>
<li class="clr3"><a href="">English</a></li>
</ul>
</div>
</div>
</nav>
</br></br></br></br>
<!--- Bagian Judul-->
<div class="container" style="margin-top:20px">
<div class="col-md-7">
<div class="panel panel-primary">
<div class="panel-heading">The Graph of Browser Trends January 2015</div>
<div class="panel-body">
<div id ="mygraph"></div>
</div>
</div>
</div>
</div>
<div class="navbar navbar-default navbar-fixed-bottom footer-bottom">
<div class="container text-center">
<p class="text-center">Copyright © 2016, DTC. Developed by <a href="https://ilmu-detil.blogspot.com/">Pusat Ilmu</a></p>
</div>
</div>
</body>
</html>