...

View Full Version : How to determine if a DATE is earlier then Now()? [PHP5]



Shaitan00
04-01-2009, 07:08 AM
Simply put, I have a field in a MySQL database called expdate (of type DATE) which is the Expiry Date of the record, and ever so often I want to kick off a PHP5 function .disableExpired() that will do the following:
1 - Get all records from Table
2 - Check if their expdate has past based on now()
3 - Set a status field in that case (disabled)

Now, I know how to do (1) and (3) but honestly I've got no clue how to do (2) which is comparing the date from my table (Date field in MySQL) and NOW() in PHP5 and how to do something conditional (if) based on that ...

So far this is what I have


$record = $this->db->Select("SELECT id, expdate FROM table WHERE status = 1");

foreach($record as $key=>$val)
{
$id = $val[id];
$expdate = $val[expdate];

// ??? DO THE MAGIC HERE ??? ///

if (past expiry date)
{
$this->setStatus($id, '0');
}
}


As you can see ... missing the key element ...

Any help would be much appreciated...
Thanks,

bdl
04-01-2009, 07:19 AM
You should be doing this from within the SQL statement, something like


UPDATE table
SET status = 0
WHERE expdate < CURRENT_DATE


Of course this is just an example, please DO NOT run this against your db unless you know what the specific date comparison should be. I'd suggest performing a SELECT on the `id` field first to test the theory.

Just run this statement once in the PHP script, you don't have to mess with any other comparisons. This is a perfect example of when the SQL statement should be formed correctly to avoid pulling down records and running loops against the data. SQL is more powerful than you think.

Check the DATE and TIME functions (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html) on MySQL's site.

Fou-Lu
04-01-2009, 08:16 AM
Although you can do this directly with PHP, I would also agree that the better option is to go by SQL using the mysql date / time functions.

Moving to MySQL forum.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum