Google列图中的错误结果
Google列图中的错误结果
提问于 2021-07-24 21:34:08
我试图用下面的代码绘制一个列图，在任何日期显示两种类型的金额(收入和反转)，但是结果是不正确的，我找不到更好的方法来纠正它。
https://imgur.com/nfSNUU3 <-我需要什么。
https://imgur.com/05EbeR9 <-我所拥有的。
有人能帮我解决这个问题吗？
<?Php
require "config.php";// Database connection
$stmt = $connection->query("SELECT date,trans_type,Sum(trans_amount) FROM prefix_transactions GROUP by date,trans_type"))
$php_data_array = Array();
while ($row = $stmt->fetch_row()) {
$php_data_array[] = $row;
}
echo json_encode($php_data_array);
echo "<script>
var my_2d = ".json_encode($php_data_array)."
</script>";
?>
<div id="chart_div"></div>
<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(drawChart);
function drawChart() {
var data = new google.visualization.DataTable();
data.addColumn('string', 'Date');
data.addColumn('number', 'Type');
data.addColumn('number', 'Amount');
for (i = 0; i < my_2d.length; i++)
data.addRow([my_2d[i][0], parseInt(my_2d[i][2]),parseInt(my_2d[i][2])]);
var options = {
hAxis: {title: 'Month', titleTextStyle: {color: '#333'}},
vAxis: {minValue: 0},
width:500,
height:400
};
var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>回答 1
Stack Overflow用户
回答已采纳
发布于 2021-07-25 08:52:47
从sql开始，实际上需要执行条件求和。(DB Fiddle演示)
SELECT
DATE(trans_time) AS Date,
SUM(IF(trans_type = 'Income', trans_amount, 0)) AS Income,
SUM(IF(trans_type = 'Reversal', trans_amount, 0)) AS Reversal
FROM prefix_transactions
WHERE ref_number = 'CompanyXYZ'
GROUP BY Date
ORDER BY Date;结果集(来自样本数据的屏幕截图)：
| Date | Income | Reversal |
| ---------- | ------ | -------- |
| 2021-07-24 | 350.00 | 50.00 |
| 2021-07-25 | 200.00 | 50.00 |PHP代码：
$ref_number = 'CompanyXYZ';
$sql = "SELECT DATE(trans_time) AS Date,
SUM(IF(trans_type = 'Income', trans_amount, 0)) AS Income,
SUM(IF(trans_type = 'Reversal', trans_amount, 0)) AS Reversal
FROM prefix_transactions
WHERE ref_number = ?
GROUP BY Date
ORDER BY Date"
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $ref_number);
$stmt->execute();
$result[] = ['Date', 'Income', 'Reversal'];
foreach ($stmt->get_result() as $row) {
$result[] = [$row['Date'], $row['Income'], $row['Reversal']];
}
// then in your javascript...
new google.visualization.arrayToDataTable(<?php echo json_encode($result); ?>),下面是一个可运行的javascript代码片段，以证明其有效性：
google.load('visualization', '1', {
packages: ['corechart', 'bar']
});
google.setOnLoadCallback(draw);
function draw() {
var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
chart.draw(
new google.visualization.arrayToDataTable(
[
['Date', 'Income', 'Reversal'],
['2021-07-24', 350.00, 50.00],
['2021-07-25', 200.00, 50.00]
]
),
{
hAxis: {title: 'Day', titleTextStyle: {color: '#333'}},
vAxis: {minValue: 0},
width:500,
height:400
}
);
}<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>
附注：在db表的每一行中拥有日期和日期时间似乎是完全多余的。如果这是我的应用程序，我将从模式中删除date列。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/68513967
复制相关文章
相似问题
腾讯云开发者
