PDO error message?

Question

Here is a snippet of my code:

$qry = '
    INSERT INTO non-existant-table (id, score) 
    SELECT id, 40 
    FROM another-non-existant-table
    WHERE description LIKE "%:search_string%"
    AND available = "yes"
    ON DUPLICATE KEY UPDATE score = score + 40
';
$sth = $this->pdo->prepare($qry);
$sth->execute($data);

print_r($this->pdo->errorInfo());

This should give me an error because the tables don't even exist. All I get however is this:

Array ( [0] => 00000 )

How can I get a better description of the error so I can debug the issue?

Try printing the error after the prepare, but before the execute — thetaiko, Oct 22 '10 at 18:44
The error looks pretty clear to me, :search_string will get replaced with something like 'foobar' (including the quotes), so your final sql statement would look something like "%'foobar'%" what you need to do is append the % to the $data in the ->execute() statement. Since this was 3 years ago, I'm sure you figured out the error — relipse, May 13 '13 at 00:31
By the way, I'm coming across the same problem, no error message. The only way I have learned to fix it is to just copy and paste the SQL into phpmyadmin and look for the output. Did anyone else figure this out? I tried the answers below. — relipse, May 13 '13 at 00:32

score 114 · Accepted Answer · edited Mar 31 '19 at 21:13

114

Try this instead:

print_r($sth->errorInfo());

Add this before your prepare:

$this->pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );

This will change the PDO error reporting type and cause it to emit a warning whenever there is a PDO error. It should help you track it down, although your errorInfo should have bet set.

edited Mar 31 '19 at 21:13

Neuron

5,141
5
38
59

answered Oct 22 '10 at 18:36

Alan Geleynse

24,821
5
46
55

2

Thanks, that gives me the exact same thing, `Array ( [0] => 00000 )`. – JD Isaacks Oct 22 '10 at 18:42
I added another thing you can try, but if errorInfo is not getting set correctly there may be something else going on. – Alan Geleynse Oct 22 '10 at 18:49
2

I tried PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING but it didn't work, i still get 00000. Anyone have any ideas? – relipse May 13 '13 at 00:23
perfect thanks, this will emit a warning echoed to the page, best to turn on on development but off on production – relipse May 23 '16 at 15:08
1

I added this and worked:$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING ); – Omiod Sep 06 '17 at 19:09
Thank you for this comment. Pointed me in the right direction. I use ERRMODE_EXCEPTION now. And handle those the way I handle all exceptions in my app. Thanks! – Valery May 26 '18 at 12:56

score 8 · Answer 2 · answered Nov 20 '15 at 10:54

Old thread, but maybe my answer will help someone. I resolved by executing the query first, then setting an errors variable, then checking if that errors variable array is empty. see simplified example:

$field1 = 'foo';
$field2 = 'bar';

$insert_QUERY = $db->prepare("INSERT INTO table bogus(field1, field2) VALUES (:field1, :field2)");
$insert_QUERY->bindParam(':field1', $field1);
$insert_QUERY->bindParam(':field2', $field2);

$insert_QUERY->execute();

$databaseErrors = $insert_QUERY->errorInfo();

if( !empty($databaseErrors) ){  
    $errorInfo = print_r($databaseErrors, true); # true flag returns val rather than print
    $errorLogMsg = "error info: $errorInfo"; # do what you wish with this var, write to log file etc...         

/* 
 $errorLogMsg will return something like: 
 error info:  
 Array(
  [0] => 42000
  [1] => 1064
  [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table bogus(field1, field2) VALUES                                                  ('bar', NULL)' at line 1
 )
*/
} else {
    # no SQL errors.
}

finally, something that worked!! – showtime Dec 01 '20 at 14:55 — showtime, Dec 01 '20 at 14:55

score 6 · Answer 3 · edited Sep 19 '12 at 19:28

6

Maybe this post is too old but it may help as a suggestion for someone looking around on this : Instead of using:

 print_r($this->pdo->errorInfo());

Use PHP implode() function:

 echo 'Error occurred:'.implode(":",$this->pdo->errorInfo());

This should print the error code, detailed error information etc. that you would usually get if you were using some SQL User interface.

Hope it helps

edited Sep 19 '12 at 19:28

uınbɐɥs

7,236
5
26
42

answered Mar 26 '12 at 21:37

Vikram

4,162
8
43
65

1

You must mean `echo 'An error occurred: '.implode(":",$this->pdo->errorInfo();`. There is no point in using `print_r` if the object is not an array :-) – uınbɐɥs Sep 19 '12 at 05:29
@ShaquinTrifonoff pdo->errorInfo() is indeed an array. See here: http://php.net/manual/en/pdo.errorinfo.php – Vikram Sep 19 '12 at 13:41
That's what I'm saying - `implode` turns it into a string, so there is no reason to use `print_r` on a string. – uınbɐɥs Sep 19 '12 at 18:56
Thanks for the implode idea. Saved me a bunch of time. +1 – Kris.Mitchell Mar 27 '15 at 14:31

score 2 · Answer 4 · answered Oct 22 '10 at 18:50

From the manual:

If the database server successfully prepares the statement, PDO::prepare() returns a PDOStatement object. If the database server cannot successfully prepare the statement, PDO::prepare() returns FALSE or emits PDOException (depending on error handling).

The prepare statement likely caused an error because the db would be unable to prepare the statement. Try testing for an error immediately after you prepare your query and before you execute it.

$qry = '
    INSERT INTO non-existant-table (id, score) 
    SELECT id, 40 
    FROM another-non-existant-table
    WHERE description LIKE "%:search_string%"
    AND available = "yes"
    ON DUPLICATE KEY UPDATE score = score + 40
';
$sth = $this->pdo->prepare($qry);
print_r($this->pdo->errorInfo());

PDO error message?

4 Answers4

Linked

Related