PHP + MySQL - 在谷歌条形图中显示空条目

Question

我正在尝试使用带有MySQL数据库的Google Charts API为我的网站实现每月导入/导出图。一切都很好，但我正在寻找一种方法在我的图表中显示没有进口或出口的月份。有人可以帮我吗？这是我的图表的屏幕截图，因为您可以看到缺少几个月

<div id="columnchart_material" style="width: 800px; height: 500px;"></div>
<script type="text/javascript">
google.charts.load('current', {'packages':['bar']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var dati = google.visualization.arrayToDataTable([
['Month', 'Imports', 'Exports'],
<?php
$q = "SELECT month(date) AS month, SUM(imports) AS imports, SUM(exports) AS exports FROM table WHERE year(date)=2020 GROUP BY month(date)";
$res_q=mysqli_query($db,$q);
//fetch data
while ($row = mysqli_fetch_array($res_q)) {
$entry = "['".$row{'month'}."',".$row{'imports'}.",".$row{'exports'}."],";
echo $entry;
}
?>
]);
var options = {
chart: {
title: 'Imports and Exports',
subtitle: 'Bar chart',
}
};
var chart = new google.charts.Bar(document.getElementById('columnchart_material'));
chart.draw(dati, google.charts.Bar.convertOptions(options));
}

Answer 1

请考虑以下事项：

<?php
$array = array(1,2,3,5,7,11);
for($i=1;$i<=12;$i++){
if(in_array($i,$array)){echo $i.' yes<br>';} else {echo $i.' no<br>';}
}

?>

Answer 2

您可以使用一个额外的数组来保存您的数据并包含所有月份：

<?php
// prepare array with default 'empty' data
$entries = []
for ($m = 1; $m <= 12; $m++) {
$entries[$m] = '[]';
}

然后通过覆盖默认值将现有数据添加到此数组中：

// fetch data
while ($row = mysqli_fetch_array($res_q)) {
$entries[$row['month']] = "['".$row{'month'}."',".$row{'imports'}.",".$row{'exports'}."]";
}
// output
echo implode(',', $entries);