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

Question

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

Couldn't you just put your $prepared_insertQry_toDuplicateRefTable ... in the catch block (after making sure the exception is the 'duplicate entry' error)? — Travesty3
– Travesty3, Commented May 2, 2012 at 13:25
Hello Travesty :) Thks for trying to help. I am pretty new to pdo and thought the catch block is just for getting exceptions. Thus I do not really get what you are suggesting... — Marc
– Marc, Commented May 2, 2012 at 13:28
The catch define the action to take when you get an exception this could be doing something with the error message, or in this case you want it to do a different query. — prodigitalson
– prodigitalson, Commented May 2, 2012 at 13:36

prodigitalson · Accepted Answer · 2012-05-02 13:37:33Z

1

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));

   }

answered May 2, 2012 at 13:37

prodigitalson

60.6k10 gold badges106 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Travesty3 · Accepted Answer · 2012-05-02 13:36:19Z

1

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();
}

answered May 2, 2012 at 13:36

Travesty3

14.5k7 gold badges64 silver badges103 bronze badges

1 Comment

Marc Over a year ago

Thks. Just trying it out... Give me a sec and I will revert

MatsLindh · Accepted Answer · 2012-05-02 13:38:21Z

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.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related