CODEWITHSUNDEEP
phpmysqlerror-handlingrows-affected
Kyle Noland
Kyle Noland

Reputation: 4798

How to determine if a MySQL update query succeeded when the data passed in the query is the same as what is already in the database?

Let's say you have a form with pre-populated data from your database, and you allow your users to make changes and save the form. If the user clicks the save button without making any changes, MySQL will not actually perform a write operation, and therefore the affected_rows will return 0.

I understand the behavior, but what is the best practice for determining if an update failed, other than checking for the number of affected_rows?

What is the best practice for differentiating between an update that actually failed, and one that "succeeded" but resulted in 0 affected_rows so that I can provide feedback to the user?

Upvotes: 10

Views: 29893

Answers (4)

user680786
user680786

Reputation:

Just check if no errors occurred after execution of query.
If you use mysql, check mysql_error():
if (!mysql_error()) print 'all is fine';
Same for mysqli.

Upvotes: 6

Mel
Mel

Reputation: 6167

[affected_rows()][1] is -1 if a query fails, not zero.

[1]: http://www.php.net/manual/en/function.mysql-affected-rows.php

It may return 0 if no changes were made to the row (same values), or if mysql didnt find a row to update. It will only return -1 due syntax erro

Upvotes: 4

Racooon
Racooon

Reputation: 1496

Variation 1:

mysql_query() or die('error');

Variation 2:

$conn = mysql_query();
if(!$conn) {//Error code here}

Variation 3:

try {
  $conn = mysql_query();
  if (!$conn) throw new Exception("mysql Error");
} catch(Exception $e) {
  echo $e->getMessage();
}

Upvotes: 4

James
James

Reputation: 76

if the update "fails" due to syntax error, or other mysql will return an error code on the actual mysql query and affected_rows will return with yet another error.

Php for example:

$qry = mysql_query("update blah where IamaSyntaxerror,33");
if ($qry === FALSE) { echo "an error has occured"; }

else  { mysql_affected_rows() == 0  means no updates occured

Upvotes: 2

Related Questions