英文:
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 block
if ($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 here
try{
$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-php
data: <?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-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
}
}
}
});
```
```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-php
data: <?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>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论