MySQL Insert Unique Value

Question

I have a MySQL Table that has a column that will AUTO_INCREMENT, another to store user-submitted data, and another column that defaults to CURRENT_TIMESTAMP.

My INSERT query is:

$query = $db->prepare("INSERT IGNORE INTO `UserData` (`user_data`) VALUES(?)");
$query->bind_param('s', $commentdata);
$query->execute();

However it is still inserting the duplicate values (if a user clicks submit multiple times). What is the best way to prevent this?

If you let the user insert whatever value he wants , you will get duplicate errors !. do you mean you want to generate random unique value for every click ? — Accountant م
– Accountant م, Commented May 18, 2017 at 22:17
From the view of "user experience" the "best way" would be not to use IGNORE but to check if the entry already exists in the DB every time before insertion and tell the user if it's the case. — Paul Spiegel
– Paul Spiegel, Commented May 18, 2017 at 22:23
Your bind_param call is wrong. sss means there are 3 parameters being bound, but you only have 1 parameter in the query. — Barmar
– Barmar, Commented May 18, 2017 at 22:47
@Barmar: You are correct. Forgot to remove my other table values when I was generalizing this query. — Bijan
– Bijan, Commented May 18, 2017 at 22:59

symcbean · Accepted Answer · 2017-05-18 22:24:28Z

3

MySQL will not create duplicate auto increment ids (unless you have a very badly configured cluster) so presumably the duplicates you refer to are in a different attribute - you've only told us about user_data.

If you don't want duplicates in there then add a unique index on the column. You should also add error handling to deal with failures when the situation arises and remove the 'IGNORE'.

However you also need to think about your controlling logic (this hints that you probably have csrf vulnerabilities) and your user interface (why are you allowing users to submit the same form twice?)

answered May 18, 2017 at 22:24

symcbean

48.4k6 gold badges63 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bijan Over a year ago

It may have been users who double clicked the submit button.

Bijan · Accepted Answer · 2017-05-18 22:58:45Z

2

I ended up using the following query:

INSERT INTO `UserData` (`post_num`, `user_data`)
SELECT ?, ? FROM `UserData`
WHERE NOT EXISTS (
    SELECT * FROM `UserData` WHERE `post_num`= ? AND `user_data`=?
) LIMIT 1

Then I do $query->bind_param('isis', $post_number, $comment, $post_number, $comment); to bind the values.

This will check to make sure there are no duplicate comments on a post.

Note that my actual example inserts more information such as the user information and I check to make sure there are no duplicate comments from that certain user on a specific post.

answered May 18, 2017 at 22:58

Bijan

8,81622 gold badges107 silver badges164 bronze badges

Comments

bytesized · Accepted Answer · 2017-05-18 22:21:58Z

0

The best way to prevent duplicate values in a MySQL table is for the table definition to use the UNIQUE or PRIMARY constraint. See the documentation for table creation syntax.

You may also want the value to be a KEY if you plan on performing lookups using that value.

answered May 18, 2017 at 22:21

bytesized

1,52213 silver badges23 bronze badges

Collectives™ on Stack Overflow

MySQL Insert Unique Value

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related