27

It seems that for an INSERT statement, one can use if (isset($connect->lastInsertId())) in order to check whether the INSERT statement was successful. (Please correct me if I'm wrong.)

But for an UPDATE statement, how can I know if it was successful?

For example, I have basic one like that:

$statement=$connect->prepare("UPDATE users SET premium='1' WHERE userid=?");
$statement->execute(array($id));

Thanks a lot in advance. Regards

alexx0186
  • 1,557
  • 5
  • 20
  • 32
  • 2
    What is a successful update statement in your eyes? – hakre Aug 05 '12 at 23:19
  • Hi, it's a statement that resulted in the modification of at least one value in the table. Regards – alexx0186 Aug 05 '12 at 23:19
  • 4
    `PDOStatement::rowCount()` returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object. See http://de.php.net/manual/pdostatement.rowcount.php – hakre Aug 05 '12 at 23:20
  • There is not difference, whether it's SELECT, INSERT or UPDATE query. You check for a failure the same way – Your Common Sense Jan 18 '23 at 21:45

2 Answers2

37

It depends on what you mean by "successful." If you mean that the query executed without failing, then PDO will either throw an exception on failure or return FALSE from PDOStatement::execute(), depending on what error mode you have set, so a "successful" query in that case would just be one in which the execute method did not return FALSE or throw an exception.

If you mean "successful" in that there were actually rows updated (versus just 0 rows updated), then you'd need to check that using PDOStatement::rowCount(), which will tell you the number of affected rows from the previous query.

Warning: For updates where newvalue = oldvalue PDOStatement::rowCount() returns zero. You can use

$p = new PDO($dsn, $u, $p, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));

in order to disable this unexpected behaviour.

Blackbam
  • 17,496
  • 26
  • 97
  • 150
FtDRbwLXw6
  • 27,774
  • 13
  • 70
  • 107
  • Hi, thank you very much for this. I actually meant "more than zero row updated". Regards – alexx0186 Aug 05 '12 at 23:23
  • 1
    Very nice answer - I actually needed the first part - true/false :) – jave.web Jan 30 '14 at 23:50
  • 5
    Pay attention relying on rowCount result: PDOStatement->rowCount — Returns the number of rows affected by the last SQL statement If you try to update a row but the new field(s) value(s) are the same than the one(s) already stored in the DB, rowCount will return 0 (as nothing has been actually updated!). This doesn't mean that that your UPDATE statement was not successfully executed! – Marco Gagliardi Feb 28 '14 at 16:22
  • 1
    @MarcoGagliardi this is the solution : http://php.net/manual/en/pdostatement.rowcount.php#104930 – WhiteLine Mar 18 '16 at 15:39
  • 1
    @FtDRbwLXw6 could you advice what means `$dsn`, `$u` and `$p` in `$p = new PDO($dsn, $u, $p, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));` – user2360831 Mar 17 '20 at 07:49
15
$stmt->execute();
$count = $stmt->rowCount();

if($count =='0'){
    echo "Failed !";
}
else{
    echo "Success !";
}
Bashir Noori
  • 639
  • 7
  • 12
  • 2
    It will be better that you explain about your answer , Code only answers are not very helpful. – ᴀʀᴍᴀɴ Feb 21 '17 at 19:00
  • my answer is about UPDATE. $sql = "UPDATE users SET premium='1' WHERE userid=$id "; $stmt = $dbConnection->prepare($sql); $stmt->execute(); then the above code will echo success or failed. – Bashir Noori Feb 22 '17 at 05:25
  • 3
    rowCount returns the number of rows affected by the last SQL statement. So if the count is 0, then no rows were affected. Hence the update failed. I would remove the quotes around the number zero because the rowCount method returns an int. It will work anyway because you are not using triple equals to compare. – Herii Mar 15 '20 at 04:43