pdo catch and output mysql errors

Question

I have an insert statement that is executed with PDO. Insert works great however if there is an error I would like it displayed to the user.

I have the below try-catch block.

try{ 
    $insertuser = $db->prepare('INSERT INTO `she_she`.`Persons` (`idnumber`,`addedby`,`firstname`, `middlename`, `surname`, `fullname`, `gender`, `birthdate`, `homelanguage`, `department`, `employeetype`, `employeestatus`) VALUES  (?,?,?,?,?,?,?,?,?,?,?,?)'); 
    $insertuser->execute(array($idnumber,$user,$firstname, $middlename, $surname, $fullname, $gender, $birthdate, $language, $department, $employmenttype, $personstatus));  
} 
catch(PDOException $exception){ 
    return $exception; 
}

If the query fails, or let's say a duplicate IDNumber, I want this displayed to the user.

If I simply try to echo the variable $exception it does not work.

I want to return the MySQL error to the user.

$exception will be an object of the type PDOException, and two of its many attributes will be the mysql error code and error message. — Marc B, Oct 19 '12 at 14:47

score 29 · Accepted Answer · answered Oct 19 '12 at 18:20

29

By default PDO is not in a state that will display errors. you need to provide the following in your DB connection

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

More info can be seen Here

answered Oct 19 '12 at 18:20

Cody Covey

1,060
6
9

1

Thanks. If I set error mode to `ERRMODE_WARNING` it returns the warning on top of the page, how can I set it to the value of the variable and return this where I want to on the page. Thanks – Smudger Oct 19 '12 at 18:41
2

In your catch block you can set $exception to a different variable for use later. `catch(PDOException $exception){ $error = $exception->getMessage(); } //later in code echo "An Error has occurred " . $error;` or something similar. – Cody Covey Oct 19 '12 at 18:49

score 4 · Answer 2 · answered Mar 06 '19 at 07:55

1.Add ERRMODE_EXCEPTION mode after your db connection:

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

2.And than you must use try{} catch{} method for all your mysql query. Like this:

try {
    $SQL = "DELETE FROM items WHERE item_id=:item_id";
    $m = $dbh->prepare($SQL);
    $m->bindParam(':item_id', $item_id, PDO::PARAM_INT);
    $m->execute();
    //success
    $return = "Your success message.";
}
catch (PDOException $e) {
    //error
    $return = "Your fail message: " . $e->getMessage();
}

Christiaan · Answer 3 · 2012-10-19T15:01:23.053

1

You should use this:

return $exception->getMessage();

See the page on the documentation of Exception class:

http://www.php.net/manual/en/exception.getmessage.php

edited Oct 19 '12 at 15:01

answered Oct 19 '12 at 14:49

Christiaan

183
1
12

pdo catch and output mysql errors

3 Answers3

Linked

Related