0

The scenario: You've made a superb script where users can vote for images but you haven't put a failsafe on the POST meaning that some users end up finding a way to vote multiple times for an image in one go by refreshing their browser.

The problem: You now have to clean up a table to reduce votes so that they are spaced by at least 1 hour intervals. For example:

photo_id voter_id date_vote
54321    123      28th june 2011 16h46 57s
54321    123      28th june 2011 16h47 12s <= delete 
54321    123      28th june 2011 16h47 35s <= delete   
54321    123      28th june 2011 16h47 52s <= delete   
54321    123      28th june 2011 16h48 22s <= delete 
...
54321    123      28th june 2011 17h47 05s <= keep

Would this be possible through one query or a combination of successive queries?

Kara
  • 6,115
  • 16
  • 50
  • 57
James P.
  • 19,313
  • 27
  • 97
  • 155

1 Answers1

1

Rather than taking a post-cleanup approach, why not first detect if a unique vote (photo_id, voter_id) had been submitted in the past hour. If not, then add the record, otherwise don't add the record and notify the user that they have to wait an hour between votes.

GEL
  • 162
  • 1
  • 11
  • That would have been ideal and is exactly what I did afterwards :) . I can post the code somewhere if anyone is interested. – James P. Jun 28 '11 at 16:03
  • I've found a solution to the above problem. It is possible to group by a time interval, say HOUR(). Using this it would possible to use LIMIT which in turn would make it possible to DELETE part of the records. – James P. Jul 26 '11 at 14:33