phpmysqljsongroup-byhighcharts

Group database records by hour for a given day and json-encode the results for Highcharts presentation


I'm trying to get data from a MySQL database and present it in Highcharts.

This is my query:

$sth = mysqli_query($con, "
    SELECT DateTime,max(T)
    FROM alldata
    WHERE DATE(DateTime) = CURDATE() - INTERVAL 1 DAY
    GROUP BY hour(DateTime)
");

$rows = array();
$rows['name'] = 'Outside';
while ($r = mysqli_fetch_array($sth)) {
    $rows['data'][] = $r['max(T)'];
}
$result = array();
array_push($result, $rows);

print json_encode($result, JSON_NUMERIC_CHECK);

The json output is missing the date and time

[{"name":"Outside","data":[17.5,16.3,15.6,15.1,14.4,14,14.1,16,18.5,21.7,24.1,26.9,28.3,29.6,30.6,31.1,31.8]}]

The graph shows the data correctly except the datetime on the x axis and I cannot figure how to fix it.


Solution

  • Changed the query to this and now it works.

    Thanks wergeld and Yuri for the assistance.

         $sth = mysqli_query($con,"
            SELECT DateTime,max(T)
            FROM davisvp
            WHERE DATE(DateTime) = CURDATE()
            GROUP BY hour(DateTime)
           "
          );
          $result = array();
          $result['name'] = 'temperature';
          while($row = mysqli_fetch_array($sth))
          {
            $date = strtotime($row['DateTime']);
            $maxt = 1 * $row['max(T)'];
            $result1 = array();
            array_push($result1,$date);
            array_push($result1,$maxt);
            $result['data'][] = $result1;
          }
          echo json_encode($result, JSON_NUMERIC_CHECK);