How to view query error in PDO PHP

问题:

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?

回答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
));

回答2:

I\'m using this without any additional settings:

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

回答3:

I\'m guessing that your complaint is that the exception is not firing. PDO is most likely configured to not throw exceptions. Enable them with this:

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

回答4:

a quick way to see your errors whilst testing:

$error= $st->errorInfo();
echo $error[2];

回答5:

/* Provoke an error -- the BONES table does not exist */

$sth = $dbh->prepare(\'SELECT skull FROM bones\');
$sth->execute();

echo \"\\nPDOStatement::errorInfo():\\n\";
$arr = $sth->errorInfo();
print_r($arr);

output

Array
(
    [0] => 42S02
    [1] => -204
    [2] => [IBM][CLI Driver][DB2/LINUX] SQL0204N  \"DANIELS.BONES\" is an undefined name.  SQLSTATE=42704
)

回答6:

this work for me if you get this error:

Fatal error: Call to undefined method mysqli::error()

if(! empty( $db->error ) ){
   echo $db->error;  // <- this is not a function call error()
}

https://stackoverflow.com/a/32045823