Re: Updating only fields which users have changed

From: Jochen Daum (jochen.daum_at_cans.co.nz)
Date: 01/07/04 

Date: Wed, 07 Jan 2004 12:10:28 +1300

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
>
This is in most cases the simplest solution, if you do not run a
journal on who changed what in this table. In the case of such a
journal (which we run here pretty much on all tables), it would change
the journal timestamp, even if nothing has been updated.

>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.

Thats what I prefer to use.

>
>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

>
>Thanks in advance. 

-- 
Jochen Daum - CANS Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/