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() < ....?

Answer 1

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

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

Answer 2

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;

Answer 3

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