MySQL How to Avoid Duplicate Entries from PHP

Question

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?

It is better to check and confirm for duplicacy before you run your insert query — Garry, May 01 '14 at 04:47
@nedstark Wouldn't it be better with a `UNIQUE` constraint, faster? — boompow, May 01 '14 at 04:48
"The problem is that duplicate entry errors cause an internal server error which I cannot recover from in PHP" that sounds odd, please explain — , May 01 '14 at 04:49
@Dagon exactly how it sounds, I am using CodeIgniter btw. Every time I try to insert the same information already in a field with a `UNIQUE` constraint, for testing, I get in return an internal server error `500`. I have tried `try/catch` blocks but the program just stops execution... — boompow, May 01 '14 at 04:51
@Dagon I have asked why it does this in two other threads and no one has been able to help. There are no errors logged in WAMP, in Firebug I just get a 500 error and nothing more and it only happens when a duplicate entry error occurs. — boompow, May 01 '14 at 04:54
internal server error 500 is returned because there is error in your mysql query due to duplicacy , so before executing query you need to confirm that you are not entering any duplicate data by running other query — Garry, May 01 '14 at 04:54
@nedstark I think this could be avoided if the duplicate query didn't throw a 500 error. I think Dagon knows this can be avoided. — boompow, May 01 '14 at 04:59
Look at your log file on the server and see what it tells you about accessing the file. — Skewled, May 01 '14 at 05:02

score 2 · Answer 1 · answered May 01 '14 at 04:58

2

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
    }
}

answered May 01 '14 at 04:58

Didar_Uranov

1,230
11
26

Thank you but what about mysqli or CodeIgniter which is what I am using? A plain try/catch does not work. – boompow May 01 '14 at 05:02
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 http://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli Or just check `mysqli::$errno` for 1062 code when for example `mysqli::query` returns false – Didar_Uranov May 01 '14 at 05:05
Guess I will be asking how to do this in CodeIgniter. – boompow May 01 '14 at 05:22

score 0 · Answer 2 · answered May 01 '14 at 04:53

0

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.

answered May 01 '14 at 04:53

Skewled

783
4
12

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. – boompow May 01 '14 at 04:59

score 0 · Answer 3 · answered May 01 '14 at 05:55

0

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.

answered May 01 '14 at 05:55

boompow

131
1
12

I mentioned about `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` in my answer comments. Cool guy did his answer. Great – Didar_Uranov May 01 '14 at 13:00
@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. – boompow May 01 '14 at 15:43

score 0 · Answer 4 · answered May 01 '14 at 07:49

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.

MySQL How to Avoid Duplicate Entries from PHP

4 Answers4