CodeIgniter - continue on SQL error?

Question

Basically I have a table with a couple of columns marked Unique. I have a script that dumps a bunch of values into the table with a command like this:

$this->db->query("INSERT INTO `table` (`col1`, `col2`, `col3`) VALUES (`val1`, `val2`, `val3`)");

Every so often my script will try to insert a row which would violate the uniqueness of one of the columns. However, instead of causing the script to abort with a database error, I'd like it to continue, possible outputting a little message. Basically I'm looking for the codeigniter equivalent of

mysql_query("INSERT blah blah blah") or print("fail");

Thanks!
Mala

Answer 1

Yeah, took me a while too and annoyed the hell out of me:

$db['default']['db_debug'] = FALSE;

... in config/database.php - disables the error page.

After a query ran, use this to check for an error:

if (!empty($this->db->_error_message())) {
    echo "FAIL";
}

Answer 2

I know you already have a solution, but thought this might be useful for others viewing this question as well.

Let the database do the work for you:

$this->db->query("INSERT IGNORE INTO `table` (`col1`, `col2`, `col3`) VALUES (`val1`, `val2`, `val3`)");

When you use INSERT IGNORE, things like duplicate key errors become warnings instead of errors, which let your queries run without interrupting the flow of your script.

You could then do a

SHOW WARNINGS;

after all the queries have run to see what warnings occurred.

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