PDO mysql: How to know if insert was successful

Question

I'm using PDO to insert a record (mysql and php)

$stmt->bindParam(':field1', $field1, PDO::PARAM_STR);
$stmt->bindParam(':field2', $field2, PDO::PARAM_STR);
$stmt->execute();

Is there a way to know if it inserted successfully, for example if the record was not inserted because it was a duplicate?

Edit: of course I can look at the database, but I mean programmatic feedback.

Answer 1

PDOStatement->execute() returns true on success. There is also PDOStatement->errorCode() which you can check for errors.

Answer 2

Given that most recommended error mode for PDO is ERRMODE_EXCEPTION, no direct execute() result verification will ever work. As the code execution won't even reach the condition offered in other answers.

So, there are three possible scenarios to handle the query execution result in PDO:

1. To tell the success, no verification is needed. Just keep with your program flow.
2. To handle the unexpected error, keep with the same - no immediate handling code is needed. An exception will be thrown in case of a database error, and it will bubble up to the site-wide error handler that eventually will result in a common 500 error page.
3. To handle the expected error, like a duplicate primary key, and if you have a certain scenario to handle this particular error, then use a try..catch operator.

For a regular PHP user it sounds a bit alien - how's that, not to verify the direct result of the operation? - but this is exactly how exceptions work - you check the error somewhere else. Once for all. Extremely convenient.

So, in a nutshell: in a regular code you don't need any error handling at all. Just keep your code as is:

$stmt->bindParam(':field1', $field1, PDO::PARAM_STR);
$stmt->bindParam(':field2', $field2, PDO::PARAM_STR);
$stmt->execute();
echo "Success!"; // whatever

On success it will tell you so, on error it will show you the regular error page that your application is showing for such an occasion.

Only in case you have a handling scenario other than just reporting the error, put your insert statement in a try..catch operator, check whether it was the error you expected and handle it; or - if the error was any different - re-throw the exception, to make it possible to be handled by the site-wide error handler usual way. Below is the example code from my article on error handling with PDO:

try {
     $pdo->prepare("INSERT INTO users VALUES (NULL,?,?,?,?)")->execute($data);
} catch (PDOException $e) {
    if ($e->getCode() == 1062) {
        // Take some action if there is a key constraint violation, i.e. duplicate name
    } else {
        throw $e;
    }
}
echo "Success!";

In the code above we are checking for the particular error to take some action and re-throwing the exception for the any other error (no such table for example) which will be reported to a programmer.

While again - just to tell a user something like "Your insert was successful" no condition is ever needed.

Answer 3

Try looking at the return value of execute, which is TRUE on success, and FALSE on failure.

Answer 4

If an update query executes with values that match the current database record then $stmt->rowCount() will return 0 for no rows were affected. If you have an if( rowCount() == 1 ) to test for success you will think the updated failed when it did not fail but the values were already in the database so nothing change.

$stmt->execute();
if( $stmt ) return "success";

This did not work for me when I tried to update a record with a unique key field that was violated. The query returned success but another query returns the old field value.

Answer 5

You can test the rowcount

    $sqlStatement->execute( ...);
    if ($sqlStatement->rowCount() > 0)
    {
        return true;
    }

Answer 6

PDOStatement->execute() can throw an exception

so what you can do is

try
{
PDOStatement->execute();
//record inserted
}
catch(Exception $e)
{
//Some error occured. (i.e. violation of constraints)
}

Answer 7

Use id as primary key with auto increment

$stmt->execute();
$insertid = $conn->lastInsertId();

incremental id is always bigger than zero even on first record so that means it will always return a true value for id coz bigger than zero means true in PHP

if ($insertid)
   echo "record inserted successfully";
else
   echo "record insertion failed";