Check if user has uploaded more or exactly 50 files in less than two minutes MySQL

Question

So I have an image uploader, and I want to create a minimal bot protection. My table structure looks like this:

| ID | uploader_ip | image_name | image_url_id | date
------------------------------------------------------
| 1  | 127.0.0.1   | bla.jpg    | fsdJGf       | UNIX Timestamp  (UPDATE ON: Create)

I want to check if there are more or exactly 50 images/rows, that were created in less or exactly two minutes.

How can I do that?

You should read about [triggers](http://net.tutsplus.com/tutorials/databases/introduction-to-mysql-triggers/). Also, you could do a simple `SELECT * FROM table WHERE ip = ? ORDER BY date DESC LIMIT 20, 1` and then check the date of that one against the current date. — Francisco Presencia, Oct 10 '13 at 18:02
In two minutes on the clock? Or, two minutes between two uploads? That's an important distinction that makes this an easy problem, or a very difficult problem. For the hard problem, see this: http://stackoverflow.com/q/6551214/362536 — Brad, Oct 10 '13 at 18:04
@Brad Good point, and what I mean is, if there were created exactly 50 or more rows, in two minutes together, so it will check everyone's date, and if these dates are interval 2 minutes, get it. — Jony Kale, Oct 10 '13 at 18:07
@JonyKale In that case, see that link I sent you. Your question is exactly the same, just with a different time interval. — Brad, Oct 10 '13 at 18:07
@brad Actually it wokrs with two tables, Im not sure how to get it,. — Jony Kale, Oct 10 '13 at 19:19

score 0 · Answer 1 · answered Oct 10 '13 at 18:07

You'll want to construct a query that incorporates count and group, and puts a range on the date column. If you were to do this for only the most recent two minutes, your query would look something like this:

select count(*) from your_table where uploader_ip="<some ip>" and date > (now() - interval 2 minute) group by uploader_ip;

Chloe · Answer 2 · 2013-10-10T20:56:15.047

Here is the query

Select count(*) from table 
where 
table.date >  now() - interval 2 minute
-- or the function date_sub(now(), interval 2 minute)
and id = 1

If you want all user id that have uploads within a 2 minute window:

select id, 
  (select count(*) 
    from table 
    where id = t.id 
    and date > t.date - interval 1 minute 
    and date < t.date + interval 1 minute
  ) as total
from table t
where total >= 50

Check if user has uploaded more or exactly 50 files in less than two minutes MySQL

2 Answers2