CODEWITHSUNDEEP
phpmysqlpdo
PwnageAtPwn
PwnageAtPwn

Reputation: 431

PDO reports failed execution but error code suggests otherwise

I'm just using PDO to insert customers into my table but it has been overcomplicated by something which is unexplainable by myself or other research. It gives the error code: 00000 (which means that is a success, apparently) but no data was actually inserted into the database and the error is only supposed to be outputted if the query was a failure, but the error is.. success?

$insertUser = $database->prepare("INSERT INTO customer (Surname, Forename, AddressRow1, AddressRow2, AddressRow3, AddressRow4, PostCode, Telephone, mobileNumber, Email, assignedGarage)
                            VALUES (:surname, :forename, :addressrow1, :addressrow2, :addressrow3, :addressrow4, :postcode, :telephone, :mobilenumber, :email, :assignedgarage)");
$insertUser->bindParam(':surname', $_POST['surname']);
$insertUser->bindParam(':forename', $_POST['forename']);
$insertUser->bindParam(':addressrow1', $_POST['addressrow1']);
$insertUser->bindParam(':addressrow2', $_POST['addressrow2']);
$insertUser->bindParam(':addressrow3', $_POST['addressrow3']);
$insertUser->bindParam(':addressrow4', $_POST['addressrow4']);
$insertUser->bindParam(':postcode', $_POST['postcode']);
$insertUser->bindParam(':telephone', $_POST['telephone']);
$insertUser->bindParam(':mobilenumber', $_POST['mobilenumber']);
$insertUser->bindParam(':email', $_POST['email']);
$insertUser->bindParam(':assignedgarage', $_SESSION['garageId']);
if(!$insertUser->execute()) {
    $err[] = $database->errorCode();
}
elseif ($insertUser->rowCount() == 1) {
    $id =  $database->lastInsertId();
    echo "<script type=\"text/javascript\">
<!--
window.location = \"updateUser.php?id=$id\"
//-->
</script>";
}
if(count($err)) {
        echo "<p style=\"color:red;\">The following errors were detected:</p><br/>";
        foreach ($err as $key => $error) {
            echo "<p style=\"color:red;\">$error</p><br/>";
        }
    }

I first started without defining all the columns I wanted to insert into but that didn't work so I predefined them.
A rudimentary customer table design, I have my reasons for having the telephone/mobile numbers as varchars.
My table in its early, rudimentary stages. I have my reasons for choosing varchars for mobile/telephone numbers.

Upvotes: 1

Views: 155

Answers (3)

awsmketchup
awsmketchup

Reputation: 122

Try this instead and tell me what you find:

try{
   $database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION)

   $insertUser = $database->perepare("INSERT INTO customer (Surname, Forename, AddressRow1, AddressRow2, AddressRow3, AddressRow4, PostCode, Telephone, mobileNumber, Email, assignedGarage) VALUES (:surname, :forename, :addressrow1, :addressrow2, :addressrow3, :addressrow4, :postcode, :telephone, :mobilenumber, :email, :assignedgarage)");

   $insertUser->execute(array(':surname'=>$_POST['surname'], ':forename'=>$_POST['forename'], ':addressrow1'=>$_POST['addressrow1'], ':addressrow2'=>$_POST['addressrow2'], ':addressrow3'=>$_POST['addressrow3'], ':addressrow4'=>$_POST['addressrow4'], ':postcode'=>$_POST['postcode'], ':telephone'=>$_POST['telephone'], ':mobilenumber'=>$_POST['mobilenumber'], ':email'=>$_POST['email'], ':assignedgarage'=>$_SESSION['garageId']));

    if ($insertUser->rowCount() == 1) {
    $id =  $database->lastInsertId();
    echo "<script type=\"text/javascript\">
<!--
window.location = \"updateUser.php?id=$id\"
//-->
</script>";} else{
   //sum'n sum'n
}catch(PDOException $e){
  echo 'Error occured'.$e-getMessage();
}

Upvotes: 1

pajaja
pajaja

Reputation: 2202

This is because the $database->errorCode(); will not work for operations not directly performed on DB handle as in your case (you used PDO->prepare()). In your case this error code is reffering to last successful query not the one you are trying to execute, hence the 0000 error code.

You most likely have an error in your params array (values that are passed from $_POST). Also when using bindParams method you should specify the type of variable that is expected by database.

Upvotes: 0

&#193;lvaro Gonz&#225;lez
&#193;lvaro Gonz&#225;lez

Reputation: 146450

Summarising, you have this:

$insertUser = $database->prepare(...);
if(!$insertUser->execute()) {
    $err[] = $database->errorCode();
             ^^^^^^^^^
}

So you're calling PDO::errorCode() rather than PDOStatement::errorCode(). As the manual explains:

PDO::errorCode() only retrieves error codes for operations performed directly on the database handle. If you create a PDOStatement object through PDO::prepare() or PDO::query() and invoke an error on the statement handle, PDO::errorCode() will not reflect that error. You must call PDOStatement::errorCode() to return the error code for an operation performed on a particular statement handle.

Depending on your needs and current code, you might also be interested in PDOStatement::errorInfo(), which provides error details in friendly format. And, of course, you can also instruct PDO to throw exceptions and get rid of manual error checking.

Upvotes: 1

Related Questions