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

?>

28 comments:

  1. Can u send this code to mehul.2406@gmail.com

    ReplyDelete
  2. Thank you very much ! It works perfectly !

    ReplyDelete
  3. Thank you, it is a clear walkthrough :)

    ReplyDelete
  4. That's a great tutorial. Thanks!
    I 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 :)

    ReplyDelete
  5. Hi, Thanks for such great tutorials.

    Is 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 :)

    ReplyDelete
  6. 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?

    ReplyDelete
  7. Good & Thanks. it is working nicely.

    ReplyDelete
  8. Good & Thanksssssss

    ReplyDelete
  9. Do you mind if I ask you a favor?
    The chart doest appear on my page, why?
    I copy all the code from the link given.


    ReplyDelete
  10. Hi, can you help me that I want to show the monthly day leaves, gate pass, late-ins from database record.

    ReplyDelete
  11. I 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.

    data.addColumn('string', 'Month');
    data.addColumn('number', 'Profit');

    ReplyDelete
  12. Hey, thank you very very much for this tutorial (and all the others).
    I 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!

    ReplyDelete
  13. Database Backup(.sql) File pls ?

    ReplyDelete
  14. Many Thanks Brother. Very Good

    ReplyDelete
  15. How to Change the Profit to Production [prodn] in this tutorial>.. Please help me. tushirgobi@gmail.com

    ReplyDelete
  16. It works after changing my own DB attributes. Thanks a lot for your help.

    ReplyDelete
  17. how to add multiple option's (year) ->(Catergory) like

    ReplyDelete
  18. Thank you very much for Perfect Lesson.

    ReplyDelete