Missing Data in Pie Chart
14 views
Skip to first unread message
Gregg Somes
Jan 9, 2018, 12:22:06 PM1/9/18
to Google Visualization API
Hi, I have a php script that uses POST to retrieve data from a mysql database. When a financial report is displayed using traditional php mysql calls with html tables as output the totals show as $4029.23, however, when using the charts script the totals show as $654.16 for the same period. Below is the complete script for the charts.
The "while($row = ... )" query portion is identical in both scripts but the results differ. Any insights would be appreciated.
<!DOCTYPE html>
<?php
// $for_quarter and $for_year are obtained from $_POST[]
$link = mysqli_connect($dbhost, $dbuname, $dbpass, $my_db);
if($for_quarter) {
if($for_quarter == 1)
$qn = "1st"; else if($for_quarter == 2) $qn = "2nd"; else if($for_quarter == 3) $qn = "3rd"; else if($for_quarter == 4) $qn = "4th";
$q = "select *, count(*) as number from $my_db.expenses where tax_year = '$for_year' AND quarter = '$for_quarter' GROUP BY exp_type ";
$report_for = "$qn Quarter $for_year";
} else if(!$for_quarter) {
$q = "select *, count(*) as number from $my_db.expenses where tax_year = '$for_year' GROUP BY exp_type ";
$report_for = "Yearly Report $for_year";
}
$r = mysqli_query($link, $q);
if (!$link) {
echo "Error: Unable to connect to MySQL." . PHP_EOL;
echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
exit;
}
?>
<html>
<head>
<title>Expense Pie Chart</title>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load("current", {packages:["corechart"]});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([
['Expense Type', 'Amount']
<?php
while($row = mysqli_fetch_row($r)) {
//echo $row["amount"]."<br>";
//echo "['".$row['exp_type']."', '".$row['amount']."'],";
echo ", ['$row[5]', $row[7]]";
$total = $total + $row[7];
}
?>
]);
var options = {
title: 'Percentages of Expense Types <?php echo " (Total Expenses $$total) "; ?> ',
is3D: true,
};
var chart = new google.visualization.PieChart(document.getElementById("piechart"));
chart.draw(data, options);
}
</script>
</head>
<body>
<div style="width:900px; height: 500px;">
<?php echo "<h3>$my_company Income/Expense Pie Chart $report_for "._GOBACK."</h3>"; ?>
<br />
<div id="piechart" style="width: 900px; height: 500px;"></div>
</div>
</body>
</html>
The "while($row = ... )" query portion is identical in both scripts but the results differ. Any insights would be appreciated.
<!DOCTYPE html>
<?php
// $for_quarter and $for_year are obtained from $_POST[]
$link = mysqli_connect($dbhost, $dbuname, $dbpass, $my_db);
if($for_quarter) {
if($for_quarter == 1)
$qn = "1st"; else if($for_quarter == 2) $qn = "2nd"; else if($for_quarter == 3) $qn = "3rd"; else if($for_quarter == 4) $qn = "4th";
$q = "select *, count(*) as number from $my_db.expenses where tax_year = '$for_year' AND quarter = '$for_quarter' GROUP BY exp_type ";
$report_for = "$qn Quarter $for_year";
} else if(!$for_quarter) {
$q = "select *, count(*) as number from $my_db.expenses where tax_year = '$for_year' GROUP BY exp_type ";
$report_for = "Yearly Report $for_year";
}
$r = mysqli_query($link, $q);
if (!$link) {
echo "Error: Unable to connect to MySQL." . PHP_EOL;
echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
exit;
}
?>
<html>
<head>
<title>Expense Pie Chart</title>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load("current", {packages:["corechart"]});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([
['Expense Type', 'Amount']
<?php
while($row = mysqli_fetch_row($r)) {
//echo $row["amount"]."<br>";
//echo "['".$row['exp_type']."', '".$row['amount']."'],";
echo ", ['$row[5]', $row[7]]";
$total = $total + $row[7];
}
?>
]);
var options = {
title: 'Percentages of Expense Types <?php echo " (Total Expenses $$total) "; ?> ',
is3D: true,
};
var chart = new google.visualization.PieChart(document.getElementById("piechart"));
chart.draw(data, options);
}
</script>
</head>
<body>
<div style="width:900px; height: 500px;">
<?php echo "<h3>$my_company Income/Expense Pie Chart $report_for "._GOBACK."</h3>"; ?>
<br />
<div id="piechart" style="width: 900px; height: 500px;"></div>
</div>
</body>
</html>
Reply all
Reply to author
Forward
0 new messages