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
2
Answers
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:
To start seeing your data, you have to do the following:
datashould be an array of objects, not an array of arrays of objects.labelsarrayoptions.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.
With these, you’ll get something like: