mysql continue on errors

Question

I have a PHP foreach loop and a mysql insert statement inside of it. The loop inserts data into my database. I've never ran into this issue before but what I think is happening is that the insert dies (I do not have an "or die" statement after the insert) when it reaches a duplicate record. Even though there may be duplicate records in the table, I need this to just continue. Is there something that I need to specify to do this?

I'm transferring some records from one table to another. Right now, I have 20 records in table #1 and only 17 in table #2. I'm missing 3 records but only one of those are duplicated which violates the constraint on the table. The other two records should have been added. Can someone give me some advice here?

Answer 1

Not a good way cause you should figure out whats wrong, but to just prevent it from dieing try adding @ in front of the function

@mysql_query = ...

Answer 2

One way around this would be to simply query the database for the record that you're about to insert. This way, your series of queries will not die when attempting to insert a duplicate record.

A slightly more efficient solution would be to query for [i]all[/i] of the records you're about to insert in one query, remove all the duplicates, then insert the new ones.

Answer 3

Do you insert multiple rows with one INSERT statement?

INSERT INTO xyz (x,y,z) VALUES
  (1,2,3),
  (2,3,5),
  (3,4,5),
  (4,5,6)

Then you might want to consider prepared statements
...or adding the IGNORE keyword to your INSERT statement

INSERT IGNORE INTO xyz (x,y,z) VALUES
  (1,2,3),
  (2,3,5),
  (3,4,5),
  (4,5,6)

http://dev.mysql.com/doc/refman/5.0/en/insert.html says:

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead

You can still fetch the warnings but the insertion will not be aborted.

Answer 4

INSERT INTO FOO (ID, BAR) VALUES(1,2),(3,4) ON DUPLICATE KEY UPDATE BAR=VALUES(BAR)

Answer 5

This may help.

Answer 6

What's happening is that PHP is throwing a warning when the mysql insert fails and stopping on that warning. The best way to accomplish your goal is:

1. Create a custom exception handler
2. Set PHP to use the exception handler for warnings.
3. Wrap the insert attempt into a try / catch
4. When you catch the exception / warning, either log or output the mysql error but continue script execution.

This will allow your script to continue without stopping while at the same time explaining to you the problem.