Google 折线图不显示使用 PHP 的 MySQL 查询
我正在努力在 Google 折线图中使用 PHP 显示 MySQL 查询。问题是折线图未显示。我将感谢任何能够为我指明正确方向的人。以下 MySQL 查询是我试图在折线图中表示的内容：
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
以下查询显示过去 7 天内每小时的请求数。
https://i.stack.imgur.com/SKN7v.png
我希望在 Google 折线图中表示这些数据，如下图所示：
https://i.stack.imgur.com/svqqi.png
这是我尝试过的： //如果您决定创建数据库，请填写服务器、用户名、密码和数据库字段。
守候你守候我浏览 99回答 1
1回答
-
眼眸繁星
这里有一些问题。首先，列标题中有 25 个值...['Date', 'TwelveAM', 'OneAM', 'TwoAM', ...但行数据中有 26 个值...echo "['".$result['Date']."',".$result['TwelveAM']."','".$result['OneAM']."' ...有两个TwelvePM值，我们将删除最后一个值，因为它与最后一个列标题不匹配。接下来，正如您在评论中提到的，逗号和撇号的数量可能会令人困惑。 这是您不应尝试手动构建 JSON 的原因之一。相反，在 PHP 中构建数组，然后在写入页面时对 JSON 进行编码。<?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>编辑如果数字值在 PHP 中表现为字符串，则尝试将JSON_NUMERIC_CHECK参数添加到编码语句中。<?php echo json_encode($data, JSON_NUMERIC_CHECK); ?>让我们将 JSON 数据分配给它自己的变量。然后我们可以将结果发送到控制台进行检查。如果仍然不起作用，请分享控制台的示例。<?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为了获得所需的图表，我们需要将列换成行，反之亦然。这很可能很难通过更改查询来实现。但我们可以轻松地用 JavaScript 创建一个新的数据表。所以，几乎让一切保持不变。除了我们将添加以下例程来创建一个新的数据表，并交换列和行。然后使用新的数据表绘制图表。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}以下是一个工作示例，显示了交换之前和之后......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>
随时随地看视频慕课网APP
PHP
