Check if mySQL record is added in the last x seconds

Question

I have a mySQL database and a table in which new records are created for the project. Each created project has a “project name” and an event creation date (of type DATETIME).

There are two projects created with the same name, but if they are created by the same user in quick succession, we can safely assume that this was a mistake in the user part (double-clicking, updating the browser when event variables are sent, etc. d.).

How to write an SQL statement to check if a record with the same name exists, was it added in the last 10 seconds? So far I have the following, although I do not know how to check 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 storing such checks in MySQL, because it may not be the best way to find errors, because the user can click on the address or refresh the page in 10 seconds. Instead, place the checks in the front-end code to disable pressing the submit button twice or redirect the user to a page where no variables are passed.

But if this is not what you would like to do, this could be your request:

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

Answer 3

You are trying to fix the problem wrong. Why not fix the problem at the source? It is not possible for the user to create these two projects in sequence.

If your application allows the user to submit the form several times through an update, consider using redirection after processing the GET / POST variables.

Also, use simple client tricks to disable the submit button after it has been pressed once. You can accomplish this with very little jQuery