Displaying Google Chart from database not showing data correctly

Question

I am making an management web application. I want to display sales and purchase graph from database. I am using php to retrieve data and ajax to display it in html page. Graph is drawing but the problem is data is not showing correctly.

Here is the link of my page:

https://smilestechno.000webhostapp.com/My/past.html

You can try it by giving this input - Categary=Purchase, View=Monthly, Year=2018, Month=Jun

Script

<script type="text/javascript">

// Load the Visualization API and the piechart package.
google.charts.load('current', {'packages':['bar']});

function drawChart() {

     var category = document.getElementById('category');
      var categorySelected = category.options[category.selectedIndex].value;
      var view = document.getElementById('view');
      var viewSelected = view.options[view.selectedIndex].value;
      var month = document.getElementById('month');
      var monthSelected = month.options[month.selectedIndex].value;
      var year = document.getElementById('year');
      var yearSelected = year.options[year.selectedIndex].value;

      var settings = {
    "url": "php/past.php",
    "method": "post",
    "headers": {
    "Content-Type": "application/x-www-form-urlencoded",
    },
    "data": {
    "category": categorySelected,
    "view": viewSelected,
    "month": monthSelected,
     "year": yearSelected
     }
    }

    $.ajax(settings).done(function(response) {
var data = google.visualization.arrayToDataTable([
['Month', 'Amount'], response
 ]);

var options = {
chart: {
  title: 'Past Performance Graph',
  subtitle: 'Duration : Jan 2018- Jun 2018',
}
};

var chart = new google.charts.Bar(document.getElementById('graph'));
chart.draw(data, google.charts.Bar.convertOptions(options));
});
}

</script>

PHP

<?php
$category = $_POST["category"];
$view = $_POST["view"];
$month = $_POST["month"];
$year = $_POST["year"];

$con = mysqli_connect("localhost","username","pw","db");

if($category == "Purchase"){
if($view == "Weekly"){
    $sql = "SELECT Date,SUM(Amount) from AccPurchase WHERE Date LIKE '$year-$month%' GROUP BY WEEK(Date)";
}else if($view == "Monthly"){
    $sql = "SELECT Date,SUM(Amount) from AccPurchase WHERE Date LIKE '$year%' GROUP BY MONTH(Date)";
}else if($view == "Yearly"){
    $sql = "SELECT Date,SUM(Amount) from AccPurchase GROUP BY YEAR(Date)";
}
}else if($category == "Sales"){
if($view == "Weekly"){
    $sql = "SELECT Date,SUM(Amount) from AccSales WHERE Date LIKE '$year-$month%' GROUP BY WEEK(Date)";
}else if($view == "Monthly"){
    $sql = "SELECT Date,SUM(Amount) from AccSales WHERE Date LIKE '$year%' GROUP BY MONTH(Date)";
}else if($view == "Yearly"){
    $sql = "SELECT Date,SUM(Amount) from AccSales GROUP BY YEAR(Date)";
}
}

$exc = mysqli_query($con, $sql);
$rows = array();
while ($row = mysqli_fetch_assoc($exc)) {
  $rows[] = array("v"=>$row["Date"], "v"=>$row["SUM(Amount)"]);
}
header('Content-Type: application/json');
echo json_encode($rows);
mysqli_close($con);
?>

I was finding solution and i got this post:

How to create DataTable for ColumnChart by JSON object?

I think i need to sand data in this format but i dont know how to send it from php in this format.

{
"cols": [
    {"id": "date", "label": "date","pattern": "","type": "string"},
    {"id": "newjobs","label": "newjobs","pattern": "","type": "number"}
],
"rows": [
    {"c": [
            {"v": "01-02-2013","f": null},
            {"v": 132,"f": null}
        ]},
    {"c": [
            {"v": "08-02-2013","f": null},
            {"v": 78,"f": null}
        ]},
    {"c": [
            {"v": "15-02-2013","f": null},
            {"v": 105,"f": null}
        ]
    },
    {"c": [
            {"v": "22-02-2013","f": null},
            {"v": 8,"f": null}
        ]}
]
}

Answer 1

first, let's give the sum calculation a name in all the sql statements...

change --> SUM(Amount) -- to --> SUM(Amount) as TotalAmount

e.g.

$sql = "SELECT Date,SUM(Amount) as TotalAmount from AccPurchase WHERE Date LIKE '$year-$month%' GROUP BY WEEK(Date)";

---

next, the following php will create the json in google's data table format...

$exc = mysqli_query($con, $sql);

$rows = array();
$table = array();

$table['cols'] = array(
  array('label' => 'Month', 'type' => 'string'),
  array('label' => 'Amount', 'type' => 'number')
);

while ($row = mysqli_fetch_assoc($exc)) {
  $data = array();
  $data[] = array("v" => date_format($row["Date"], "Y-m"));
  $data[] = array("v" => $row["TotalAmount"]);
  $rows[] = array('c' => $data);
}

$table['rows'] = $rows;

header('Content-Type: application/json');
echo json_encode($table);
mysqli_close($con);

note: you may want to change the date format, not exactly sure what you need here...

$data[] = array("v" => date_format($row["Date"], "Y-m"));

this will give you the format as --> 2018-06 -- for June...

---

finally, in the JavaScript, since you now have the correct json format,
you can create a new data table directly, without using --> arrayToDataTable,
like so...

$.ajax(settings).done(function(response) {

  var data = new google.visualization.DataTable(response);

  var options = {
    chart: {
      title: 'Past Performance Graph',
      subtitle: 'Duration : Jan 2018- Jun 2018',
    }
  };

  var chart = new google.charts.Bar(document.getElementById('graph'));
  chart.draw(data, google.charts.Bar.convertOptions(options));
});