delete records older than 1 hour

Posted on 2012-08-14
Last Modified: 2012-08-19

When a user submits a form, I am trying to delete all previous records older than 1 hour. So right after my insert statement I am adding a delete statement.  My date field is named "added_on" and the date values look like 2012-07-31 21:45:03.  Using the below deletes everything including the current record, instead of the records older than 1 hour.

$sql = "INSERT INTO info_requests SET
email = '".addslashes($data['email'])."',
ip_address = '".$_SERVER['REMOTE_ADDR']."',
added_on = now() ";

$sql = "DELETE FROM info_requests WHERE added_on < (now() - interval 1 hour)";
Question by:web5dev7
    LVL 11

    Accepted Solution

    try this

    first write this test to  see what date is returned from dateadd
    select now(), DATE_ADD(now(),INTERVAL -1 hour)

    then try
    select top 10 added_on, *   FROM info_requests
    WHERE added_on < DATE_ADD(now(),INTERVAL -1 hour) order by added_on  desc

    this will show if your assumption about added_on format is correct - it could be just date, without time.
    LVL 3

    Assisted Solution

    The NOW() function provides the current time in the local timezone of the server.  Please check the  local timezone of the server is your intended timezone.
    LVL 14

    Assisted Solution

    by:Scott Madeira
    What happens when you do this:

    $sql = "DELETE FROM info_requests WHERE added_on < Date_sub(now(), interval 1 hour)";
    LVL 107

    Assisted Solution

    by:Ray Paseur

    I expect that there may be some confusion of timezones.  PHP and MySQL do not share the same time zone information.  To see this in action, create a test table with a column of the data type TIMESTAMP.  INSERT a row into the table.  SELECT the row and print it out with var_dump().  Then print out the PHP value of date('c') and compare them.

    Author Closing Comment

    Thanks all!  Yes there may well be something about timezone difference between my location and server?  

    The data is being collected at a very low activity kiosk site so we decided it was better to schedule a Cron job to empty records twice a day - since it would likely be more dependable/frequent than the next user.  The data was only being used for temporary validation and so we do not want to store it beyond its usefullness.

    The Cron is working as desired, but I'll still experiment with your suggestions to see if I can solve the mystery.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Suggested Solutions

    Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
    Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now