Dunia Telekomunikasi, dan Komputer: Chart Google Connect database via PHP mysql

If you need to display some sort of statistics on your website, it would be beautiful if you represent it with charts. It may be difficult to create those charts on your own. But, Google provides visualization api that makes it easy to create google charts from MySQL database using php.

Basic Setup to Generate Charts

To create google charts, you need to include Google visualization api in your page and make sure you have connected with your MySQL database like in the simple and basic setup below.


<?php
 $con = mysqli_connect('hostname','username','password','database');
?>
<!DOCTYPE HTML>
<html>
<head>
 <meta charset="utf-8">
 <title>
 Create Google Charts
 </title>
 <script type="text/javascript" src="https://www.google.com/jsapi"></script>
</head>
<body>
</body>
</html>

For this tutorial, we are going to show visitors statistics on our page with four kinds of charts such as Column chart, Pie chart, Geo chart and Bar chart.
I assume that you already have the data in your database. But if you don’t know how to get your visitors data, I will post it as a separate tutorial on how to save your visitors geo location data to your mysql database.
For now, let us consider this sample MySQL table entries from which we are going to create google charts.

Column Chart

Column chart is helpful if you want to show your stats for a particular time period. So we can use this chart for period wise stats.
Excluding id, we have four columns such as ip, browser, country and vdate. So we can generate date wise chart by grouping mysql select query by vdate.


<script type="text/javascript">
 google.load("visualization", "1", {packages:["corechart"]});
 google.setOnLoadCallback(drawChart);
 function drawChart() {
 var data = google.visualization.arrayToDataTable([
 ['Date', 'Visits'],
 ['2015-09-01','100'],
 ['2015-09-02','80'],
 ['2015-09-03','210'],
 ]);

 var options = {
 title: 'Date wise visits'
 };
 var chart = new google.visualization.ColumnChart(document.getElementById("columnchart"));
 chart.draw(data, options);
 }
</script>

