英文:
PHP SQL Query into a chart.js bar chart - Issue with the array
问题
以下是翻译好的部分：
"I'm successfully getting the sql data into an array, what I can't seem to work out is how to get that array to show in a chart.js chart. I can get the array into the dataset section but for some reason it isn't rendered? The chart.js code is as-is off the website so excuse the default bits and pieces, I aim to tweak those once I have the data displaying OK."
我的代码成功将SQL数据存入数组中，但我似乎无法弄清楚如何在chart.js图表中显示该数组。我可以将数组放入数据集部分，但出现某种原因，它没有渲染？ chart.js代码如网站上所示，所以请原谅其中的默认部分，我打算在数据显示正常后进行调整。
"A page view of the results shows the array inside the dataset section:"
结果的页面视图显示数据数组位于数据集部分内。
"Can anyone throw any pointers to where this is going wrong? Thanx"
有人可以指出这出了什么问题吗？谢谢
"My final solution to this following the advice was:"
根据建议，我对此的最终解决方案是：
英文:
I'm successfully getting the sql data into an array, what I can't seem to work out is how to get that array to show in a chart.js chart. I can get the array into the dataset section but for some reason it isn't rendered? The chart.js code is as-is off the website so excuse the default bits and pieces, I aim to tweak those once I have the data displaying OK.
My code is as follows;
<?php//print_r($_POST);$month = $_POST["month"];$year = $_POST["year"];$type = $_POST["type"];//can build sql strings in this ifelse blockif ($type == "both") {$sql = "SELECT * FROM solar_generation WHERE MONTH(timestamp)= $month AND YEAR(timestamp) = $year";} elseif ($type == "generation") {$sql = "SELECT timestamp, generation FROM solar_generation WHERE MONTH(timestamp)= $month AND YEAR(timestamp) = $year";} else{$sql = "SELECT timestamp, export FROM solar_generation WHERE MONTH(timestamp)= $month AND YEAR(timestamp) = $year";}echo $sql;$host = "*******";$dbname = "solar_generation";$username = "*******";$password = "*******";$connection = mysqli_connect(hostname: $host,username: $username,password: $password,database: $dbname);if (mysqli_connect_errno()) {die("Connection error: " . mysqli_connect_error());}//Do stuff heretry{$result = mysqli_query($connection, "$sql");echo "Returned rows are: " . $result -> num_rows;print("Result of the SELECT query: ");print_r($result);}catch (Exception $e) {echo 'Caught exception: ', $e->getMessage(), "\n";}$dataPoints = array();if ($result->num_rows > 0) {while ($row = $result->fetch_assoc()) {$dataPoints[] = $row;}}mysqli_close($connection);?><div><canvas id="myChart"></canvas></div><script src="https://cdn.jsdelivr.net/npm/chart.js"></script><script>const ctx = document.getElementById('myChart');new Chart(ctx, {type: 'bar',data: {labels: ['Red', 'Blue', 'Yellow', 'Green', 'Purple', 'Orange'],datasets: [{label: 'Testing',data: [<?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>],borderWidth: 1}]},options: {scales: {y: {beginAtZero: true}}}});</script>
A page view of the results shows the array inside the dataset section:
data: [[{"timestamp":"2023-07-04 23:59:02","generation":0},{"timestamp":"2023-07-05 23:59:02","generation":26},{"timestamp":"2023-07-06 23:59:02","generation":28},{"timestamp":"2023-07-07 23:59:02","generation":43},{"timestamp":"2023-07-08 23:59:02","generation":20},{"timestamp":"2023-07-09 23:59:02","generation":32},{"timestamp":"2023-07-11 23:59:02","generation":22},{"timestamp":"2023-07-12 23:59:02","generation":35},{"timestamp":"2023-07-14 23:59:02","generation":8},{"timestamp":"2023-07-15 23:59:02","generation":33},{"timestamp":"2023-07-16 23:59:02","generation":28},{"timestamp":"2023-07-17 16:23:31","generation":35.4},{"timestamp":"2023-07-18 23:00:00","generation":16.2},{"timestamp":"2023-07-19 23:00:00","generation":28},{"timestamp":"2023-07-20 23:00:00","generation":23.9},{"timestamp":"2023-07-21 23:00:00","generation":21.7},{"timestamp":"2023-07-22 23:00:02","generation":14.4}]],
Can anyone throw any pointers to where this is going wrong?
Thanx
My final solution to this following the advice was:
<div><canvas id="myChart" style="height:300px"></canvas></div><script src="https://cdn.jsdelivr.net/npm/chart.js"></script><script>const ctx = document.getElementById('myChart');new Chart(ctx, {type: 'bar',data: {datasets: [{label: 'Solar - Generated kWh',data: <?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>,borderColor: '#000',backgroundColor: '#FFCC00',borderWidth: 1,parsing: {yAxisKey: 'generation'}},{label: 'Solar Exported kWh',data: <?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>,borderColor: '#000',backgroundColor: '#ED7014',borderWidth: 1,parsing: {yAxisKey: 'export'}}]},options: {parsing: {xAxisKey: 'timestamp',},scales: {y: {beginAtZero: true}}}});
答案1
得分: 0
为了解决这个问题，您应该修改PHP代码，直接分配$dataPoints数组，而不添加额外的嵌套级别。以下是更新后的PHP代码：
$dataPoints = array();if ($result->num_rows > 0) {while ($row = $result->fetch_assoc()) {$dataPoints[] = $row['generation']; // 假设'generation'是您想在图表中显示的数据点}}
英文:
To resolve this, you should modify the PHP code to directly assign the $dataPoints array without adding an extra level of nesting. Here's the updated PHP code:
$dataPoints = array();if ($result->num_rows > 0) {while ($row = $result->fetch_assoc()) {$dataPoints[] = $row['generation']; // Assuming 'generation' is the data point you want to show in the chart}}</details># 答案2**得分**: 0要*开始*查看您的数据，您需要执行以下操作：- 删除围绕数据生成的 PHP 的方括号，或者使用其他方法删除输出中的一个方括号层级（js 数组文字） - `data` 应该是一个对象数组，而不是一个对象数组的数组。```lang-phpdata: <?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>,```- 删除 `labels` 数组- 添加 `options.parsing.xAxisKey: 'timestamp'`，`options.parsing.yAxisKey: 'generation'`，以指示哪个属性应该放在 x 轴上，哪个属性应该放在 y 轴上 [文档链接](https://www.chartjs.org/docs/latest/general/data-structures.html#object-using-custom-properties)。有了这些，您将会得到类似以下的效果：```lang-jsconst ctx = document.getElementById('myChart');new Chart(ctx, {type: 'bar',data: {//labels: ['Red', 'Blue', 'Yellow', 'Green', 'Purple', 'Orange'],datasets: [{label: 'Testing',data: [{"timestamp":"2023-07-04 23:59:02","generation":0},{"timestamp":"2023-07-05 23:59:02","generation":26},{"timestamp":"2023-07-06 23:59:02","generation":28},{"timestamp":"2023-07-07 23:59:02","generation":43},{"timestamp":"2023-07-08 23:59:02","generation":20},{"timestamp":"2023-07-09 23:59:02","generation":32},{"timestamp":"2023-07-11 23:59:02","generation":22},{"timestamp":"2023-07-12 23:59:02","generation":35},{"timestamp":"2023-07-14 23:59:02","generation":8},{"timestamp":"2023-07-15 23:59:02","generation":33},{"timestamp":"2023-07-16 23:59:02","generation":28},{"timestamp":"2023-07-17 16:23:31","generation":35.4},{"timestamp":"2023-07-18 23:00:00","generation":16.2},{"timestamp":"2023-07-19 23:00:00","generation":28},{"timestamp":"2023-07-20 23:00:00","generation":23.9},{"timestamp":"2023-07-21 23:00:00","generation":21.7},{"timestamp":"2023-07-22 23:00:02","generation":14.4}],borderWidth: 1}]},options: {parsing: {xAxisKey: 'timestamp',yAxisKey: 'generation'},scales: {y: {beginAtZero: true}}}});``````lang-html<canvas id="myChart" style="height:500px"></canvas><script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/4.3.0/chart.umd.js" integrity="sha512-CMF3tQtjOoOJoOKlsS7/2loJlkyctwzSoDK/S40iAB+MqWSaf50uObGQSk5Ny/gfRhRCjNLvoxuCvdnERU4WGg==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>```<details><summary>英文:</summary>To *start* seeing your data, you have to do the following:- drop the square parentheses around data generating php, or use some other method to drop one level of square parentheses (js array literal) in the output - `data` should be an array of objects, not an array of arrays of objects.```lang-phpdata: <?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>,```- delete the `labels` array- add `options.parsing.xAxisKey: 'timestamp'`, `options.parsing.yAxisKey: 'generation'`to indicate which property should go to the x axis and which one to the y axis [doc link](https://www.chartjs.org/docs/latest/general/data-structures.html#object-using-custom-properties).With these, you'll get something like:<!-- begin snippet: js hide: false console: false babel: false --><!-- language: lang-js -->const ctx = document.getElementById('myChart');new Chart(ctx, {type: 'bar',data: {//labels: ['Red', 'Blue', 'Yellow', 'Green', 'Purple', 'Orange'],datasets: [{label: 'Testing',data: [{"timestamp":"2023-07-04 23:59:02","generation":0},{"timestamp":"2023-07-05 23:59:02","generation":26},{"timestamp":"2023-07-06 23:59:02","generation":28},{"timestamp":"2023-07-07 23:59:02","generation":43},{"timestamp":"2023-07-08 23:59:02","generation":20},{"timestamp":"2023-07-09 23:59:02","generation":32},{"timestamp":"2023-07-11 23:59:02","generation":22},{"timestamp":"2023-07-12 23:59:02","generation":35},{"timestamp":"2023-07-14 23:59:02","generation":8},{"timestamp":"2023-07-15 23:59:02","generation":33},{"timestamp":"2023-07-16 23:59:02","generation":28},{"timestamp":"2023-07-17 16:23:31","generation":35.4},{"timestamp":"2023-07-18 23:00:00","generation":16.2},{"timestamp":"2023-07-19 23:00:00","generation":28},{"timestamp":"2023-07-20 23:00:00","generation":23.9},{"timestamp":"2023-07-21 23:00:00","generation":21.7},{"timestamp":"2023-07-22 23:00:02","generation":14.4}],borderWidth: 1}]},options: {parsing: {xAxisKey: 'timestamp',yAxisKey: 'generation'},scales: {y: {beginAtZero: true}}}});<!-- language: lang-html --><canvas id="myChart" style="height:500px"></canvas><script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/4.3.0/chart.umd.js" integrity="sha512-CMF3tQtjOoOJoOKlsS7/2loJlkyctwzSoDK/S40iAB+MqWSaf50uObGQSk5Ny/gfRhRCjNLvoxuCvdnERU4WGg==" crossorigin="anonymous" referrerpolicy="no-referrer"></script><!-- end snippet --></details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论