Displaying google graph using dynamic data not displaying correctly

Question

I want to display sales and purchase graph in my webpage. User can select category Purchase, Sales, Production. I have separate table for both Purchase and Sales(AccPurchase and AccSales). And production is a combination of both. After that user can select view, it will be weekly where graph will display week wise of selected month. Monthly means graph of all the months of year selected. And Yearly means it will display year wise. In all the graphs i want to display only sum of amount. After that there is 2 more dropdown to select year and month.

Link of page where i want to display graph

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

past.html

 <head>
    <title>Past Performance Graph</title>
    <link rel="stylesheet" type="text/css" href="css/pastperfomance.css">
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></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>
</head>

past.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"], "f"=>$row["SUM(Amount)"]);
}
header('Content-Type: application/json');
echo json_encode($rows);
mysqli_close($con);
?>

Something is wrong in my echoing array from php script.

Data may be needed in this format- https://stackoverflow.com/a/15381171/9786296

Your mixing Javascript and PHP and that's not possible, PHP is server-side system Javascript is client-side `'document.write(categorySelected)' == "Purchase"` will always be false, the string `document.write...` does not equal the string `Purchase` — Barkermn01, Jun 12 '18 at 08:54
this depends on your situation, you can use AJAX to fetch the results per your selected option and get a JSON version of your array for the graph and reload it. — Barkermn01, Jun 12 '18 at 09:01
U said i cannot use javascript inside php. But i seen many post in which they are using javascript inside php using script tag — , Jun 12 '18 at 09:05
@Acg you are right. You can do this. php only replace `` tags with the result (should be html of course) from the server. Martin is right when you want to let the user to enter his value. In this case you can use A. ajax, then refresh the graphs with the new data. B. do `post` call with the user's input and render the page according it. You can read the Google's guide for it [here](https://developers.google.com/chart/interactive/docs/php_example) — Mosh Feu, Jun 12 '18 at 09:47
I change the code n updated in question. I am using ajax but still getting some error. I am not good with ajax please check whats wrong. Link of new file also in question please check error. @MoshFeu — , Jun 12 '18 at 11:04
Add `method: 'post'` to your ajax options. Search for `method` in the [docs](http://api.jquery.com/jquery.ajax/). Also, don't use `async: false` option, search `async` in the docs. — Mosh Feu, Jun 12 '18 at 11:08
See the console error: `Unexpected token ;` just replace `;` with `,` in the line `method: 'post';` — Mosh Feu, Jun 12 '18 at 12:09
still something wrong cant draw chart. `Uncaught Error: Invalid row type for row 0 at gvjs_Fba (jsapi_compiled_default_module.js:85)` @MoshFeu — , Jun 13 '18 at 04:49
It seems that the server responses nothing: https://i.stack.imgur.com/eznWr.png. Double check your response. — Mosh Feu, Jun 13 '18 at 04:57
server working fine bcoz other files are accessible. My php script is correct? @MoshFeu — , Jun 13 '18 at 05:58
It seems so but it's hard to answer without debugging. If I was you, I was start with a simple hardcoded response. If it's working, I was continue by echo after each line to understand which line is the problematic. Or, if you can turn the debug flag so the server will response the error it will the best.. — Mosh Feu, Jun 13 '18 at 09:22
the problem was in if else statement. Now php script is working. But still error coming in generating graph. @MoshFeu — , Jun 14 '18 at 08:39
If you updated the link, the server still returns nothing: You can see the (empty) response here: https://www.codepunker.com/tools/http-requests/121580-9oploco — Mosh Feu, Jun 14 '18 at 08:54
Yes I also checked that it is not returning in browser but it is returning in postman. @MoshFeu — , Jun 14 '18 at 08:55
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/173121/discussion-between-mosh-feu-and-acg). — Mosh Feu, Jun 14 '18 at 09:02

Mosh Feu · Answer 1 · 2018-06-24T08:44:45.970

There are some issues:

You have to return JSON from the server if you want to use it as is in the client. Your code not returns a valid JSON - ['2018-06-09','1500'],['2018-06-10','538900'], it's not valid. You need to replace the ' with ". In generally probably it will be better to create an object and "stringify" it, this way you can be sure that you have a valid object. You can see how to do this here: https://stackoverflow.com/a/383664/863110

Replace:

while($row = mysqli_fetch_array($exc)){
  echo json_encode("[".$row["Date"].",".$row["SUM(Amount)"]."],");
}

With:

$rows = array();
while ($row = mysqli_fetch_assoc($exc)) {
  $rows[] = array($row["Date"], $row["Amount"]);
}
echo json_encode($rows);

Another problem is that you are sending the post request wrong. Instead of put the values in the url, you should pass it as JSON object data, like this:

var settings = {
  "url": "https://smilestechno.000webhostapp.com/My/php/past.php",
  "method": "POST",
  "headers": {
    "Content-Type": "application/x-www-form-urlencoded",
  },
  "data": {
    "category": "Purchase",
    "view": "Weekly",
    "month": "06",
    "year": "2018"
  }
}

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

  var options = {
    chart: {
      title: 'Comparative Analysis',
      subtitle: 'Duration : Jan 2018- Jun 2018',
    }
  };

  var chart = new google.charts.Bar(document.getElementById('graph'));
});

Remember that ajax call is async (*don't use async: false) so you can draw the chart only after the you get the response

* As of jQuery 1.8, the use of async: false with jqXHR ($.Deferred) is deprecated; you must use the success/error/complete callback options instead of the corresponding methods of the jqXHR object such as jqXHR.done()

^{http://api.jquery.com/jquery.ajax/}

Check my updated code and also check my link. Graph is coming, but it is setting only my Amount in both the axis. Also check link i sent u in chatroom. — , Jun 15 '18 at 10:42
True. The format you supply from the server is invalid. You should supply an array with the the same number of items as you supplied to the graph, which means in your case, 2. The response should look like: `["2018-05-23", "400"]`. Even so, due you have only 1 result it will look weird: https://i.stack.imgur.com/VZoMR.png. You need to supply more rows to compare with. — Mosh Feu, Jun 17 '18 at 09:39

Displaying google graph using dynamic data not displaying correctly

1 Answers1