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
Advertisement
Answer
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); ?>