Often it can be quite hard to debug SQL errors when using PHP since PHP will often throw a generic error that doesn’t really help you diagnose the situation. Other times you may not want to allow PHP errors to be shown. A quick way to debug PDO SQL errors is to use the or die function. “or die” works by executing the SQL query and if it fails to execute it will “die” and output the contents of the function. This is quite similar to a try catch block.
In order to output the PDO error you will need to get the error info from the statement object. To implement this function simply add the or die code to the end of the $stmt->execute code.
$stmt = $db->prepare($sql); $stmt->execute($array) or die(print_r($stmt->errorInfo(), true));
The critical part of this code is the call to the method “errorInfo on the stmt class. There are lots of different types of SQL errors that can happen. A lot of time time, if you are developing code, the cause will be the query you have written. This method will allow you to dump the entire error response that is returned from the DB.
Log PDO and SQL errors internally
This should go without saying, but I will say it anyway. You should never output SQL or PDO errors onto the page in a live scenario. These error messages can expose highly sensitive information about your web site, DB and or server. If this information gets into the wrong hands, you could find yourself with a hacked website.
The code snippet above will dump the entire error onto the web page. Very useful in development, but not in production. Please be careful and use an internal logging system to keep track of database errors on a production website.