PHP Mysql Update issue

Question

I'm having an issue that I can't quite figure out. I have a bit of code that allows a user to pick, let's say, which type of fruit is their favourite. If they've previously selected 'apples' as their favourite and want to change it to 'oranges' - the script performs well.

But if they select 'apples' when they've already selected 'apples' the MYSQL Update call breaks down and returns an error. It's like it won't write over itself with the same data, that the new value has to be unique. I'm at a loss.... Here's the update bit:

    // UPDATE THEIR FRUIT SELECTION...
    $q = "UPDATE account SET fav_fruit='" . $row['fruit'] . "' WHERE act_id=$act_id LIMIT 1";       
    $r = @mysqli_query ($dbc, $q);
    if (mysqli_affected_rows($dbc) == 1) { // If it ran OK.
           echo 'success!';

    } else { 
        echo 'oops!';
    }

Again, this works so long as the new selection and what's in the database aren't the same. If they are: I get the oops! error.

the error is nondescript - my own text, not a mysql standard error: "The user could not be updated due to a system error." — WillHerndon
– WillHerndon, Commented Jan 24, 2011 at 4:43
Use of @ to suppress errors is usually frowned upon. It's much better to just set error_reporting() in the script or PHP.ini to none. — Bailey Parker
– Bailey Parker, Commented Jan 24, 2011 at 4:44
Yes, as zerkms has said, remove the @ t, and check what mysqli_query's returning. If it's returning FALSE, then the query failed and mysqli_error() will return the error message. Also inspect your $q to see what the query string is for one of those "update to same" queries that's not working. — Marc B
– Marc B, Commented Jan 24, 2011 at 4:44
Hey - took away the @ (error reporting is on) and there's no change. Also - the query works fine when not selecting the same value as what's already in the database. — WillHerndon
– WillHerndon, Commented Jan 24, 2011 at 4:50

Bailey Parker · Accepted Answer · 2011-01-24 04:43:10Z

1

Why would you need to update a field to contain a value it already contains? Regardless, this can be fixed by altering the table structure. You need to remove the unique flag from the fav_fruit column.

answered Jan 24, 2011 at 4:43

Bailey Parker

15.9k5 gold badges58 silver badges93 bronze badges

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

4 Comments

Bailey Parker Over a year ago

Is there any reason that you need to update a value to the value already stored?

WillHerndon Over a year ago

Kind of - I mean they need to be able to select different values and change this whenever they need to. If there was a way to basically say - "Hey, you've selected this choice, but here are others" and just leave out the value that's already stored, that'd be cool.

Bailey Parker Over a year ago

Temporary solution: How about querying the DB before you insert the new value to check if the value is the one currently stored. This seems unnecessary, though, because does the query need to succeed if the value is already stored?

WillHerndon Over a year ago

That sounds like it should work - I'm trying to put it together real quick to test it...

Collectives™ on Stack Overflow

PHP Mysql Update issue

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related