If you have working on any web application and in that you want to display analytics data, so for display analytics data you want to use charts. For making dynamic charts from big data, so Google has provide rich and powerful charts library which is available free to use with any programming language. By using Google Charts Api we can use different type of charts like Column chart, bar chart, area chart, geo graphical chart and many more. So, we can use this all charts in our web application as per our requirement. Charts or Graph is a graphical representation of our dynamic data, so based on this Charts we can analyze data and can take decision. Most of management has take their decision based on Graphical representation of data which has been display in Graph format and this graph we will make by using Google charts library.
In this post we have use Google Chart library api and by using this Api we will make Column Charts from dynamic Mysql table data using Ajax with PHP PDO script. We have use Ajax with Google charts that means we will make Google Charts Column charts using Ajax call and in Ajax request which send request to PHP script for fetch data from Mysql database and converted into PHP array and then this PHP array data will be converted into JSON string. Because Google Charts has use JSON data for populate charts on web page. So here we have converted PHP array to JSON and this JSON data has been received by Ajax function. After receiving of dynamic data in JSON format. So, lastly we have to load this data into Google charts. On every Ajax function call it has fetch dynamic data in JSON format and make dynamic column chart on web page without refresh of web page.
If we have large amount of data and this data we want to make dynamic chart from data divided into different part. e.g. We have last 10 years of data and now we cannot display last 10 years data in single chart, so we can divided this data into year, so we can display every year data on web in chart format by using Ajax. So suppose we want to get second year data so we can simply select that year data from select box and on selection of year it will called Ajax call and it will fetch selected year data from Mysql database and received in json format and load into Google charts library. So this way we can make dynamic column chart for each year using Google charts library with Ajax and PHP.
Source Code
database_connection.php
<?php
//database_connection.php
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
?>
index.php
<?php
//index.php
include("database_connection.php");
$query = "SELECT year FROM chart_data GROUP BY year DESC";
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
?>
<!DOCTYPE html>
<html>
<head>
<title>Create Dynamic Column Chart using PHP Ajax with Google Charts</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
</head>
<body>
<br /><br />
<div class="container">
<h3 align="center">Create Dynamic Column Chart using PHP Ajax with Google Charts</h3>
<br />
<div class="panel panel-default">
<div class="panel-heading">
<div class="row">
<div class="col-md-9">
<h3 class="panel-title">Month Wise Profit Data</h3>
</div>
<div class="col-md-3">
<select name="year" class="form-control" id="year">
<option value="">Select Year</option>
<?php
foreach($result as $row)
{
echo '<option value="'.$row["year"].'">'.$row["year"].'</option>';
}
?>
</select>
</div>
</div>
</div>
<div class="panel-body">
<div id="chart_area" style="width: 1000px; height: 620px;"></div>
</div>
</div>
</div>
</body>
</html>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {packages: ['corechart', 'bar']});
google.charts.setOnLoadCallback();
function load_monthwise_data(year, title)
{
var temp_title = title + ' '+year+'';
$.ajax({
url:"fetch.php",
method:"POST",
data:{year:year},
dataType:"JSON",
success:function(data)
{
drawMonthwiseChart(data, temp_title);
}
});
}
function drawMonthwiseChart(chart_data, chart_main_title)
{
var jsonData = chart_data;
var data = new google.visualization.DataTable();
data.addColumn('string', 'Month');
data.addColumn('number', 'Profit');
$.each(jsonData, function(i, jsonData){
var month = jsonData.month;
var profit = parseFloat($.trim(jsonData.profit));
data.addRows([[month, profit]]);
});
var options = {
title:chart_main_title,
hAxis: {
title: "Months"
},
vAxis: {
title: 'Profit'
}
};
var chart = new google.visualization.ColumnChart(document.getElementById('chart_area'));
chart.draw(data, options);
}
</script>
<script>
$(document).ready(function(){
$('#year').change(function(){
var year = $(this).val();
if(year != '')
{
load_monthwise_data(year, 'Month Wise Profit Data For');
}
});
});
</script>
<?php
//fetch.php
include('database_connection.php');
if(isset($_POST["year"]))
{
$query = "
SELECT * FROM chart_data
WHERE year = '".$_POST["year"]."'
ORDER BY id ASC
";
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
foreach($result as $row)
{
$output[] = array(
'month' => $row["month"],
'profit' => floatval($row["profit"])
);
}
echo json_encode($output);
}
?>
Database?
ReplyDeletesir can u send me code of full JS and CSS Files. i want to try this offline
Deletecan i try this offline using source file of JS &CSS?
DeleteCan u send this code to mehul.2406@gmail.com
ReplyDeletefor database
ReplyDeleteThanks,bro
ReplyDeleteGood & Thanks
ReplyDeletecan i get the database ty
ReplyDeleteThank you very much ! It works perfectly !
ReplyDeleteThank you, it is a clear walkthrough :)
ReplyDeleteThat's a great tutorial. Thanks!
ReplyDeleteI would like to explore different chart types like pie, line and multiple column charts. If I can add multiple column chart in this column chart, it would help me a lot. Could you please provide me the useful links or some sample codes to render multiple column chart.
Thank you :)
Hi, Thanks for such great tutorials.
ReplyDeleteIs there a tutorial for creating Dynamic chart similar to this tutorial but Combo Chart using PHP Ajax with Google Charts like the one in this link: https://developers.google.com/chart/interactive/docs/gallery/combochart#example
I want to filter by year and get multiple bar charts(Combo Chart).
Thank you :)
Thank you so much. This is awesome. I do want to know, currently this takes up the entire page. What is a easy way to fit 2 of the charts next to each other and both using the same year filter?
ReplyDeleteGood & Thanks. it is working nicely.
ReplyDeleteGood & Thanksssssss
ReplyDeleteDo you mind if I ask you a favor?
ReplyDeleteThe chart doest appear on my page, why?
I copy all the code from the link given.
Hi, can you help me that I want to show the monthly day leaves, gate pass, late-ins from database record.
ReplyDeleteSql database please
ReplyDeleteI used your tutorial and need a little help. I would like to dynamically create the Google Chart based on data returned from a PHP call. In your example, you have pre-defined columns Month and Profit for the Google Chart. I would like the column titles to be dynamic. Sometimes, the columns may be Month and Profit, and sometimes the columns may be Year and Loss. How do I make the columns dynamic.
ReplyDeletedata.addColumn('string', 'Month');
data.addColumn('number', 'Profit');
Hey, thank you very very much for this tutorial (and all the others).
ReplyDeleteI checked it and all was fine. However, I'd like to program one in which the charts take account if new data has entered. I know that I can do this by using a SetInverval function to refresh de chart, but because selecting a year (from the little menu) is imperative, the chart data disappears every "SetInterval" period. So I really would appreciate if you can give a clue about it.
And again, thanks!
thank you bro
ReplyDeleteit is too good
ReplyDeletehow to change laravel
ReplyDeleteDatabase Backup(.sql) File pls ?
ReplyDeleteMany Thanks Brother. Very Good
ReplyDeleteHow to Change the Profit to Production [prodn] in this tutorial>.. Please help me. tushirgobi@gmail.com
ReplyDeleteIt works after changing my own DB attributes. Thanks a lot for your help.
ReplyDeletehow to add multiple option's (year) ->(Catergory) like
ReplyDeleteThank you very much for Perfect Lesson.
ReplyDeleteThan you so much!!!
ReplyDelete