CODEWITHSUNDEEP
phpsql
bktff
bktff

Reputation: 11

Compare Date in sql table to current date

I am working on a project in which the dates in the sql table needs to be checked with current date. If the ticket goes past the current date, then the status of ticket go from Active to Expired.
I am not good at php. This is what I came up with. I wrote this function at top of the page so that each time the page loads, it checks for the date and compares. date format is yyyy-mm-dd.
What am I doing wrong. Can anyone please help me out?

   $result= "SELECT date, status FROM TABLE1";

   while($row = sqlsrv_fetch_array($result)){

   if(strtotime($row['date']) > strtotime(date('Y-m-d'))){

$updatequery = " UPDATE TABLE1 SET $row[status] = 'Expired' ";

}}

Upvotes: 0

Views: 1190

Answers (4)

Praful Argiddi
Praful Argiddi

Reputation: 1192

Below code working for me, just a single line of update query will updated less than of current date

UPDATE TABLE_NAME SET status='expired' WHERE DATEDIFF(date, CURDATE()) < 0

Thank.

Upvotes: 0

efeder
efeder

Reputation: 562

Your sql database may be on a server whose time is different from the time on the machine where you are running the code, so I would recommend doing the check and update all on the sql server side.

(Disclaimer: I use mysql, so that's how I've written my answer. I assume you can translate to whatever sql database you use)

I would recommend using MySql's date functions, which you can see here.

UPDATE TABLE1 SET status='Expired' WHERE DATEDIFF(CURDATE(), date) < 0

Upvotes: 0

Zereges
Zereges

Reputation: 5209

You can use 

$result= "SELECT UNIX_TIMESTAMP(date) AS unixdate, status FROM TABLE1";

and then compare

if ($row['unixdate']) > strtotime(date('Y-m-d')))

Upvotes: 0

wintheday
wintheday

Reputation: 139

I would advise using the PHP DateTime class it has the date diff function so you could implement like this

$today = new DateTime('today');
$expires = new DateTime($datefromdb);
$diff = $today->diff($expires);
if($diff < 1)
{ 
   $updatequery = " UPDATE TABLE1 SET $row[status] = 'Expired' ";
}

Upvotes: 2

Related Questions