How do I prevent duplicate entrys to mySQL?

Question

On my site, I have a form that users fill out to become a member. They fill out name, bday, email, etc. Then when they click submit, the data gets into mySQL. But sometimes when a user clicks submit many times or refreshes the page, the data gets inputted to the database more than once. How can I prevent this accidental submission? Is there a code I can use to only let one set of data get into the database?

This is also a problem in my comment section. I allow uses to put comments on people's profiles. But when they abuse the refresh button or submit button, I get like 10 of the same comments. I am trying to prevent users accidentally submitting a comment twice.

Thanks.

Answer 1

Server-side: Implement throttling. Only allow 1 submission every 10 seconds or so.

Update: When you accept a form submission, record the timestamp you made the submission in $_SESSION. When you accept another (or rather, every) form submission, check if the value stored in $_SESSION is older than 10 seconds. If it is, continue. If it isn't, don't do any more work.

You could do it with just some database stuff to I guess, but $_SESSION is much simpler.

Client-side: Disable the submit button via Javascript when the form is submitted.

Answer 2

Create a UNIQUE constraint:

CREATE UNIQUE INDEX name_of_youw_index ON tablename(columnname);

INSERTs will now fail with double data.

Answer 3

In the case of the database duplicates, put a unique constraint on the field.

For both the database duplicates and the duplicate comments, disable the submit button for 2-3 seconds after it is pressed to prevent multiple submits.

Answer 4

In the first case you probably just want to add a unique index on the email address and use that to create a profile for that user.

In the second case, as I understand it, you are trying to prevent users from accidentally submitting data twice, not to completely prevent data being submitted twice. You probably don't want to prevent people from writing the same comment twice on the same page. If someone writes "Today was like yesterday!", you don't want to prevent them from coming back the next day and writing "Today was like yesterday!" again. This would be unnatural and the check could be expensive as it would require indexing a lot of data. I think you want to prevent someone from submitting the same form twice, regardless of whether the data is the same or not.

So the solution to your second example is to include a hidden field in your form that uniquely identifies it. When they submit the form, mark the value in the hidden field as used. If someone later submits a form with the same value in the hidden field, reject it.

Answer 5

Make the email field in your database "unique", by adding a unique index to it.

If your database gets another entry with an email which has already been used then mysql throws an error - the error number is 1062.

If you wanted to then you could handle this error different from others.

mysql_errno()

http://www.php.net/manual/en/function.mysql-errno.php

Answer 6

Don't build your own account creation/authentication mechanism. This has been done 10000 times before. Use another preexisting one that has been tested and is bug free. The only reason to do this is for experience/understanding of how a production implementation of a login system evolves. And really, is that what you want to become an expert in, or would you prefer to focus on the domain of the problem you are working on?