CODEWITHSUNDEEP
phpmysqlsqlduplicatesunique
boompow
boompow

Reputation: 151

MySQL How to Avoid Duplicate Entries from PHP

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?

Upvotes: 0

Views: 1506

Answers (4)

FuzzyTree
FuzzyTree

Reputation: 32392

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.

Upvotes: 0

boompow
boompow

Reputation: 151

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.

Upvotes: 0

Didar_Uranov
Didar_Uranov

Reputation: 1240

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

Upvotes: 2

Skewled
Skewled

Reputation: 783

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.

Upvotes: 0

Related Questions