I thought the most efficient way was to create a UNIQUE field on the table instead of selecting to check for existing values before doing anything else but this makes use of two queries. Instead with a UNIQUE field only one query is necessary because MySQL checks for you. The problem is that duplicate entry errors cause an internal server error which I cannot recover from in PHP. What do you guys suggest, what is the best way to avoid duplicate entries in a PHP & MySQL application?
4 Answers
- Use
ON DUPLICATE KEY
INSERT INTO someTable (id, amount) VALUES ($to_uid, $send_amount) ON DUPLICATE KEY UPDATE amount = amount + $send_amount
https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
2) You can catch the duplicate key exception. PDO example:
try{
$stmt->execute(...);
}
catch(PDOException $e){
if($e->errorInfo[1] == 1062){
// Mysql returned 1062 error code which means a duplicate key
}
}
3 Comments
boompow
Thank you but what about mysqli or CodeIgniter which is what I am using? A plain try/catch does not work.
Didar_Uranov
Because
mysqli doesn't throw exceptions by default. Mysqli functions just return false. See this answer if you want make mysqli throw exceptions on error stackoverflow.com/questions/14578243/… Or just check mysqli::$errno for 1062 code when for example mysqli::query returns falseboompow
Guess I will be asking how to do this in CodeIgniter.
You could use REPLACE INTO for your query, it will try an insert first and than it will delete the row with the same ID and replace it.
1 Comment
boompow
That is not what I want. I do not want to replace anything I just want to avoid putting in information that is already there. You said duplicate errors could not be recovered from, I think Dagon has a different idea.
FOUND THE SOLUTION!
CodeIgniter requires the setting
$db['default']['stricton'] = TRUE;
an explicitly calling
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
In order for MySQL to throw exceptions. The exceptions must also be caught.
2 Comments
Didar_Uranov
I mentioned about
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); in my answer comments. Cool guy did his answer. Greatboompow
@Didar_Uranov I thank you for your help but this was actually more a of a CodeIgniter issue which you failed to resolve. Without the setting I mentioned MySQL would not have been able to throw exceptions.
You can use INSERT IGNORE to prevent updating a row and prevent an exception from being thrown if row already exists.
https://dev.mysql.com/doc/refman/5.5/en/insert.html
If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.
UNIQUEconstraint, faster?UNIQUEconstraint, for testing, I get in return an internal server error500. I have triedtry/catchblocks but the program just stops execution...