How to view query error in PDO PHP

Question

try {     $db = new PDO("mysql:host=".HOST.";dbname=".DB, USER, PW);     $st = $db->prepare("SELECT * FROM c6ode"); } catch (PDOException $e){     echo $e->getMessage(); } 

How can I check the mysql error for the query in above case?

Answer 1

You need to set the error mode attribute PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION.
And since you expect the exception to be thrown by the prepare() method you should disable the PDO::ATTR_EMULATE_PREPARES* feature. Otherwise the MySQL server doesn't "see" the statement until it's executed.

<?php try {     $pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly');     $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);     $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);       $pdo->prepare('INSERT INTO DoesNotExist (x) VALUES (?)'); } catch(Exception $e) {     echo 'Exception -> ';     var_dump($e->getMessage()); } 

prints (in my case)

Exception -> string(91) "SQLSTATE[42S02]: Base table or view not found:  1146 Table 'test.doesnotexist' doesn't exist" 

see http://wezfurlong.org/blog/2006/apr/using-pdo-mysql/
EMULATE_PREPARES=true seems to be the default setting for the pdo_mysql driver right now. The query cache thing has been fixed/change since then and with the mysqlnd driver I hadn't problems with EMULATE_PREPARES=false (though I'm only a php hobbyist, don't take my word on it...)

*) and then there's PDO::MYSQL_ATTR_DIRECT_QUERY - I must admit that I don't understand the interaction of those two attributes (yet?), so I set them both, like

$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly', array(     PDO::ATTR_EMULATE_PREPARES=>false,     PDO::MYSQL_ATTR_DIRECT_QUERY=>false,     PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION )); 

Answer 2

I'm using this without any additional settings:

if (!$st->execute()) {     print_r($st->errorInfo()); }