Displaying google graph using dynamic data not displaying correctly

Question 1

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

Question 2

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

Question 3

Then how can i do this? @MartinBarker

Question 4

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.

Question 5

U said i cannot use javascript inside php. But i seen many post in which they are using javascript inside php using script tag

Question 6

@Acg you are right. You can do this. php only replace <?php ?> 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

Question 7

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/}

Question 8

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.

Question 9

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: i.sstatic.net/VZoMR.png. You need to supply more rows to compare with.

Question 10

@Acg Any thoughts?

Mosh Feu 29.5k18 gold badges94 silver badges142 bronze badges · Accepted Answer · 2018-06-14 09:15:40Z

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.
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: i.sstatic.net/VZoMR.png. You need to supply more rows to compare with.

CollectivesTM on Stack Overflow

Displaying google graph using dynamic data not displaying correctly

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Linked

Hot Network Questions

CollectivesTM on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Linked

Related