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.