google折线图在php中使用mysql的数据时不会按月分组只显示所有日期

ovfsdjhp  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(358)

我使用以下php和javascript代码从mysql数据构建google折线图。它的工作,但它给我的每一个日期,我需要它按月份分组。如何调整代码使“一月”、“二月”、“三月”等在x轴上?
(连接正常)

<?php
....
$conn = new mysqli($servername, $username, $password, $dbname);

if (!$conn) {
die("Connection failed: ". mysqli_connect_error());
}

$query = "SELECT SUM(day_hours) AS 'hours', 
date FROM monthly_totals WHERE date >='2018-01-01' 
GROUP BY date 
ORDER BY date";

$sql = mysqli_query($conn, $query);
$results = array('cols' => array(
array('label' => 'Date', 'type' => 'datetime'),
array('label' => 'Total Hours', 'type' => 'number')
),
'rows' => array()
);
while($row = mysqli_fetch_assoc($sql))
{

$dateArr = explode('-', $row['date']);
$year = (int) $dateArr[0];
$month = (int) $dateArr[1] - 1;
$day = (int) $dateArr[2];

$date = "Date($year,$month,$day)";

$results['rows'][] = array('c' => array(
array('v' => $date),
array('v' => $row['hours'])
));
}
$jsonTable = json_encode($results)
?>

javascript代码:

<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var data = new google.visualization.DataTable(<?php echo $jsonTable;?>);

var options = {
title:'Hours per Month',
legend:{position:'bottom'},
chartArea:{width:'95%', height:'65%'}
};

var chart = new google.visualization.LineChart(document.getElementById('line_chart'));

chart.draw(data, options);
}
</script>
deyfvvtc

deyfvvtc1#

通过更新select语句并从php代码中删除日期引用,我能够提取所需的数据:

$query = "SELECT SUM(day_flight_hours) AS 'hours', 
MONTHNAME(flight_date) AS 'month' FROM monthly_totals 
WHERE flight_date>='2018-01-01' GROUP BY MONTHNAME(flight_date) 
ORDER BY flight_date";

$sql = mysqli_query($conn, $query);
$rows = array();
$table = array();
$table['cols'] = array(
 array('label' => 'Month', 'type' => 'string'),
 array('label' => 'Hours', 'type' => 'number')
);

while($row = mysqli_fetch_assoc($sql)) {
$temp = array();
$temp[] = array('v' => (string) $row['month']);
$temp[] = array('v' => (int) $row['hours']);
$rows[] = array('c' => $temp);
}

$table['rows'] = $rows;
$jsonTable = json_encode($table)

相关问题