Updating records using prepared instructions, checking update operation

Question

I have a query that updates an entry in my database, it works fine, but I wanted to know how to check if the update occurred, so I can return true and display the correct message?

Now I know with a SELECT query that I can do:

if(stmt->fetch()) 

If this is true, I return true and say "found records", but I did not understand how to do this to request an update?

Does anyone know how?

 $query = "UPDATE user SET password = ? WHERE email = ?"; if($stmt = $conn->prepare($query)) { $stmt->bind_param('ss', $pwd, $userEmail); $stmt->execute(); //Check if update worked } 

Thanks for the help.

Answer 1

check if below works

 $query = "UPDATE user SET password = ? WHERE email = ?"; if($stmt = $conn->prepare($query)) { $stmt->bind_param('ss', $pwd, $userEmail); if ($stmt->execute()) { // worked } else { // not worked } 

Good luck !!!

Answer 2

Execute returns True when it is completed successfully, but if that is not enough for you, you can also check the affected lines :

 $query = "UPDATE user SET password = ? WHERE email = ?"; if($stmt = $conn->prepare($query)) { $stmt->bind_param('ss', $pwd, $userEmail); if ($stmt->execute()) { //query with out errors: printf("rows updateds: %d\n", $stmt->affected_rows); } else { //some error: printf("Error: %s.\n", $stmt->error); } } 

The second check you can do is check that exactly one row has been updated:

 if($stmt = $conn->prepare($query)) { $stmt->bind_param('ss', $pwd, $userEmail); if ($stmt->execute() and $stmt->affected_rows == 1) { //your update is succesfully. } } 

Answer 3

From the manual for mysqli_stmt_execute ()

Returns TRUE on success or FALSE on failure.

Answer 4

Will mysql_affected_rows () function work?

Answer 5

If you use PDO (it seems so ...), you can check with rowCount :

 $affected_rows = $stmt->rowCount(); 

And use the try - catch for errors.

Answer 6

A few years later, but maybe this can help someone.

As mentioned earlier, you can use affected_rows to check if the UPDATE query in the PREPARED APPLICATION really updated any record. However, keep in mind that if the data presented matches the record in the database, affected_rows will return zero (0).

The workaround is to create a column for TIMESTAMP with ON UPDATE CURRENT_TIMESTAMP. Then, each time I run the query, I also pass a NULL value to the time-tracking column - this way, forcing an UPDATE string every time the query is executed. Then all you do is check for affected_rows.