我正在为客户的预订系统构建一个统计视图。他们想知道哪些活动是在特定月份预订的。
我使用谷歌图表,但数据不匹配正在产生的标签-即正确的数据被附加到错误的活动。
我已经通过数据库和print_r's验证了产品ID正在成功地从数据库中提取并附加到正确的活动ID号,并且标签的数量和顺序与活动的数量和顺序相匹配,它们是:
$sql = "SELECT ProductID, ProductName FROM products ORDER BY ProductID ASC";
$set = $link->query($sql);
while($row = $set->fetch_row())
{
$arrLabels[$row[0]] = "#".$row[0].": ".$row[1];
}
ksort($arrLabels);
$sql = "SELECT * FROM bookings_products INNER JOIN bookings ON bookings.BookingID=bookings_products.BookingProductBooking WHERE BookingConfirmed>0 ORDER BY BookingProductProduct ASC";
$set = $link->query($sql);
while($row = $set->fetch_array(MYSQLI_ASSOC))
{
$dm = substr($row['BookingDate'],5,2); // To get the month from YYYY-MM-DD dates
$arrPeople[$dm][$row['BookingProductProduct']] = $arrPeople[$dm][$row['BookingProductProduct']] + (1 * $row['BookingGroupSize']);
}
ksort($arrPeople);
echo "<pre>"; print_r($arrLabels); echo "</pre>";
echo "<hr/>";
echo "<pre>"; print_r($arrPeople); echo "</pre>";
然后我像这样构建Google Charts JS代码:
<script type="text/javascript">
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);
function drawChart()
{
var data = google.visualization.arrayToDataTable([
[<?php foreach($arrLabels as $p) { ?>'<?php echo str_replace("'","'",$p); ?>', <?php } ?>{ role: 'annotation' } ],
['Jan', <?php foreach($arrLabels as $k=>$v) { ?><?php echo $arrPeople['01'][$k] ?: '0'; ?>, <?php } ?>],
['Feb', <?php foreach($arrLabels as $k=>$v) { ?><?php echo $arrPeople['02'][$k] ?: '0'; ?>, <?php } ?>],
['Mar', <?php foreach($arrLabels as $k=>$v) { ?><?php echo $arrPeople['03'][$k] ?: '0'; ?>, <?php } ?>],
['Apr', <?php foreach($arrLabels as $k=>$v) { ?><?php echo $arrPeople['04'][$k] ?: '0'; ?>, <?php } ?>],
['May', <?php foreach($arrLabels as $k=>$v) { ?><?php echo $arrPeople['05'][$k] ?: '0'; ?>, <?php } ?>],
['Jun', <?php foreach($arrLabels as $k=>$v) { ?><?php echo $arrPeople['06'][$k] ?: '0'; ?>, <?php } ?>],
['Jul', <?php foreach($arrLabels as $k=>$v) { ?><?php echo $arrPeople['07'][$k] ?: '0'; ?>, <?php } ?>],
['Aug', <?php foreach($arrLabels as $k=>$v) { ?><?php echo $arrPeople['08'][$k] ?: '0'; ?>, <?php } ?>],
['Sep', <?php foreach($arrLabels as $k=>$v) { ?><?php echo $arrPeople['09'][$k] ?: '0'; ?>, <?php } ?>],
['Oct', <?php foreach($arrLabels as $k=>$v) { ?><?php echo $arrPeople['10'][$k] ?: '0'; ?>, <?php } ?>],
['Nov', <?php foreach($arrLabels as $k=>$v) { ?><?php echo $arrPeople['11'][$k] ?: '0'; ?>, <?php } ?>],
['Dec', <?php foreach($arrLabels as $k=>$v) { ?><?php echo $arrPeople['12'][$k] ?: '0'; ?>, <?php } ?>],
]);
var options = {
title: 'Product popularity by month - Number of people (based on activity date)',
isStacked: true,
};
var chart = new google.visualization.BarChart(document.getElementById('chart3b'));
chart.draw(data, options);
}
</script>
<div id="chart3b" style="margin-top:15px; width: 900px; height: 500px; border:solid 1px #000;"></div><hr/>
您可以看到(通过静态HTML复制粘贴动态PHP输出)这里生成的Google Chart: http://www.stuart-pinfold.co.uk/stats.htm
你会注意到活动#50(圣诞老人体验)在12月(第12个月)有195名游客,而在图表中被分配给活动#51("僵尸气枪,僵尸手枪,10粒泥弹")。
我哪里错了?
值的第一列是月份名称,但标题行的第一个单元格不是"month"标签。
如果你看一下活动#50(圣诞老人体验)的例子,你可以看到这个标签的值在图中向左1步。
确保不是
['#5: Special Offer Ultimate Shooting Package ', '#6: CPSA Shotgun Skills Course',.....],
['Jan', 0,....
它看起来像
['Months', '#5: Special Offer Ultimate Shooting Package ',.....],
['Jan', 0,.....