strftime and mktime malfunction

[nca]

For quite some time now I have had a "remove old entries" script run every morning. It removes entries that are 90 days old in a mysql database. Well I have noticed that it misses entries from time to time. Here is the sniplet that computes the "delete date" and then runs a query that deletes all entries that have that "delete date" ($removeit).

$year=date('Y');$month=date('m');$day=date('d');
$removeit = strftime("%Y-%m-%d", mktime(0,0,0,$month,$day-90,$year));

Can someone tell me why this misses dates from time to time? Anything that would work better?

Thanks!! 😉

[toplay]

You can do it strictly in MySQL and not have to use PHP at all. Any of the following examples should work assuming that your date column in your table is of type DATE. If it's another type, then you want to extract just the date portion using DATE_FORMAT() or DATE() depending on your version of MySQL.

Example 1:

DELETE FROM table_name WHERE date_column = DATE_SUB(CURDATE(), INTERVAL 90 DAY)

Example 2:

DELETE FROM table_name WHERE date_column = CURDATE() - INTERVAL 90 DAY

Example 3:

DELETE FROM table_name WHERE TO_DAYS(date_column) = (TO_DAYS(CURDATE()) - 90)

You can use other SQL functions like DATEDIFF() depending on the version of MySQL you have. See manual page:

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

hth.

[kburger]

For your particular problem it would be

DELETE FROM table_name WHERE date_column <= DATE_SUB(CURDATE(), INTERVAL 90 DAY)

(note the <=) Just in case it didn't run some morning, for whatever reason.

[toplay]

Kburger, yes I agree and I was going to show that, however, I did it the way nca wants since he/she wrote "...have that delete date".

🙂

[nca]

Great guys thanks for the help... but I dont think this method will work for me... is there some way to re-write my script so that it will work? Because the script also takes the "ID" value of the row and removes the image from a directory that is associated with that row. Here is the script...

$year=date('Y');
$month=date('m');
$day=date('d');

$removeit = strftime("%Y-%m-%d", mktime(0,0,0,$month,$day-90,$year));

$results = mysql_query("SELECT * FROM table WHERE CREATED='$removeit'");

while ( $rows = mysql_fetch_array($results) ) {

$id=$rows[ID];

@("DELETE FROM table WHERE ID='$id' LIMIT 1");
@unlink("/home/user44/public_html/images/$id.jpg");

}

[toplay]

Of course it can work for you. Just use it in the SELECT. Example:

$sql = 'SELECT ID FROM table WHERE CREATED = DATE_SUB(CURDATE(), INTERVAL 90 DAY)';
$results = mysql_query($sql);
if (!$results) {
    // Handle error
    echo 'Query failed. SQL: ', $sql, '<br />Error # ', mysql_errno(), ' Error msg: ', mysql_error();
    exit;
}

while ($rows = mysql_fetch_array($results) ) {

$id=$rows['ID'];

@mysql_query("DELETE FROM table WHERE ID='$id' LIMIT 1");
@unlink("/home/user44/public_html/images/$id.jpg");

}

🙂