Mysql PDO: how to find out if an insert was successful

Question

I use 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 find out if it was 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

I wonder why there is still no right answer.

Given that the most recommended error mode for PDO is ERRMODE_EXCEPTION , there is no direct execute() checking the result will never work . Since the execution of the code does not even reach the condition suggested in the other answers.

So, there are three possible scenarios for processing the result of an insert operation in a PDO:

• To report success, no verification is required. Just follow the flow of your program.
• To deal with an unexpected error, keep the same thing - no immediate processing code is required. In the event of a database error, an exception will be thrown and it will pop up to the system-wide error handler, which will ultimately lead to a 500 common error page.
• To handle the expected error, for example, a duplicated primary key, and if you have a specific script to handle this error itself , use the try..catch .

For the average PHP user, this sounds a bit alien - how is it, and not for checking the direct result of an operation? - but here's how the exceptions work - you check for an error somewhere else. Once for everyone. Very comfortably.

So, in the normal case, you don’t need the processing code at all. Just save your code as is:

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

If successful, this will tell you that if it fails, it will show you a page with regular errors that your application shows for such a case.

Only if you have a processing script , in addition to the error message, put the insert statement in the try..catch , check whether this error was expected and handle it; or - if the error was any other - throw an exception so that you can normally handle the error handler throughout the territory. The following is a sample 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, ie duplicate name } else { throw $e; } } echo "Success!"; 

In the above code, we check that a specific error performs some actions and repeatedly throws an exception for any other error (for example, there is no such table) that will be reported to the programmer.

While again - just to tell the user something like "Your insert was successful" No condition is required.

Answer 3

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

Answer 4

If the update request is executed with values ​​corresponding to the current database record, then $stmt->rowCount() will return 0 , since no rows were damaged. If you have if( rowCount() == 1 ) to check success, you think the update failed when it didn’t work, but the values ​​were already in the database, so nothing has changed.

 $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 request returned, but another request returns the old field value.

Answer 5

You can check the number of rows

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

Answer 6

PDOStatement-> execute () may throw an exception

so what can you do

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

Answer 7

Use id as auto increment primary key

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

incremental id is always greater than zero even on the first record, therefore means that it will always return a true id coz value greater than zero, means true in PHP

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