Google 柱形图中的结果不正确

2024-05-21 • 问答

我试图用下面的代码做的是绘制一个柱状图，在任何日期显示两种类型的金额（收入和冲销），但是结果不正确，我找不到更好的方法纠正一下。

谁能帮我实现这个目标？

<?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>

从你的sql开始，你实际上需要进行条件求和。 (DB Fiddle Demo)

SELECT 
    DATE(trans_time) AS Date,SUM(IF(trans_type = 'Income',trans_amount,0)) AS Income,SUM(IF(trans_type = 'Reversal',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,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','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 表的每一行中都有日期和日期时间似乎完全是多余的。如果这是我的应用程序，我会从架构中删除日期列。

Google 柱形图中的结果不正确

jia654112637 回答：Google 柱形图中的结果不正确

大家都在问