PHP PDO. error number '00000' when query i

Question

This question already has an answer here:

• PDO SQL-state “00000” but still error? [duplicate] 5 answers

I have the code below:

$sql3 = "update news set date='$time' where id='2'";
$sql3 = $connect->exec($sql3);
if(!$sql3)
{
    print_r($connect->errorInfo());
    $error = $connect->errorInfo();
    die ("Error: (".$error[0].':'.$error[1].') '.$error[2]);
}

When I run the script, sometimes I get error number '00000'. I mean it goes intro the IF. and it is all random. output (sometimes):

Array ( [0] => 00000 [1] => [2] => )

What should I do to fix this problem ?
PS: The script executes correctly every time.

Answer 1

If exec does not update any row, it will return 0. that makes if(!$sql3) evaluate to false, you should do this instead :

if($sql3 === false){

}

Answer 2

The PDO error code 00000 means that everything works fine. The reason you're hitting the error-checking code is that $sql3 is returning 0 (no rows were effected) and PHP evaluates that to false. Try explicitly checking for a return false;

if($sql3 === false)

Answer 3

00000 means, it works fine. you should change your if to this: $sql3 === false.

Answer 4

In my experience badly formed queries (syntax errors) and failed queries (for example an INSERT that didn't insert anything) also may WRONGLY return error code 00000. You should go ahead and try to run the complete query on your SQL console and see why it failed. I really don't know why the proper error message isn't returned though. Here's a snippet of the code we use

    $r = $pdo->prepare($sql);
    if (!$r->execute($input_parameters)) { # query failed
        if ($bLogFailures) {
            error_log('query failed: ERROR['.$pdo->errorCode().':'.print_r($pdo->errorInfo(), true).'] QUERY['.$sql.']');
        }
        return false;
    }

Answer 5

I had the same problem. It also tortured me a lot, but finally figured it out.

Suppose you have 7 columns in your table.

You are inserting data into 4 of them.

If for remaining 3 columns the default value is not set (say NULL for alpha-numeric columns, CURRENT_TIMESTAMP for date-time related columns etc.) then the above stated problem occurs.

If you are inserting data into all of those 7 columns or at least in those columns for which default value is not set, you wont get any error and data will get inserted.

Answer 6

The PDO::exec statement returns an integer to indicate the number of rows that were affected. So in your particular case, as the SomeKittens indicates, if 0 rows were affected, then your error code would be triggered.

However, if you are concerned as to whether your query worked, your better action may be to use PDO::query (in terms of your code ($returnObj = $connect->query($sql3) instead of PDO::exec.

The $returnObj can then be checked to see if there was an error in the SQL execution, and you can then troubleshoot your SQL query because it will tell you what the error was and where it was located.

Your best bet to do this would be:

//set PDO to throw an error so you can wrap the query in a try / catch block. 
$connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql3 = "update news set date='$time' where id='2'";
try {
     $returnObj = $connect->query($sql3);
} catch (PDOException $e) {
    print_r($returnOjb->errorInfo());
    $error = $returnObj->errorInfo();
    die ("Error: (".$error[0].':'.$error[1].') '.$error[2]);
}