How to know if a MySQL UPDATE query fails because information being supplied matches data already in the database?

I have a query where the user types a block of text in a textarea field. They are able to save this information in a database. The problem is that if they have not changed the information or if the information matches the data already in the database, I recieve a '0' for affected rows. Usually I display an error that says the query failed when there are no affected rows. How would I 'know' that the 0 affected 开发者_如何学运维rows is because the data already exists, so that I can display a more specific error?

Another reason you'd get 0 affected rows is if the UPDATE statement matches no rows. For instance:

UPDATE MyTable SET field = 'content' WHERE id = 1234;

Gives 0 affected rows if no row exists with id = 1234. This is not an error either, it's just an UPDATE that happened to match no rows.

The way to detect this case is to use SELECT to verify that there is such a row. If you can confirm the row exists, but the UPDATE said it affected 0 rows, then you know that the values you tried to change were in fact the rows already in the database.

SELECT COUNT(*) FROM MyTable WHERE id = 1234;

But the distinction may not be important. You could report an error if mysql_error() says there is one, as @BoltClock suggests.* If there is no error you could just report "no change" to the user.

* Note: you don't have to report the literal message returned by mysql_error(). The messages can be confusing to users, so it's a good idea to report something more friendly to them.

Since the query is successful MySQL should not return any error number (if it does it'll be 0). In that case, you can check if the number of affected rows is 0 and the error number returned is 0.

If you're coding in PHP, you can do something like this:

// ... mysql_query() with the UPDATE query

if (mysql_affected_rows() == 0 && mysql_errno() == 0)
{
    // Data was not changed
}

To supplement the answer above.

$sth = $Param->{dbh}->prepare($sql);   
$rows_affected = $sth->execute()  
or die "SQL Error: $DBI::errstr\n";      
if ( $rows_affected eq '0E0' ) {