Example #1
0
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;
}