How to handle duplicate entry error?
Moderator: General Moderators
- SmokyBarnable
- Forum Contributor
- Posts: 105
- Joined: Wed Nov 01, 2006 5:44 pm
How to handle duplicate entry error?
I'm running into a lot situations where I attempt an INSERT into a table and get a Duplicate entry error. I learned how to suppress the error by adding ON DUPLICATE KEY UPDATE but this doesn't seem to make sense, why would anything need to be updated if nothing is changing. If there is already an identical row in a table, why do I need to update anything? Is there some way to attempt to update a row but if the identical row is there then do nothing?
Thanks.
Thanks.
Sounds like you would not want to use ON DUPLICATE KEY UPDATE at all. Can you explain a bit more about what your data is like? What kind of Primary Key are you using? Why are you encountering so many attempts to enter duplicate data? I'm suspicious that there's something about your schema or operations that is not good database design practice.
- SmokyBarnable
- Forum Contributor
- Posts: 105
- Joined: Wed Nov 01, 2006 5:44 pm
I solved this by doing a Select query to see if the data was already in the table, and if it wasn't then I did the insert. I was just wondering if there was any waywhere if there was a duplicate then the insert query would be ignored...like ON DUPLICATE KEY IGNORE perhaps. I came to the conclusion that my logic was flawed and there was no reason to try to insert data if it was already there.
The problem with doing a select first is that you need to make sure the "select and insert" are in the same transaction, otherwise you'll run into concurrency issues... (nothing garantuees you that the insert statement will be handled immediately right after the select, eg: user1 -> select -> record not found, user2 -> select ->record not found, user1 -> insert -> ok, user2 -> insert -> problem...)
Untested and ignoring notices...
Code: Select all
<?php
function newToken() {
$token = md5(uniqid(rand(), TRUE));
$_SESSION['token'] = $token;
return $token;
}
if ($_POST['commit'] && $_POST['token'] == $_SESSION['token']) {
$token = newToken();
//process form
} else {
$token = newToken();
//build view..form below
}
?>Code: Select all
<form>
<input type='hidden' name='token' value="<?php echo $token; ?>" />
<input type='submit' name='commit' value='Submit' />
</form>