Comparing Times In PHP Using MySQL Datetime

Question

I have activity logs stored in my mysql database, each with a registered datetime. I am trying to write a short php script to delete all logs that are older than 60 days. I am having trouble trying to compare the times in datetime format.

Here's what I have:

$sixty_days_past = strtotime("-60 days");

$earliest_time = date("Y-m-d H:i:s", $sixty_days_past);

$logs_result = mysql_query("SELECT id FROM df_logs WHERE date < '$earliest_time'");

    while($logs_row = mysql_fetch_array($logs_result)) { 

        $oldentry = $logs_row['id'];

        mysql_query("DELETE FROM df_logs WHERE id='$oldentry'");    

    }

What is wrong with this query?

Niet the Dark Absol · Accepted Answer · 2012-04-24 00:30:32Z

1

Everything.

DELETE FROM `df_logs` WHERE `date` < DATE_ADD(NOW(),INTERVAL -60 DAY)

Done.

To elaborate:

You are going through PHP when MySQL is perfectly capable of doing the right calculations
You are not enclosing field names in backticks, which is a problem because date is a keyword.
You are deleting rows one at a time, which results in a potentially unbound number of queries, instead of deleting them all at once (such as with IN to list the IDs to delete)

answered Apr 24, 2012 at 0:30

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Comparing Times In PHP Using MySQL Datetime

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related