CODEWITHSUNDEEP
phpmysqlpdo
Marc
Marc

Reputation: 9567

PHP MySQL PDO -> ON INSERT if value already there do another query

I have a mysql db with two tables. The first table is called 'uniqueReferences' and the second one 'duplicatedReferences'. The two tables have two fields only: an id field (auto-incremented) and a field called Reference. What I would like is as follows. When trying to insert a ref in the 'uniqueReferences' table, if the reference already exists, do not insert it in that table but in the table 'duplicatedReferences'.

So what I tried but did not work is the following.
1-> set the field reference of my 'uniqueReferences' table as 'unique'.
2-> Make the following

try{  
         $req = $prepared_insertQry_toUniqueRefTable -> execute(array(something));

         if($req == 0){
               $prepared_insertQry_toDuplicateRefTable->execute(array(something));
         }
     }
    catch(PDOException $e){echo $e->getMessage();}

This is unfortunately not working. I have the following error SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry. Hope someone can help. Cheers. Marc

Upvotes: 0

Views: 2363

Answers (3)

MatsLindh
MatsLindh

Reputation: 52912

Try to insert the data first, then if it already exists, insert it into the table for duplicated rows. As the exception is generated if the row exists, you can catch this exception and then insert the row into the duplicated table instead:

try
{  
    $req = $prepared_insertQry_toUniqueRefTable -> execute(array(something));
}
catch (PDOException $e)
{
    // you can use the value of errorInfo[1] to check the actual generated error code
    if ($e->errorInfo[1] == 1062)
    {
        $prepared_insertQry_toDuplicateRefTable->execute(array(something));
    }
    else
    {
        // rethrow the exception as we're not handling it
        throw $e;
    }
}

You might have to adjust it a bit to get what you want, but this should be the gist of it.

Upvotes: 1

prodigitalson
prodigitalson

Reputation: 60413

See my notes inline with the code:

    try{  
         $req = $prepared_insertQry_toUniqueRefTable -> execute(array(something));

         // this never executes because an Exception halts it here
         /*if($req == 0){
               $prepared_insertQry_toDuplicateRefTable->execute(array(something));
         }*/
     }
    catch(PDOException $e){
       // this catch grabs the exception and executes the code within instead
       // that code might log an error, echo the error message, or perform
       // alternative logic. In your case you want to execute alterntative logic
       // ie. your query
       $prepared_insertQry_toDuplicateRefTable->execute(array(something));

   }

Upvotes: 1

Travesty3
Travesty3

Reputation: 14489

Based on the message you're getting, it looks like you are catching a PDOException, of which the message is ... 1062 Duplicate entry. This means that the table constraints on your 'uniqueReferences' table will not allow a duplicate entry. Probably because you have a primary key in that table. This is a good thing, and it will make this problem easier to solve.

So if you get an exception thrown every time you attempt to insert a duplicate entry, then that's when we know to insert into the 'duplicatedReferences' table. You just want to verify that the exception thrown was due to a duplicate entry.

Try this:

try
{
    $prepared_insertQry_toUniqueRefTable->execute(array(something));
}
catch (PDOException $e)
{
    if ($e->getCode() == 1062)
        $prepared_insertQry_toDuplicateRefTable->execute(array(something));
    else
        echo $e->getMessage();
}

Upvotes: 1

Related Questions