function dbQuery($device, $reading, $timeRange, $db) { global $timestampColumn; global $valueColumn; global $unitColumn; global $readingColumn; global $deviceColumn; global $logTable; $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $timeRange = mktime() - $timeRange * 60; $timeRange = date("Y-m-d H:i:s", $timeRange); // Check if request is initial request or update if (isset($_POST['update'])) { // Select only the last entry to get the update data for the plot $maxCount = 1; $dbQuery = 'SELECT ' . $timestampColumn . ', ' . $valueColumn . ', ' . $unitColumn . ' FROM ' . $logTable . ' WHERE ' . $deviceColumn . '=:device AND ' . $readingColumn . '=:reading ORDER BY ' . $timestampColumn . ' DESC LIMIT 0,:count'; $countQuery = 'SELECT count(*) FROM ' . $logTable . ' WHERE ' . $deviceColumn . '=:device AND ' . $readingColumn . '=:reading ORDER BY ' . $timestampColumn . ' DESC LIMIT 0,:count'; } else { // Check if max_entry_count var is set in POST request, defaults to 300 $maxCount = isset($_POST['maxRows']) ? $_POST['maxRows'] : 300; $dbQuery = 'SELECT ' . $timestampColumn . ', ' . $valueColumn . ', ' . $unitColumn . ' FROM ' . $logTable . ' WHERE ' . $deviceColumn . '=:device AND ' . $readingColumn . '=:reading AND ' . $timestampColumn . ' > :timeRange ORDER BY ' . $timestampColumn . ' DESC LIMIT 0,:count'; $countQuery = 'SELECT count(*) FROM ' . $logTable . ' WHERE ' . $deviceColumn . '=:device AND ' . $readingColumn . '=:reading AND ' . $timestampColumn . ' > :timeRange ORDER BY ' . $timestampColumn . ' DESC LIMIT 0,:count'; } // Check for number of rows returned, if there are zero rows, return error with sql query $rowCountQuery = executeDbQuery($db, $countQuery, $device, $reading, $timeRange, $maxCount); $numRowCount = $rowCountQuery->fetchColumn(); if ($numRowCount == 0) { returnError('Zero rows returned for Device: ' . $device . ' with Reading: ' . $reading); } // Execute query and return fetched rows $fetchedRows = executeDbQuery($db, $dbQuery, $device, $reading, $timeRange, $maxCount); return array($fetchedRows, $numRowCount); }
function getData($device, $reading, $timestampStart, $timestampEnd, $maxCount, $db) { global $timestampColumn; global $valueColumn; global $unitColumn; global $readingColumn; global $deviceColumn; global $logTable; $datetimeStart = date('Y-m-d H:i:s', $timestampStart); $datetimeEnd = date('Y-m-d H:i:s', $timestampEnd); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $dbQuery = 'SELECT ' . $timestampColumn . ', ' . $valueColumn . ', ' . $unitColumn . ' FROM ' . $logTable . ' WHERE ' . $deviceColumn . '=:device AND ' . $readingColumn . '=:reading AND ' . $timestampColumn . ' BETWEEN :timeRangeStart AND :timeRangeEnd ORDER BY ' . $timestampColumn . ' DESC LIMIT 0,:count'; // Execute query and return fetched rows $fetchedRows = executeDbQuery($db, $dbQuery, $device, $reading, $datetimeStart, $datetimeEnd, $maxCount); return $fetchedRows; }