CODEWITHSUNDEEP
phpmysqlsql-update
tf.rz
tf.rz

Reputation: 1367

MySQL user updates row with the existing data

I am working on a PHP web application that allows the user to edit values grabbed from a database. The user clicks an 'edit' link, and the fields are populated with the data from the table in the database (if there is any). If there isn't any data, all of the fields just show up blank.

When the user clicks 'save', the form is submitted to POST, and those values are saved to the database with an "UPDATE" query.

I check the result of the query by checking for the "affected rows". If it's equal to 0, then the changes didn't go through and I tell the user.

However, I have a slight problem when the user updates with the exact same values that the row already contains.

For example, A row I have with columns, A, B, C, D which have the values 1, 2, 3, 4 respectively.

If the user edits that row, and submits with values 1, 2, 3, 4, the row will not be affected and my output message will say that the query failed when the "updates" are really the same as before and went through just fine.

Right now I'm stuck with outputting:

"The updated values are the same as the old ones OR there is something wrong with the tables and column names. Check the logs."

I send a message to the logs regarding this error. I then output the query just so the user can see.

The thing is, I want my program to be informative when it comes to errors. Is there a way to have the program classify an update that is the exact same as what the row originally was as.. successful?

        // construct query, execute query, check if any rows have been affected
    $query = "UPDATE table SET status='$status', client='$client' WHERE key='$key'";
    $result = $conn->query($query);
    if(mysqli_affected_rows($conn) === 0)
    {
        echo "Something went wrong. Entry not updated. Check the logs.\n";
        echo $query;
        error_log(mysqli_error($conn));
    }

I've also thought about just checking if $result === false or $result === true inside the if condition, but would that work?

Any insight is greatly appreciated.

Thanks!

Upvotes: 0

Views: 932

Answers (1)

jeroen
jeroen

Reputation: 91734

If you want to check for an error with your query, you can check for -1 instead of 0.

If mysqli_affected_rows returns 0 it means that nothing was updated because of no changed values or no matching WHERE condition. If you need to distinguish between these two, you would probably need to do an additional SELECT query.

And you need to use prepared statements with bound variables to avoid sql injection problems.

Upvotes: 1

Related Questions