PHP SQL查询到chart.js条形图-数组问题

我正在成功地将sql数据放入一个数组中,但我似乎不知道如何将该数组显示在chart.js图表中。我可以将数组放入数据集部分,但由于某种原因,它没有被渲染?chart.js的代码是网站上没有的,所以请原谅默认的部分,我的目标是在数据显示正常后对它们进行调整。

我的代码如下所示;

<?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>

结果的页面视图显示了数据集部分内的数组:

        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}]],

有人能指出哪里出了问题吗? Thanx

根据建议,我的最终解决方案是:

<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
            }
        }
    }
});