63 lines
No EOL
1.4 KiB
PHP
63 lines
No EOL
1.4 KiB
PHP
<?php
|
|
|
|
$servername = "localhost";
|
|
$username = "svcPHPAdmin";
|
|
$password = "tgC3*&tghlRc";
|
|
$dbname = "sensor";
|
|
|
|
$jsonlabel = array();
|
|
$jsontemp = array();
|
|
$jsonhumidity = array();
|
|
$Series = array();
|
|
|
|
// Run the query
|
|
|
|
$conn = mysqli_connect($servername, $username, $password, $dbname);
|
|
$sql = "SELECT CONCAT_WS('',thour,':00') as labels, avgTemp
|
|
FROM (
|
|
SELECT *
|
|
FROM (
|
|
SELECT @curRow := @curRow + 1 AS row_number,
|
|
DATE_FORMAT(`CREATED_DATE`,'%b-%e') as tdate,
|
|
DAY(`CREATED_DATE`) AS tday,
|
|
HOUR(`CREATED_DATE`) as thour,
|
|
ROUND(AVG(`RECORDED_TEMP`),1) as avgTemp,
|
|
MIN(`RECORDED_TEMP`) as tmintemp,
|
|
MAX(`RECORDED_TEMP`) as tmaxtemp
|
|
FROM
|
|
(
|
|
SELECT CREATED_DATE,VALUE_1 AS RECORDED_TEMP
|
|
FROM `tblSENSOR_DATA`
|
|
WHERE (CREATED_DATE >= (CURRENT_DATE - INTERVAL 24 HOUR)) AND (SCODE = 'RPI4-A')
|
|
) AS X, (SELECT @curRow := 0) r
|
|
GROUP BY tdate, tday, thour
|
|
) as A
|
|
ORDER BY row_number DESC
|
|
LIMIT 24
|
|
) AS B
|
|
ORDER BY row_number ASC";
|
|
|
|
$result = mysqli_query($conn,$sql);
|
|
|
|
//$json_array = array();
|
|
|
|
while($row = mysqli_fetch_assoc($result))
|
|
{
|
|
$jsonlabel[] = $row["labels"];
|
|
$jsontemp[] = (float)$row["avgTemp"];
|
|
}
|
|
|
|
$Series[]=$jsontemp;
|
|
$Series[]=$jsonhumidity;
|
|
/*
|
|
echo "<PRE>";
|
|
print_r ($jsontemp);
|
|
print_r ($jsonhumidity);
|
|
echo "</PRE>";
|
|
*/
|
|
$myObj->labels = $jsonlabel;
|
|
$myObj->series = $Series;
|
|
|
|
echo json_encode($myObj);
|
|
|
|
?>
|