Doctrine DBAL Insert if doesn't exist

Question

I'm using Symfony2-framework with Doctrine DBAL, and I'm inserting some data into MySql-database. Insert looks something like this(simplified):

$conn->insert('sometable', array(
        "col1"     => $data1,
        "col2"     => $data2,
        "col3"     => $data3
));

What I would like to achieve is the functionality like the ordinary sql-has. The ability to insert if doesn't exist, like: INSERT IGNORE. But is it possible to do such a thing with DBAL?

Note that I don't use objects here.

Edit: Please do note that I'm not using objects, but rather the depicted array-insert-method of DBAL.

Edit2: I tried to approach the problem with using the suggested try-catch, which seems to work quite well except for one thing. The db auto increments the primary key even if no new rows were added. Here is the code that I used:

    try{
        $conn->insert('sometable', array(
            "col1"     => $data1,
            "col2"     => $data2,
            "col3"     => $data3
       ));
   } catch( \Exception $e) {
          switch (get_class($e)) {
               case 'Doctrine\DBAL\DBALException':
                   // No problems here. Just means that the row already existed.
                   break;
               default:
                   $this->get('logger')->error("(ERROR in ".__METHOD__.", @Row: ".(__LINE__)."): DB-error! error: ".$e->getMessage());
                   break;
          }
  }

And I also had to do a multiple row unique index for the table, because I have to check if all the columns are the same. i.e. if the whole row is the same as the one we are trying to insert.

So.. It works well otherwise, except that the auto increment value keeps rising up every time we try-insert-catch. I don't think it's a real problem, but it just feels stupid to waste numbers.. :D

Answer 1

I'm not aware of a way to do this other than just doing a select query and try to retrieve the row, i.e:

$sql = "SELECT * FROM sometable WHERE ....";
$stmt = $conn->prepare($sql);
$stmt->bindParam(); //or bindValue...
$stmt->execute();

$exists = $stmt->fetch();

if(!$exists) {
    // insert or whatever..
} else {
    // do nothing? 
}

Eventually you might be able to attach Event which would do that for you, but I'm not sure if that applies in your case.

Alternatively you could create an unique constraint and do a try/catch when inserting your data. Whenever the data is not unique, the database would return an error and you'd usually receive a PDOException although in Doctrine I think it's with a different name (check exceptions). Just catch that exception and do nothing with it. Example:

try {
    $conn->insert('sometable', array(
        "col1"     => $data1,
        "col2"     => $data2,
        "col3"     => $data3
    ));
} catch(PDOException $e) {
   // do nothing. 
}