根据php和sql server中的日期时间从表中查找活动记录

Question

I need to fetch active records from my table. A record is active means it is not expired and the expiration time is 2 minutes after record is generated. I am using sql server database. Here is the structure for my table

And my code is as follows

$serverName = "xxx.xx.x.xxx";
$connectionInfo = array( "Database"=>"xxxxxx", "UID"=>"xxxxx", "PWD"=>"xxxxxx");
$conn = sqlsrv_connect($serverName, $connectionInfo);   
if($conn)
    echo 'success';
else 
    echo "failed";

$currentTime = date('Y-m-d H:i:s');
$query = "SELECT * FROM ApiTockenMaster WHERE Tocken = ? AND DateGenerated <= ? AND Status = ?";
$params = array("xxxxxxx", "2018-09-03 18:06:17.7600000", "Generated");
$result = sqlsrv_query( $conn, $query, $params);
$row = sqlsrv_fetch_array($result);
echo '<pre>'; print_r($row);
echo count($row);

I need the condition for DateGenerated as

currenttime <= DateGenerated + 2 minutes

How can I implement this condition in my query

Answer 1

Another possible approach is to let the SQL Server do the check using CURRENT_TIMESTAMP and DATEADD():

<?php
# Connection
$serverName = "xxx.xx.x.xxx";
$connectionInfo = array(
    "Database"=>"xxxxxx", 
    "UID"=>"xxxxx", 
    "PWD"=>"xxxxxx"
);
$conn = sqlsrv_connect($serverName, $connectionInfo);   
if ($conn) {
    echo "Connection established.<br />";
} else {
    echo "Connection could not be established.<br />";
    die(print_r(sqlsrv_errors(), true));
}

# Statement
$query = "
    SELECT * 
    FROM ApiTockenMaster 
    WHERE 
        (Tocken = ?) AND 
        (CURRENT_TIMESTAMP <= DATEADD(mi, 2, DateGenerated)) AND 
        (Status = ?)
";
$params = array(
    "xxxxxxx", 
    "Generated"
);
$result = sqlsrv_query($conn, $query, $params);
if ($result === false){
    die(print_r(sqlsrv_errors(), true));
}

# Result
$row = sqlsrv_fetch_array($result);
echo '<pre>'; 
print_r($row);
echo count($row);
?>