PDO Exception Questions - How to Catch Them

Question

I'm using PDO to re-write a website interface for a database. I used to use the mysql extension, but I had never bothered with error handling, and the few error handlers I had were basically copy-paste.

Now I'd like to do this right. However, I'm having issues catching the errors how I'd like (errors like "Duplicate Entry", "Null Value" etc in MySQL). How much of my statement needs to be in the try block? Should all of it be in there? I'm using an Include() to connect to my DB (which has its own error handling), so it's only the query execution which has errors in this code. I can't figure out why it's not catching an error when executing the following code:

try {   $stmt = $db->prepare("INSERT INTO tbl_user (id, name, password, question, answer)    VALUES (NULL, :name, :password, :question, :answer)");   $stmt->bindValue(":name", $_POST['name']);   $stmt->bindValue(":password", $_POST['password']);   $stmt->bindValue(":question", $_POST['question']);   $stmt->bindValue(":answer", $_POST['answer']);   $stmt->execute();   echo "Successfully added the new user " . $_POST['name']; } catch (PDOException $e) {   echo "The user could not be added.<br>".$e->getMessage(); } 

So my questions: does ALL OF THAT have to be in the try block? Can I just put the execute in the try block? It should catch the error Duplicate value "John" in key "name", but instead goes through with the success message. (When trying to add two "John" users). I checked in PHPMyAdmin; the index is unique and does throw the error as expected, just not using this code.

Answer 1

You should look at the documentation. But If you dont find anything, you can add another catch :

<?php try {   $stmt = $db->prepare("INSERT INTO tbl_user (id, name, password, question, answer)    VALUES (NULL, :name, :password, :question, :answer)");   $stmt->bindValue(":name", $_POST['name']);   $stmt->bindValue(":password", $_POST['password']);   $stmt->bindValue(":question", $_POST['question']);   $stmt->bindValue(":answer", $_POST['answer']);   $stmt->execute();   echo "Successfully added the new user " . $_POST['name']; } catch (PDOException $e) {   echo "DataBase Error: The user could not be added.<br>".$e->getMessage(); } catch (Exception $e) {   echo "General Error: The user could not be added.<br>".$e->getMessage(); } ?> 

This must work because all exceptions of PHP plugins herits from the Exception native PHP class. (Since 5.0 if my memory is well).

Answer 2

PDO Exception Questions - How to Catch Them

As a rule -

DO NOT catch them.

For example, your code here should be written this way

$stmt = $db->prepare("INSERT INTO tbl_user (id, name, password, question, answer) VALUES (NULL, :name, :password, :question, :answer)"); $stmt->bindValue(":name", $_POST['name']); $stmt->bindValue(":password", $_POST['password']); $stmt->bindValue(":question", $_POST['question']); $stmt->bindValue(":answer", $_POST['answer']); $stmt->execute(); echo "Successfully added the new user " . $_POST['name']; 

without any try or catch calls. Because you have no particular scenario for handling an exception here (a simple echo is scarcely counts as a handling scenario).

Instead, let it bubble up to the application-wide error handler (don't be scared by the term, PHP already has a built-in one).

However, I'm having issues catching the errors how I'd like (errors like "Duplicate Entry", "Null Value" etc in MySQL).

Only in case if you have a certain scenario, you have to use try-catch operator, but you have to always check, whether the error you've got is one you expected. Otherwise an exception have to be re-thrown:

try {     $pdo->prepare("INSERT INTO users VALUES (NULL,?,?,?,?)")->execute($data); } catch (PDOException $e) {     if ($e->getCode() == 1062) {         // Take some action if there is a key constraint violation, i.e. duplicate name     } else {         throw $e;     } } 

and of course (as it turned out to be the vere problem for this question), you have to set up PDO in exception mode, either in a constructor parameter of simply by adding the code

$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); 

right after connect.