Line Chart With PHP MySQL on Bootstrap by using HighCharts
A line chart or line graph is a type of chart which displays information as a series of data points called 'markers' connected by straight line segments. It is a basic type of chart common in many fields and Line charts are used to display trends over time. Because of that, we often see a website using Line Chart to display something like : how the stock value for a certain company develops over time on the stock market.
In this post, we still use highchart as a library to draw a chart and the final result of in this tutorial will be shown by figured.1 below :
Steps to create a line chart
Follow the steps below to create a line chart using library highcharts, PHP, Database MySQL on Bootstrap layout :1. Create Database
Give name to database as dbline, where it contain five fields and data as shown by script sql below :CREATE TABLE IF NOT EXISTS `comodity` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`month` varchar(20) NOT NULL,
`sugar` int(11) NOT NULL,
`rice` int(11) NOT NULL,
`wheat_flour` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
INSERT INTO `comodity` (`id`, `month`, `sugar`, `rice`, `wheat_flour`) VALUES
(1, 'Jan', 6415, 5442, 6759),
(2, 'Feb', 6430, 5457, 7921),
(3, 'Mar', 6437, 5376, 7291),
(4, 'Apr', 6301, 5398, 7627),
(5, 'May', 6440, 5363, 7641),
(6, 'Jun', 6502, 5501, 7704),
(7, 'Jul', 6441, 5471, 7744),
(8, 'Aug', 6465, 5400, 7820),
(9, 'Sep', 6446, 5420, 7790),
(10, 'Oct', 6700, 5900, 7850),
(11, 'Nov', 6650, 5460, 7320),
(12, 'Dec', 6680, 5789, 7867);2. Create file dataline.php
In this file we establish a connection to the database,do query on each commodity like query to sugar, rice and wheat_flour. Results of data query will be formatted in JSON.<?php
$con=mysqli_connect("localhost","root","","dbline");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
// Data for Sugar
$query = mysqli_query($con,"SELECT sugar FROM comodity");
$rows = array();
$rows['name'] = 'Sugar';
while($tmp= mysqli_fetch_array($query)) {
$rows['data'][] = $tmp['sugar'];
}
// Data for Rice
$query = mysqli_query($con,"SELECT rice FROM comodity");
$rows1 = array();
$rows1['name'] = 'Rice';
while($tmp = mysqli_fetch_array($query)) {
$rows1['data'][] = $tmp['rice'];
}
// Data for Wheat Flour
$query = mysqli_query($con,"SELECT wheat_flour FROM comodity");
$rows2 = array();
$rows2['name'] = 'Wheat Flour';
while($tmp = mysqli_fetch_array($query)) {
$rows2['data'][] = $tmp['wheat_flour'];
}
$result = array();
array_push($result,$rows);
array_push($result,$rows1);
array_push($result,$rows2);
print json_encode($result, JSON_NUMERIC_CHECK);
mysqli_close($con);
?> 3. Call JSON data and make property of line chart
In step 3, we will call the JSON data from the file of dataline.php. Then, we would set the type of graph with the line. Define the names of the months on the code ofxAxis:{categories:[' '] and the price at yAxis:{ title: {text: 'Price (Rp)'}. If you want to display the legend vertically, do it on the code legend: {layout: 'vertical', because by default if we do not make the code, the legend will be horizontally by position below.
<script type="text/javascript">
$(function () {
var chart;
$(document).ready(function() {
$.getJSON("dataline.php", function(json) {
chart = new Highcharts.Chart({
chart: {
renderTo: 'mygraph',
type: 'line'
},
title: {
text: 'Comparison of Sugar, Rice and Wheat Flour'
},
subtitle: {
text: '(Price In 2008)'
},
xAxis: {
categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
},
yAxis: {
title: {
text: 'Price (Rp)'
},
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: 120,
borderWidth: 0
},
series: json
});
});
});
});
</script>
<script src="assets/js/highcharts.js"></script>
<script src="assets/js/exporting.js"></script>Don't forget to give a name to the code of the renderTo, in this example we named mygraph.It is to be id unique.
4. Call id unique with div element
<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>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 type="text/javascript">
$(function () {
var chart;
$(document).ready(function() {
$.getJSON("dataline.php", function(json) {
chart = new Highcharts.Chart({
chart: {
renderTo: 'mygraph',
type: 'line'
},
title: {
text: 'Comparison of Sugar, Rice and Wheat Flour'
},
subtitle: {
text: '(Price In 2008)'
},
xAxis: {
categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
},
yAxis: {
title: {
text: 'Price (Rp)'
},
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: 120,
borderWidth: 0
},
series: json
});
});
});
});
</script>
<script src="assets/js/highcharts.js"></script>
<script src="assets/js/exporting.js"></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-10">
<div class="panel panel-primary">
<div class="panel-heading">Commodity Price Graphs</div>
<div class="panel-body">
<div id ="mygraph"></div>
</div>
</div>
</div>
</div>
<script src="assets/js/highcharts.js"></script>
<script src="assets/js/jquery-1.10.1.min.js"></script>
<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>