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?

score 75 · Answer 1 · edited Nov 06 '17 at 18:16

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

score 55 · Answer 2 · edited Jul 21 '16 at 20:04

55

I'm using this without any additional settings:

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

edited Jul 21 '16 at 20:04

The Codesee

3,628
3
32
71

answered Jan 08 '12 at 08:48

ladar

5,788
2
24
38

2

Still the best answer 8 years later – Petro Dec 04 '20 at 14:18
1

Perfect for 2021 – Arun Maurya Jun 01 '21 at 07:48

score 23 · Answer 3 · answered Jan 08 '12 at 08:45

23

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

answered Jan 08 '12 at 08:45

Steve Rukuts

8,857
3
45
70

Nope still not working, i know my query is wrong, but i want to display that error. – TPSstar Jan 08 '12 at 08:48
You may find that there is an error with the call to `new PDO(...` itself. Try increasing the level of error_reporting by setting for example `error_reporting(E_ALL)`. – Steve Rukuts Jan 08 '12 at 08:49

score 15 · Answer 4 · answered Jan 15 '14 at 11:19

15

a quick way to see your errors whilst testing:

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

answered Jan 15 '14 at 11:19

Lan

1,778
2
19
36

I think this is very easy way – Naveen DA Dec 27 '16 at 17:16

score 4 · Answer 5 · answered Jul 05 '17 at 09:07

4

/* 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
)

answered Jul 05 '17 at 09:07

Timothy Nwanwene

939
11
18

Uncaught Error: Call to undefined function errorInfo() – Kwnstantinos Nikoloutsos Dec 14 '19 at 15:00

How to view query error in PDO PHP

5 Answers5

Linked

Related