• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2304
  • Last Modified:

delete records older than 1 hour

Hi,

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)";
0
web5dev7
Asked:
web5dev7
4 Solutions
 
Lara FEACommented:
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.
0
 
et_meCommented:
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.
0
 
Scott MadeiraCommented:
What happens when you do this:

$sql = "DELETE FROM info_requests WHERE added_on < Date_sub(now(), interval 1 hour)";
0
 
Ray PaseurCommented:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

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.
0
 
web5dev7Author Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now