PDO mysql How to know if insert was successful

Question

I m using PDO to insert a record  mysql and php    stmt- gt bindParam   field1    field1  PDO  PARAM STR    stmt- gt bindParam   field2    field2  PDO  PARAM STR    stmt- gt 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

User · Answer

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";

User · Answer

If an update query executes with values that match the current database record then  stmt- gt 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- gt 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

User · Answer

You can test the rowcount       sqlStatement- gt execute            if   sqlStatement- gt rowCount    gt  0                return true

User · Answer

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

User · Answer

PDOStatement- execute   can throw an exception  so what you can do is  try   PDOStatement- gt execute      record inserted   catch Exception  e      Some error occured   i e  violation of constraints

User · Answer

PDOStatement- gt execute   returns true on success  There is also PDOStatement- gt errorCode   which you can check for errors

User · Answer

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    To tell the success  no verification is needed  Just keep with your program flow  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  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- gt bindParam   field1    field1  PDO  PARAM STR    stmt- gt bindParam   field2    field2  PDO  PARAM STR    stmt- gt 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- gt prepare  INSERT INTO users VALUES  NULL           - gt execute  data     catch  PDOException  e        if   e- gt 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

[php] PDO mysql: How to know if insert was successful

Examples related to php

Examples related to mysql

Examples related to database

Examples related to pdo