Check if mySQL record added in the last x seconds

Question

I have a mySQL database and a table where new records for a project are created. Each project created has a "project name" and an event created date (of type DATETIME).

There can be two projects created with the same name, but if they get created by the same user in quick succession, it is safe to assume it was a mistake on the user's part (clicking twice, refreshing the browser when event variables are passed, etc.).

How do I write a SQL statement to check if a record with the same name already exists, it was added in the last 10 seconds? So far I have the following, although I don't know how to check for the last 10 seconds.

select * from projects where user = 'johnsmith' AND projectname = 'test' AND active='y' AND DATE(projectcreatedon) = CURRENT_DATE AND DATEPART() < ....?

i think the right way is to do the insertion in a transaction with check if the name already exist, so you avoid your problem — Haim Evgi, Jun 19 '11 at 05:42
Just to be clear, having a project added with the same name is OK, just not if it was in the last 10 seconds or so. If the user really did mean to add the project with the same name, I will ask them to just wait for a few more seconds. — zee, Jun 19 '11 at 05:45

score 11 · Answer 1 · answered Jun 19 '11 at 05:50

11

replace AND DATE(projectcreatedon) = CURRENT_DATE AND DATEPART() < ....? with:

AND projectcreatedon > (now() - INTERVAL 10 SECOND)

answered Jun 19 '11 at 05:50

Gryphius

75,626
6
48
54

score 3 · Answer 2 · answered Jun 19 '11 at 06:35

I would suggest not to keep such checks in MySQL because that might not be the perfect way of knowing mistakes because the user might well click the submit or refresh the page after 10 seconds. Instead, put checks in the front-end code to disable clicking the submit button twice or redirect the user to a page where no variables are passed.

But if that isn't what you would like to do, then this might be your query:

SELECT *
FROM `projects`
WHERE `user` = 'johnsmith'
AND `projectname` = 'test' 
AND `active`='y' 
AND TIMESTAMPDIFF(SECOND, projectcreatedon, now()) > 10;

score 1 · Answer 3 · edited May 23 '17 at 12:00

You're trying to fix the problem in the wrong way. Why not eliminate the problem at the source? Make it impossible for the user to create these two projects successively.

If your app makes it possible for a user to submit a form multiple times via refresh, consider using a redirect after the GET/POST variables have been processed.

Furthermore, use simple client-side tricks to disable the submit button after it has been clicked once. You can accomplish this with a very small amount of jQuery

Check if mySQL record added in the last x seconds

3 Answers3

Linked