我正在努力在 Google 折線圖中使用 PHP 顯示 MySQL 查詢。問(wèn)題是折線圖未顯示。我將感謝任何能夠?yàn)槲抑该髡_方向的人。以下 MySQL 查詢是我試圖在折線圖中表示的內(nèi)容：SELECT DATE(`VISIT_TIME`) AS `Date`, SUM(IF(HOUR(`VISIT_TIME`) = 0, 1, 0)) AS `TwelveAM`, SUM(IF(HOUR(`VISIT_TIME`) = 1, 1, 0)) AS `OneAM`, SUM(IF(HOUR(`VISIT_TIME`) = 2, 1, 0)) AS `TwoAM`, SUM(IF(HOUR(`VISIT_TIME`) = 3, 1, 0)) AS `ThreeAM`, SUM(IF(HOUR(`VISIT_TIME`) = 4, 1, 0)) AS `FourAM`, SUM(IF(HOUR(`VISIT_TIME`) = 5, 1, 0)) AS `FiveAM`, SUM(IF(HOUR(`VISIT_TIME`) = 6, 1, 0)) AS `SixAM`, SUM(IF(HOUR(`VISIT_TIME`) = 7, 1, 0)) AS `SevenAM`, SUM(IF(HOUR(`VISIT_TIME`) = 8, 1, 0)) AS `EightAM`, SUM(IF(HOUR(`VISIT_TIME`) = 9, 1, 0)) AS `NineAM`, SUM(IF(HOUR(`VISIT_TIME`) = 10, 1, 0)) AS `TenAM`, SUM(IF(HOUR(`VISIT_TIME`) = 11, 1, 0)) AS `ElevenAM`, SUM(IF(HOUR(`VISIT_TIME`) = 12, 1, 0)) AS `TwelvePM`, SUM(IF(HOUR(`VISIT_TIME`) = 13, 1, 0)) AS `OnePM`, SUM(IF(HOUR(`VISIT_TIME`) = 14, 1, 0)) AS `TwoPM`, SUM(IF(HOUR(`VISIT_TIME`) = 15, 1, 0)) AS `ThreePM`, SUM(IF(HOUR(`VISIT_TIME`) = 16, 1, 0)) AS `FourPM`, SUM(IF(HOUR(`VISIT_TIME`) = 17, 1, 0)) AS `FivePM`, SUM(IF(HOUR(`VISIT_TIME`) = 18, 1, 0)) AS `SixPM`, SUM(IF(HOUR(`VISIT_TIME`) = 19, 1, 0)) AS `SevenPM`, SUM(IF(HOUR(`VISIT_TIME`) = 20, 1, 0)) AS `EightPM`, SUM(IF(HOUR(`VISIT_TIME`) = 21, 1, 0)) AS `NinePM`, SUM(IF(HOUR(`VISIT_TIME`) = 22, 1, 0)) AS `TenPM`, SUM(IF(HOUR(`VISIT_TIME`) = 23, 1, 0)) AS `ElevenPM`FROM `jurl_analytics`WHERE `URL_ID` = 'westonsupermare'GROUP BY DATE(`VISIT_TIME`)LIMIT 7以下查詢顯示過(guò)去 7 天內(nèi)每小時(shí)的請(qǐng)求數(shù)。https://i.stack.imgur.com/SKN7v.png 我希望在 Google 折線圖中表示這些數(shù)據(jù)，如下圖所示：https://i.stack.imgur.com/svqqi.png 這是我嘗試過(guò)的： //如果您決定創(chuàng)建數(shù)據(jù)庫(kù)，請(qǐng)?zhí)顚?xiě)服務(wù)器、用戶名、密碼和數(shù)據(jù)庫(kù)字段。
1 回答
眼眸繁星
TA貢獻(xiàn)1873條經(jīng)驗(yàn) 獲得超9個(gè)贊
這里有一些問(wèn)題。
首先，列標(biāo)題中有 25 個(gè)值...
['Date', 'TwelveAM', 'OneAM', 'TwoAM', ...
但行數(shù)據(jù)中有 26 個(gè)值...
echo "['".$result['Date']."',".$result['TwelveAM']."','".$result['OneAM']."' ...
有兩個(gè)TwelvePM值，我們將刪除最后一個(gè)值，
因?yàn)樗c最后一個(gè)列標(biāo)題不匹配。
接下來(lái)，正如您在評(píng)論中提到的，逗號(hào)和撇號(hào)的數(shù)量
可能會(huì)令人困惑。 這是您不應(yīng)嘗試手動(dòng)構(gòu)建 JSON 的原因之一。
相反，在 PHP 中構(gòu)建數(shù)組，然后在寫(xiě)入頁(yè)面時(shí)對(duì) JSON 進(jìn)行編碼。
<?php
$data = array();
$data[] = ['Date', 'TwelveAM', 'OneAM', 'TwoAM', 'ThreeAM', 'FourAM', 'FiveAM', 'SixAM', '7AM', '8AM', '9AM', '10AM', '11AM', '12PM', '1PM', '2PM', '3PM', '4PM', '5PM', '6PM', '7PM', '8PM', '9PM', '10PM', '11PM'];
$fire = mysqli_query($connect, $sevenDaysHours);
while($result = mysqli_fetch_assoc($fire))
{
$data[] = [$result['Date'], $result['TwelveAM'], $result['OneAM'], $result['TwoAM'], $result['ThreeAM'], $result['FourAM'], $result['FiveAM'], $result['SixAM'], $result['SevenAM'], $result['EightAM'], $result['NineAM'], $result['TenAM'], $result['ElevenAM'], $result['TwelvePM'], $result['OnePM'], $result['TwoPM'], $result['ThreePM'], $result['FourPM'], $result['FivePM'], $result['SixPM'], $result['SevenPM'], $result['EightPM'], $result['NinePM'], $result['TenPM'], $result['ElevenPM']];
}
?>
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable(<?php echo json_encode($data); ?>);
var options = {
title: 'Company Performance',
curveType: 'function',
legend: { position: 'bottom' }
};
var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
chart.draw(data, options);
}
這是您正在尋找的部分...
<?php
$server = "";
$username = "";
$password = "";
$database = "";
$connect = mysqli_connect($server,$username,$password,$database);
if ($connect)
{
echo "You are connected!". "<br>";
}
function runAndCheckSQL($connection, $sql){
$run = mysqli_query($connection, $sql);
if ($run) {
if(is_array($run) || is_object($run)){
return $run;
}else{
return true;
}
} else {
die(showError($sql, $connection));
}
}
$sevenDaysHours = "SELECT
DATE(`VISIT_TIME`) AS `Date`,
SUM(IF(HOUR(`VISIT_TIME`) = 0,
1,
0)) AS `TwelveAM`,
SUM(IF(HOUR(`VISIT_TIME`) = 1,
1,
0)) AS `OneAM`,
SUM(IF(HOUR(`VISIT_TIME`) = 2,
1,
0)) AS `TwoAM`,
SUM(IF(HOUR(`VISIT_TIME`) = 3,
1,
0)) AS `ThreeAM`,
SUM(IF(HOUR(`VISIT_TIME`) = 4,
1,
0)) AS `FourAM`,
SUM(IF(HOUR(`VISIT_TIME`) = 5,
1,
0)) AS `FiveAM`,
SUM(IF(HOUR(`VISIT_TIME`) = 6,
1,
0)) AS `SixAM`,
SUM(IF(HOUR(`VISIT_TIME`) = 7,
1,
0)) AS `SevenAM`,
SUM(IF(HOUR(`VISIT_TIME`) = 8,
1,
0)) AS `EightAM`,
SUM(IF(HOUR(`VISIT_TIME`) = 9,
1,
0)) AS `NineAM`,
SUM(IF(HOUR(`VISIT_TIME`) = 10,
1,
0)) AS `TenAM`,
SUM(IF(HOUR(`VISIT_TIME`) = 11,
1,
0)) AS `ElevenAM`,
SUM(IF(HOUR(`VISIT_TIME`) = 12,
1,
0)) AS `TwelvePM`,
SUM(IF(HOUR(`VISIT_TIME`) = 13,
1,
0)) AS `OnePM`,
SUM(IF(HOUR(`VISIT_TIME`) = 14,
1,
0)) AS `TwoPM`,
SUM(IF(HOUR(`VISIT_TIME`) = 15,
1,
0)) AS `ThreePM`,
SUM(IF(HOUR(`VISIT_TIME`) = 16,
1,
0)) AS `FourPM`,
SUM(IF(HOUR(`VISIT_TIME`) = 17,
1,
0)) AS `FivePM`,
SUM(IF(HOUR(`VISIT_TIME`) = 18,
1,
0)) AS `SixPM`,
SUM(IF(HOUR(`VISIT_TIME`) = 19,
1,
0)) AS `SevenPM`,
SUM(IF(HOUR(`VISIT_TIME`) = 20,
1,
0)) AS `EightPM`,
SUM(IF(HOUR(`VISIT_TIME`) = 21,
1,
0)) AS `NinePM`,
SUM(IF(HOUR(`VISIT_TIME`) = 22,
1,
0)) AS `TenPM`,
SUM(IF(HOUR(`VISIT_TIME`) = 23,
1,
0)) AS `ElevenPM`
FROM
`jurl_analytics`
WHERE
`URL_ID` = 'westonsupermare'
GROUP BY
DATE(`VISIT_TIME`)
LIMIT 7"
?>
<html>
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
<?php
$data = array();
$data[] = ['Date', 'TwelveAM', 'OneAM', 'TwoAM', 'ThreeAM', 'FourAM', 'FiveAM', 'SixAM', '7AM', '8AM', '9AM', '10AM', '11AM', '12PM', '1PM', '2PM', '3PM', '4PM', '5PM', '6PM', '7PM', '8PM', '9PM', '10PM', '11PM'];
$fire = mysqli_query($connect, $sevenDaysHours);
while($result = mysqli_fetch_assoc($fire))
{
$data[] = [$result['Date'], $result['TwelveAM'], $result['OneAM'], $result['TwoAM'], $result['ThreeAM'], $result['FourAM'], $result['FiveAM'], $result['SixAM'], $result['SevenAM'], $result['EightAM'], $result['NineAM'], $result['TenAM'], $result['ElevenAM'], $result['TwelvePM'], $result['OnePM'], $result['TwoPM'], $result['ThreePM'], $result['FourPM'], $result['FivePM'], $result['SixPM'], $result['SevenPM'], $result['EightPM'], $result['NinePM'], $result['TenPM'], $result['ElevenPM']];
}
?>
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable(<?php echo json_encode($data); ?>);
var options = {
title: 'Company Performance',
curveType: 'function',
legend: { position: 'bottom' }
};
var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
chart.draw(data, options);
}
</script>
</head>
<body>
<div id="curve_chart" style="width: 900px; height: 500px"></div>
</body>
</html>
編輯
如果數(shù)字值在 PHP 中表現(xiàn)為字符串，
則嘗試將JSON_NUMERIC_CHECK參數(shù)添加到編碼語(yǔ)句中。
<?php echo json_encode($data, JSON_NUMERIC_CHECK); ?>
讓我們將 JSON 數(shù)據(jù)分配給它自己的變量。
然后我們可以將結(jié)果發(fā)送到控制臺(tái)進(jìn)行檢查。
如果仍然不起作用，請(qǐng)分享控制臺(tái)的示例。
<?php
$data = array();
$data[] = ['Date', 'TwelveAM', 'OneAM', 'TwoAM', 'ThreeAM', 'FourAM', 'FiveAM', 'SixAM', '7AM', '8AM', '9AM', '10AM', '11AM', '12PM', '1PM', '2PM', '3PM', '4PM', '5PM', '6PM', '7PM', '8PM', '9PM', '10PM', '11PM'];
$fire = mysqli_query($connect, $sevenDaysHours);
while($result = mysqli_fetch_assoc($fire))
{
$data[] = [$result['Date'], $result['TwelveAM'], $result['OneAM'], $result['TwoAM'], $result['ThreeAM'], $result['FourAM'], $result['FiveAM'], $result['SixAM'], $result['SevenAM'], $result['EightAM'], $result['NineAM'], $result['TenAM'], $result['ElevenAM'], $result['TwelvePM'], $result['OnePM'], $result['TwoPM'], $result['ThreePM'], $result['FourPM'], $result['FivePM'], $result['SixPM'], $result['SevenPM'], $result['EightPM'], $result['NinePM'], $result['TenPM'], $result['ElevenPM']];
}
?>
var jsonData = <?php echo json_encode($data, JSON_NUMERIC_CHECK); ?>;
console.log(JSON.stringify(jsonData));
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable(jsonData);
var options = {
title: 'Company Performance',
curveType: 'function',
legend: { position: 'bottom' }
};
var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
chart.draw(data, options);
}
編輯2
為了獲得所需的圖表，
我們需要將列換成行，反之亦然。
這很可能很難通過(guò)更改查詢來(lái)實(shí)現(xiàn)。
但我們可以輕松地用 JavaScript 創(chuàng)建一個(gè)新的數(shù)據(jù)表。
所以，幾乎讓一切保持不變。
除了我們將添加以下例程來(lái)創(chuàng)建一個(gè)新的數(shù)據(jù)表，
并交換列和行。
然后使用新的數(shù)據(jù)表繪制圖表。
var jsonData = <?php echo json_encode($data, JSON_NUMERIC_CHECK); ?>;
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable(jsonData);
// create new data table with columns and rows swapped
var dataHours = new google.visualization.DataTable();
dataHours.addColumn('number', 'Hour');
for (var row = 0; row < data.getNumberOfRows(); row++) {
var dateColumn = dataHours.addColumn('number', data.getValue(row, 0));
for (var col = 1; col < data.getNumberOfColumns(); col++) {
var dateRow = dataHours.addRow();
dataHours.setValue(dateRow, 0, col);
dataHours.setValue(dateRow, dateColumn, data.getValue(row, col));
}
}
var options = {
title: 'Company Performance',
curveType: 'function',
legend: { position: 'bottom' }
};
var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
chart.draw(dataHours, options); // <-- draw chart with new data table
}
以下是一個(gè)工作示例，顯示了交換之前和之后......
google.charts.load('current', {
packages:['corechart']
}).then(function () {
var data = google.visualization.arrayToDataTable([
['Date', 'TwelveAM', 'OneAM', 'TwoAM', 'ThreeAM', 'FourAM', 'FiveAM', 'SixAM', '7AM', '8AM', '9AM', '10AM', '11AM', '12PM', '1PM', '2PM', '3PM', '4PM', '5PM', '6PM', '7PM', '8PM', '9PM', '10PM', '11PM'],
['2020-08-14', 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25],
['2020-08-15', 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26, 28, 30, 32, 34, 36, 38, 40, 42, 44, 46, 48, 50],
['2020-08-16', 6, 9, 12, 15, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45, 48, 51, 54, 57, 60, 63, 66, 69, 72, 75],
['2020-08-17', 8, 12, 16, 20, 24, 28, 32, 36, 40, 44, 48, 52, 56, 60, 64, 68, 72, 76, 80, 84, 88, 92, 96, 100]
]);
// create new data table with columns and rows swapped
var dataHours = new google.visualization.DataTable();
dataHours.addColumn('number', 'Hour');
for (var row = 0; row < data.getNumberOfRows(); row++) {
var dateColumn = dataHours.addColumn('number', data.getValue(row, 0));
for (var col = 1; col < data.getNumberOfColumns(); col++) {
var dateRow = dataHours.addRow();
dataHours.setValue(dateRow, 0, col);
dataHours.setValue(dateRow, dateColumn, data.getValue(row, col));
}
}
var options = {
chartArea: {
left: 64,
top: 48,
right: 32,
bottom: 64,
height: '100%',
width: '100%'
},
height: '100%',
legend: {
alignment: 'center',
position: 'bottom'
},
width: '100%'
};
var chartBefore = new google.visualization.LineChart(document.getElementById('chart_before'));
chartBefore.draw(data, options);
var chartAfter = new google.visualization.LineChart(document.getElementById('chart_after'));
chartAfter.draw(dataHours, options);
});
.chart {
height: 260px;
}
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div class="chart" id="chart_before"></div>
<div class="chart" id="chart_after"></div>
- 1 回答
- 0 關(guān)注
- 137 瀏覽
添加回答
舉報(bào)
0/150
提交
取消