Problem with UPDATE MySQL

Question

I have a bit of an issue with my code.

I'm making an administrative panel for users to add things to the database. On occasion, they might try to save data without changing it (open a dialog, click save without changing anything). This, of course, will make mysql_affected_rows() return '0' when checking to see if the UPDATE query worked.

Is there another query to make that will always UPDATE regardless of whether the data is the same or not (or can I modify a simple UPDATE query to always update)?

EDIT

This is for users who don't have programming experience. Of course you wouldn't want to update if there's no reason to, but when a user tries to update and it doesn't happen I end up showing a failure message. Rather than there being something wrong, its just it doesn't need to be updated. I need a way to show the user that, instead of a generic 'failure' message. If it failed for another reason, I still need to know.

MySQL is smart enough, not to make unnecessary updates - so why did you want to? I just simply print a message like 'Nothing to save here - no changes found.' to the client. — feeela
– feeela, Commented Aug 20, 2011 at 14:14

Evan Mulawski · Accepted Answer · 2011-08-20 15:41:43Z

4

From the MySQL Documentation:

If you set a column to the value it currently has, MySQL notices this and does not update it.

Instead of checking mysql_affected_rows, just check to see if the query was successful:

if(!mysql_query("UPDATE ..."))
{ 
  //failure
}
else
{
    $verification = mysql_query("SELECT ROW_COUNT() as rows_affected");
    $row = mysql_fetch_row($verification);
    $rows_affected = $row[0];

    if ($rows_affected > 0)
    {
        //update was performed
    }
    else
    {
        //no update was needed
    }
}

edited Aug 20, 2011 at 15:41

answered Aug 20, 2011 at 14:13

Evan Mulawski

55.5k16 gold badges122 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Phillip Over a year ago

I edited my entry to aim at more of what I'm looking for, hopefully.

Evan Mulawski Over a year ago

@Phillip: To show an error to your users, just populate the if block in the code above with a die(mysql_error()). You could also log the error in another table for further investigation.

Phillip Over a year ago

The only problem with that is that there is no error to report when the data is already there (which is my problem). It'd be great if it'd spit out something dealing with that but its not. It also seems inefficient to run another query just to see if the data is the same.

Evan Mulawski Over a year ago

@Phillip: So, if I understand correctly, you want to alert your users that no update was performed because the entered data was already in the table?

Phillip Over a year ago

Yes correct. But if there was another reason for the query not updating, I need to present them with that as well.

|

Collectives™ on Stack Overflow

Problem with UPDATE MySQL

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related