仅更新或插入已更改的列

Question

I am experimenting trying to find the best and most efficient way to alter the data in a given table through a form using PHP.

The scenario is a list of items in a table, if you right click->edit an item, a request is made to MySQL for all the data and the fields are populated.

The user can change or leave the data untouched in any of the fields, and then presses save which sends everything back to PHP.

The easy way would be to just update all the columns regardless of whether or not they have changed, i.e.:

$this->model->set('name', 'some name string from the form', $itemId);
$this->model->set('price', 'number from the form', $itemId);
...etc...

So potentially I could change just the name and needlessly update the rest of the columns with the same data as what was received. (As a side question, does MySQL know this and ignore the update behind the scenes?)

Would a good way to perform an intelligent update be to compare two arrays? One that contains the original data and another with the data from the user. If values of a given index don't match, then it must have changed and so do the update?

i.e. a very simplified example:

if($submittedValues['name'] != $originalValues['name'])
{
    ...Update...
}

Answer 1

I guess you answer your question, and you could compare two array, either in your PHP code or using javascript and instead of sending every thing to the server, only send the changed values.

But in general I wouldn't care if I reset all the data, the process of affecting all fields again could be faster than comparing between old and new data in arrays, I would take much care if I was making many queries to the database but its only one update query

---

What could be interesting in test is, when the user lefts the fields empty, then the request will send an empty string, at the end it the update request will insert an empty string where a NULL value would have a better signification