Creating Google Charts from MySQL Database
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
<?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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
<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.
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
|
<?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.
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
|
<?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.
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
|
<?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.
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
56
57
58
|
<?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>
|
For more details about style options, configurations and more documentations, visit here
https://developers.google.com/chart/interactive/docs/gallery?hl=en
So,
creating charts is simple and easy with google visualization api. All
you need to do is to load your db values in right place.
Try creating google charts and tell your feedback below. Also share this post, if you find it useful.
.png)
0 comments :