PDO SQL-state "00000" but still error? [duplicate]

Question

Can anybody explain why

$sql->execute($params); 

returns FALSE, whereas

print $pdo->errorCode(); print_r($pdo->errorInfo()); 

both return SQLSTATE 00000, which means according to the documentation success? It is an INSERT and nothing is actually being inserted into the database... so, why do I get a success message from SQLSTATE?

---

In case it helps, this is the code...

$sql = $pdo->prepare("         INSERT INTO user (             username, fname, pass, salt, email,             loc_id_home, country_id_home, region_id_home,             cont_id_home, timestamp_reg, timestamp_upd, timestamp_lastonline,              online_status, gender, birthdate             )         VALUES (             :username,:fname,:pass,:random_salt,:email,             :loc_id_home,:country_id_home,:region_id_home,             :cont_id_home,'".time()."','".time()."','".time()."',             1,:gender,:birthdate)         ");  $params=array(     ':username'=>$username,     ':fname'=>$fname,     ':pass'=>$pass,     ':random_salt'=>$random_salt,     ':email'=>$email,     ':loc_id_home'=>$loc_id_home,     ':country_id_home'=>$country,     ':region_id_home'=>$region,     ':cont_id_home'=>$continent,     ':gender'=>$gender,     ':birthdate'=>$birthdate );    $sql->execute($params);  print $pdo->errorCode(); print_r($pdo->errorInfo()); 

Answer 1

It is because $pdo->errorInfo() refers to the last statement that was successfully executed. Since $sql->execute() returns false, then it cannot refer to that statement (either to nothing or to the query before).

As to why $sql->execute() returns false, I don't know... either there is a problem with your $params array or with your database connection.

PDO::errorCode — Fetch the SQLSTATE associated with the last operation on the database handle

Note: The PHP manual (http://php.net/manual/en/pdo.errorinfo.php) does not define exactly what "last operation on the database handle" means, but if there was an issue with binding parameters, that error would have occurred inside PDO and without any interaction with the database. It is safe to say that if $pdo->execute() returns true, that $pdo->errorInfo() is valid. If $pdo->execute() returns false, the behavior of $pdo->errorInfo() is not explicitly clear from the documentation. If I recall correctly from my experience, execute returns true, even if MySQL returned an error, returns false if no operation was done. Since the documentation is not specific, it might be db driver specific.

This answer reflects practical experience as of when it was written in September 2012. As a user has pointed out, the documentation does not explicitly reaffirm this interpretation. It also may only reflect the particular database driver implementation, but it should always be true that if $pdo->execute() returns true, that $pdo->errorInfo() is valid.

You might also want to set PDO::ERRMODE_EXCEPTION in your connect sequence. Exception handling makes it unnecessary to check and query the error.

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