PHP PDO. error number '00000' when query is correct

Question

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.

Error code 00000 means that the `MySQL` query way successfully executed. — Jo Smo, Aug 19 '14 at 14:37
Probably the problem is not in the code. Check the default values in the database table. — Mert S. Kaplan, Mar 09 '22 at 20:10

score 46 · Accepted Answer · answered Aug 05 '12 at 04:15

46

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)

answered Aug 05 '12 at 04:15

SomeKittens

38,868
19
114
143

1

That's not true, see my answer. – Collector Mar 29 '17 at 12:24
1

@Collector There's a big difference between "failed" and "did not do what the programmer intended". `00000` means that there were no failures but provides no guarantees that the code did what you wanted. – SomeKittens Mar 30 '17 at 18:16
As an FYI No record found would also return false + code '00000' – Itay Moav -Malimovka Jul 22 '21 at 14:50

score 7 · Answer 2 · answered Aug 05 '12 at 04:16

7

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

}

answered Aug 05 '12 at 04:16

Oussama Jilal

7,669
2
30
53

score 6 · Answer 3 · answered Mar 29 '17 at 12:23

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

Sorry, internal variable of that function which determines whether or not to log query errors. — Collector, Aug 21 '20 at 23:22

user4065635 · Answer 4 · 2014-09-22T10:16:12.587

I just got similar situation in my php project - it occured that PDO Exception with error code '00000' took place when I tried to insert row with a field set to NULL while column defining the field in the database was of type ENUM('0', '1') and restriction NOT NULL. After modifying PHP script to place '0' instead of NULL, the error perished.

Further coding brought more light into the situation - I was performing more than one PDO statments within one DB transaction but checking errors (in Exception handling block) basing only on the first PDO statement executed while real error occured int the third PDO statement.

score 2 · Answer 5 · answered Apr 03 '13 at 22:31

2

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

answered Apr 03 '13 at 22:31

s.naseri

31
3

score 2 · Answer 6 · answered Sep 04 '16 at 20:11

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.

score 2 · Answer 7 · answered May 12 '17 at 17:03

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

[Please don't die!](https://phpdelusions.net/programming) – Your Common Sense May 12 '17 at 17:13 — Your Common Sense, May 12 '17 at 17:13

PHP PDO. error number '00000' when query is correct

7 Answers7

Linked