Saturday, 14 July 2018

Create Dynamic Column Chart using PHP Ajax with Google Charts



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);
}

?>

1 comment: