Pdo Catch and Output MySQL Errors

pdo catch and output mysql errors

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

How to catch exact sql error in php pdo statement

You can check any error after execute query with following method.

$selectQueryResult1->execute();
$arr = $selectQueryResult1->errorInfo();
print_r($arr);

Why can't I catch the error with PDOException?

The exception you are trying to catch will never be thrown, because you need to tell PDO how you want it to handle errors.

$con = new PDO($dsn,"root","xxxx");
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Otherwise, the default PDO::ERRMODE_SILENT will be used:

This is the default mode. PDO will simply set the error code for you to inspect using the PDO::errorCode() and PDO::errorInfo() methods on both the statement and database objects; if the error resulted from a call on a statement object, you would invoke the PDOStatement::errorCode() or PDOStatement::errorInfo() method on that object. If the error resulted from a call on the database object, you would invoke those methods on the database object instead.

Tangentially, you should be using prepared statements. You are using a prepare() call, but you are not parametrizing the query and binding the variables as you should. Using quote() is not secure enough.

2020 Update:

Interestingly, starting with PHP 8, the default behaviour for PDO will change and will throw exceptions by default. The change was voted on this RFC, which mentions:

The current default error mode for PDO is silent. This means that when an SQL error occurs, no errors or warnings may be emitted and no exceptions thrown unless the developer implements their own explicit error handling.
This causes issues for new developers because the only errors they often see from PDO code are knock-on errors such as “call to fetch() on non-object” - there's no indication that the SQL query (or other action) failed or why.

When PHP 8 is released on November 2020, the default error mode will be PDO::ERRMODE_EXCEPTION.

PDO Exception Questions - How to Catch Them

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

Troubleshooting PDO: Error not caught when executing prepared statement

PDO::errorInfo() or PDOStatement->errorInfo()

As for exceptions, check the docs for "Errors and error handling" in PDO. Exceptions aren't thrown by default, which is why you might want to enable them.

See as well:

My PDO statement doesn't work?

Why does this PDO statement silently fail?

TL;DR

Always have set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION in your PDO connection code. It will let the database tell you what the actual problem is, be it with query, server, database or whatever. Also, make sure you can see PHP errors in general.
Always replace every PHP variable in the SQL query with a question mark, and execute the query using prepared statement. It will help to avoid syntax errors of all sorts.

Explanation

Sometimes your PDO code produces an error like Call to a member function execute() or similar. Or even without any error but the query doesn't work all the same. It means that your query failed to execute.

Every time a query fails, MySQL has an error message that explains the reason. Unfortunately, by default such errors are not transferred to PHP, and all you have is a silence or a cryptic error message mentioned above. Hence it is very important to configure PHP and PDO to report you MySQL errors. And once you get the error message, it will be a no-brainer to fix the issue.

In order to get the detailed information about the problem, either put the following line in your code right after connect

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

(where $dbh is the name of your PDO instance variable) or - better - add this parameter as a connection option. After that all database errors will be translated into PDO exceptions which, if left alone, would act just as regular PHP errors.

After getting the error message, you have to read and comprehend it. It sounds too obvious, but learners often overlook the meaning of the error message. Yet most of time it explains the problem pretty straightforward:

Say, if it says that a particular table doesn't exist, you have to check spelling, typos, letter case. Also you have to make sure that your PHP script connects to a correct database
Or, if it says there is an error in the SQL syntax, then you have to examine your SQL. And the problem spot is right before the query part cited in the error message.

You have to also trust the error message. If it says that number of tokens doesn't match the number of bound variables then it is so. Same goes for absent tables or columns. Given the choice, whether it's your own mistake or the error message is wrong, always stick to the former. Again it sounds condescending, but hundreds of questions on this very site prove this advice extremely useful.

Note that in order to see PDO errors, you have to be able to see PHP errors in general. To do so, you have to configure PHP depends on the site environment:

on a development server it is very handy to have errors right on the screen, for which displaying errors have to be turned on:
```
  error_reporting(E_ALL);
  ini_set('display_errors',1);
```
while on a live site, all errors have to be logged, but never shown to the client. For this, configure PHP this way:
```
  error_reporting(E_ALL);
  ini_set('display_errors', 0);
  ini_set('log_errors', 1);
```

Note that error_reporting should be set to E_ALL all the time.

Also note that despite the common delusion, no try-catch have to be used for the error reporting. PHP will report you PDO errors already, and in a way better form. An uncaught exception is very good for development, yet if you want to show a customized error page, still don't use try catch for this, but just set a custom error handler. In a nutshell, you don't have to treat PDO errors as something special but regard them as any other error in your code.

P.S.

Sometimes there is no error but no results either. Then it means, there is no data to match your criteria. So you have to admit this fact, even if you can swear the data and the criteria are all right. They are not. You have to check them again. I've short answer that would help you to pinpoint the matching issue, Having issue with matching rows in the database using PDO. Just follow this instruction, and the linked tutorial step by step and either have your problem solved or have an answerable question for Stack Overflow.

PHP doesn't display PDO error even if I set error handling

Probably you have error reporting turned off and you are not trying to catch an exception if your code throws one.

So you have to put your code into a try and catch block e.g.

try {
    //your stuff
} catch(PDOException $e) {
    echo $e->getMessage();
}

Because your PDO is set to throw an exception if an error occurred, so you have to catch that with this block. Also I would recommend you to turn on error reporting for useful error messages (Only while staging never in production).

How to display mysql error while using PDO in a connection class?

You shouldn't necessarily display an error message, but only raise it. Displaying mysql error messages makes your site less secure and looks mean.

So, the only your concern have to be

to make PDO theow exceptions on SQL errors.
to set up PHP to make error messages displayed on a development server and logged on a live one

Thus, try this code at the top of your scripts

ini_set('display_errors',1);
error_reporting(E_ALL);

and then run one of these functions with intentional mysql error. Your script should be halted displaying an error message.

Update:

Well, I tried to run your code and these 2 magic lines of code from above helped me other way:

Notice: Undefined variable: pdo_opt in D:\SERVER\htdocs\0.php on line 37

means variable was misspelled (You have to use $this->pdo_opt instead) - so, this was a reason why PDO didn't trow exceptions.

Here is a lesson, why error_reporting should be always E_ALL

Pdo Catch and Output MySQL Errors