The above code is the basic syntax for creating a column chart. Look at the values after “var data = google.visualization.arrayToDataTable([” This one has static values. But in order to show our stats, we need to dynamically load these values from our database with php.


var data = google.visualization.arrayToDataTable([
 ['Date', 'Visits'],
 <?php 
 $query = "SELECT count(ip) AS count, vdate FROM visitors GROUP BY vdate ORDER BY vdate";
 $exec = mysqli_query($con,$query);
 while($row = mysqli_fetch_array($exec)){
 echo "['".$row['vdate']."',".$row['count']."],";
 }
 ?>
 
]);

The final code will be something like this.


<?php
 $con = mysqli_connect('hostname','username','password','database');
?>
<!DOCTYPE HTML>
<html>
<head>
 <meta charset="utf-8">
 <title>
 Create Google Charts
 </title>
 <script type="text/javascript" src="https://www.google.com/jsapi"></script>
 <script type="text/javascript">
 google.load("visualization", "1", {packages:["corechart"]});
 google.setOnLoadCallback(drawChart);
 function drawChart() {
 var data = google.visualization.arrayToDataTable([

 ['Date', 'Visits'],
 <?php 
 $query = "SELECT count(ip) AS count, vdate FROM visitors GROUP BY vdate ORDER BY vdate";

 $exec = mysqli_query($con,$query);
 while($row = mysqli_fetch_array($exec)){

 echo "['".$row['vdate']."',".$row['count']."],";
 }
 ?>
 
 ]);

 var options = {
 title: 'Date wise visits'
 };
 var chart = new google.visualization.ColumnChart(document.getElementById("columnchart"));
 chart.draw(data, options);
 }
 </script>
</head>
<body>
 <h3>Column Chart</h3>
 <div id="columnchart" style="width: 900px; height: 500px;"></div>
</body>
</html>

Pie Chart

Pie chart is the most beautiful charts that can be created with google visualization api. You can create Pie charts in situations like representing the values in percentage. So we are going to create a chart that shows browser wise visits from our database. Like we did for column chart above, you have to dynamically load the values to replace the static content from google pie chart’s syntax.


<?php
 $con = mysqli_connect('hostname','username','password','database');
?>
<!DOCTYPE HTML>
<html>
<head>
 <meta charset="utf-8">
 <title>
 Create Google Charts
 </title>
 <script type="text/javascript" src="https://www.google.com/jsapi"></script>
 <script type="text/javascript">
 google.load("visualization", "1", {packages:["corechart"]});
 google.setOnLoadCallback(drawChart);
 function drawChart() {

 var data = google.visualization.arrayToDataTable([
 ['Browser', 'Visits'],
 <?php 
 $query = "SELECT count(ip) AS count, browser FROM visitors GROUP BY browser";

 $exec = mysqli_query($con,$query);
 while($row = mysqli_fetch_array($exec)){

 echo "['".$row['browser']."',".$row['count']."],";
 }
 ?>
 ]);

 var options = {
 title: 'Browser wise visits'
 };

 var chart = new google.visualization.PieChart(document.getElementById('piechart'));

 chart.draw(data, options);
 }
 </script>
</head>
<body>
 <h3>Pie Chart</h3>
 <div id="piechart" style="width: 900px; height: 500px;"></div>
</body>
</html>

Geo Chart

Geo chart’s main purpose is to show country wise reports with a world map. We have a column called country. So we can create a geo chart to show country wise visits with our data.


<?php
 $con = mysqli_connect('hostname','username','password','database');
?>
<!DOCTYPE HTML>
<html>
<head>
 <meta charset="utf-8">
 <title>
 Create Google Charts
 </title>
 <script type="text/javascript" src="https://www.google.com/jsapi"></script>
 <script type="text/javascript">
 google.load("visualization", "1", {packages:["geochart"]});
 google.setOnLoadCallback(drawRegionsMap);

 function drawRegionsMap() {

 var data = google.visualization.arrayToDataTable([

 ['Country', 'Visits'],
 <?php 
 $query = "SELECT count(ip) AS count, country FROM visitors GROUP BY country";

 $exec = mysqli_query($con,$query);
 while($row = mysqli_fetch_array($exec)){

 echo "['".$row['country']."',".$row['count']."],";
 }
 ?>
 ]);

 var options = {
 
 };

 var chart = new google.visualization.GeoChart(document.getElementById('geochart'));

 chart.draw(data, options);
 }
 </script>
</head>
<body>
 <h3>Geo Chart</h3>
 <div id="geochart" style="width: 900px; height: 500px;"></div>
</body>
</html>

Bar Chart

Basically Bar charts and Column charts are same but it can be used for different purposes according to the users. I use bar charts in situations like comparing two or more values for each row.
Since we are generating visitors stats, we can actually determine new visitors and returning visitors by their ip address. So we are going to create a bar chart that shows new and returned visitors for each country.


<?php
 $con = mysqli_connect('hostname','username','password','database');
?>
<!DOCTYPE HTML>
<html>
<head>
 <meta charset="utf-8">
 <title>
 Create Google Charts
 </title>
 <script type="text/javascript" src="https://www.google.com/jsapi"></script>
 
 <script type="text/javascript">
 google.load('visualization', '1', {packages: ['corechart', 'bar']});
 google.setOnLoadCallback(drawMaterial);

 function drawMaterial() {
 var data = google.visualization.arrayToDataTable([
 ['Country', 'New Visitors', 'Returned Visitors'],
 <?php 
 $query = "SELECT count(ip) AS count, country FROM visitors GROUP BY country";

 $exec = mysqli_query($con,$query);

 while($row = mysqli_fetch_array($exec)){

 echo "['".$row['country']."',";
 $query2 = "SELECT count(distinct ip) AS count FROM visitors WHERE country='".$row['country']."' ";
 $exec2 = mysqli_query($con,$query2);
 $row2 = mysqli_fetch_assoc($exec2);
 
 echo $row2['count'];
 
 

 $rvisits = $row['count']-$row2['count'];

 echo ",".$rvisits."],";
 }
 ?>
 ]);

 var options = {
 
 title: 'Country wise new and returned visitors',
 
 bars: 'horizontal'
 };
 var material = new google.charts.Bar(document.getElementById('barchart'));
 material.draw(data, options);
 }
 </script>
</head>
<body>
 <h3>Bar Chart</h3>
 <div id="barchart" style="width: 900px; height: 500px;"></div>
</body>
</html>