Re: Updating only fields which users have changed

From: Tony Marston (tony_at_marston-home.demon.co.uk)
Date: 01/08/04 

Date: 8 Jan 2004 01:41:22 -0800

Jochen Daum <jochen.daum@cans.co.nz> wrote in message news:<kdfmvv8ij4sfq33d277hlse7opvjgm4di0@4ax.com>...
> Hi !
>
> On 6 Jan 2004 08:28:43 -0800, pmorrison@4lpi.com (Jason) wrote:
>
> >Let's say I have an html form with 20 or 30 fields in it. The form
> >submits the fields via POST to a php page which updates a table in a
> >database with the $_POST vars. Which makes more sense?
> >
> >1) simply UPDATING the values for all fields in the table, whether or
> >not any particular field has actually changed
> >
> >2) running a second SELECT statement and comparing the $_POST vars to
> >the returned values, and only UPDATING those that have changed.
> >
> >3) including a hidden form field for each field with the original
> >values (so for each <input type="text" name="field1" size="10"
> >value="blah"> there would also be a <input type="hidden"
> >name="field1_orig" value="blah">), comparing $_POST['field1'] with
> >$_POST['field2'] and only updating those that have changed.
> >
> >There's very little chance that two users will be updating the same
> >table at the same time with this particular site, but anyone see any
> >particular problems with any of the above three methods? Would
> >doubling the number of $_POST vars (option 3) create a problem?
>
> No this is a completely separate issue and you will have a complete
> ***-up and data loss if it happens (and it will). You need to
> implement a locking strategy. There is pessimistic locking, which
> works like this:
> If someone opens the record for editing you change a flag to 'lock'
> and noone else can edit that record.
> This is in most cases unsuitable for the web, because you don't wanna
> call that guy in Siberia, who does your editing and went for a toilet
> break in the middle of editing to unlock it.
>
> Instead, you can use optmistic locking, which just puts a timestamp
> against the record, when it has been saved the last time. Then, when
> saving you check the timestamp against the one you had when you opened
> it for editing. If it is bigger - if someone saved it, while you were
> editing, you have to resolve that conflict. The simple and stupid way
> is to overwrite the other persons changes or to discard your own ones.
> Instead, implement a field by field merging strategy. For this you
> need to know the contents of what has been in before as well (option
> 3)
>
> HTH, Jochen

This is a good idea, and easy to implement. Before you issue the
UPDATE you issue a SELECT using the primary key plus the timestamp
value from the original SELECT. If the timestamp value has changed
(i.e. somebody else has updated the record since you last read it)
then the SELECT will not return a row and you can take whatever action
you deem necessary, such as telling the user that his update was
rejected due to a concurrent update.

Tony Marston
http://www.tonymarston.net/