Updating only fields which users have changed

[Jason]

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_or ig" 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?

Thanks in advance.

[André Næss]

Re: Updating only fields which users have changed

Jason:
[color=blue]
> 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_or ig" value="blah">), comparing $_POST['field1'] with
> $_POST['field2'] and only updating those that have changed.[/color]

I see no reason not to choose option 1. This is the simplest solution, and I
can't see any problems with it. Do you? Seeing as you list the other two
options I mean...

André Næss

[max power]

Re: Updating only fields which users have changed

In article <cf188409.04010 60828.5f4377f6@ posting.google. com>, Jason
<pmorrison@4lpi .com> wrote:
[color=blue]
> 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_or ig" 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?
>
> Thanks in advance.[/color]

If you're using mySQL as your database, then 1) is what happens anyway
- mySQL automatically detects that you're updating a field with the
same value it already contains, and doesn't actually update that field.
Not sure if this is the same for other databases thet can be used with
php.

You can also pre-fill your html form fields with current data from your
database by running a query before the form is displayed - the user
can then see the current values and edit as necessary. The html form
code would be:
<input type="text" name="field1" value="<?= $row['field1'] >">
This is a sort of similar idea to your option 3) above, but less messy,
imo.

This is the technique I usually use for doing form based database
updates. An add/insert is similar but there's no data to pre-fill the
form with.

hth.

[Jochen Daum]

Re: Updating only fields which users have changed

Hi !

On 6 Jan 2004 08:28:43 -0800, pmorrison@4lpi. com (Jason) wrote:
[color=blue]
>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
>[/color]
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.
[color=blue]
>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_o rig" value="blah">), comparing $_POST['field1'] with
>$_POST['field2'] and only updating those that have changed.[/color]

Thats what I prefer to use.
[color=blue]
>
>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?[/color]

No this is a completely separate issue and you will have a complete
fuck-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
[color=blue]
>
>Thanks in advance.[/color]

--
Jochen Daum - CANS Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.

http://sourceforge.net/projects/phpdbedittk/

[Jason]

Re: Updating only fields which users have changed

André Næss <andrena.spamre allysucks@ifi.u io.no> wrote in message news:<btf44q$e7 4$1@maud.ifi.ui o.no>...[color=blue]
>
> I see no reason not to choose option 1. This is the simplest solution, and I
> can't see any problems with it. Do you? Seeing as you list the other two
> options I mean...[/color]

Thanks everyone for the responses. I think I was a little too generic
in my original post. Right now I'm trying to write some classes for
updating a database. Sometimes users will be submitting a form that
has fields for one record in one table, but sometimes they will be
using a form with 30 or 40 rows of fields for 30 or 40 records.

I understand in the first case that my option one (simply constructing
an UPDATE statement with all the fields) is the simplest, and that
building a UPDATE statement of only the fields that have actually
changed probably won't save much on the database end. But what about
the latter case, where there will be 30 or 40 UPDATE statements? In
many cases only 1 or 2 of the records may be changed at all, so if the
script only submits 1 or 2 UPDATEs instead of 30 or 40 there should be
some savings, right?


And here's another question. I've been thinking about the whole
separating business logic from presentation logic deal, and I've got
an idea but I'm not sure if I'm over thinking all of this.

Let's say I have an entity called thing. I'll have a file called
thing.php which the users will actually call up in their browser,
usually following links like thing.php?actio n=view&thing_id =123 or
some such. Depending on the $_GET vars, thing.php will include
another file called thing_form.php, thing_view.php, thing_list.php,
etc., which has all the display logic and html they will see. It will
also include a file thing.inc.php which has a class with all the
functions and business logic for thing.

Does this make sense, or am I pulling things apart too much? When
I've done little play pages in the past I've lumped all this stuff
together in one page, so will all the includes (at least two each time
a user goes to thing.php) slow everything down? Also, I'd be
interested in hearing how everyone likes to keep their
directories--lots of little, specific purpose files or a few large
files which do everything?


Thanks again. This newsgroup is such a great resource.

[André Næss]

Re: Updating only fields which users have changed

Jason:
[color=blue]
> André Næss <andrena.spamre allysucks@ifi.u io.no> wrote in message
> news:<btf44q$e7 4$1@maud.ifi.ui o.no>...[color=green]
>>
>> I see no reason not to choose option 1. This is the simplest solution,
>> and I can't see any problems with it. Do you? Seeing as you list the
>> other two options I mean...[/color]
>
> Thanks everyone for the responses. I think I was a little too generic
> in my original post. Right now I'm trying to write some classes for
> updating a database. Sometimes users will be submitting a form that
> has fields for one record in one table, but sometimes they will be
> using a form with 30 or 40 rows of fields for 30 or 40 records.
>
> I understand in the first case that my option one (simply constructing
> an UPDATE statement with all the fields) is the simplest, and that
> building a UPDATE statement of only the fields that have actually
> changed probably won't save much on the database end. But what about
> the latter case, where there will be 30 or 40 UPDATE statements? In
> many cases only 1 or 2 of the records may be changed at all, so if the
> script only submits 1 or 2 UPDATEs instead of 30 or 40 there should be
> some savings, right?[/color]

Yes there will be savings, but I regard it to be a case of premature
optimization, the update statements in question should finish fairly
quickly. On the other hand if you can avoid the redundant updates without
much extra code then I guess you might as well just go ahead and do it.
[color=blue]
> And here's another question. I've been thinking about the whole
> separating business logic from presentation logic deal, and I've got
> an idea but I'm not sure if I'm over thinking all of this.
>
> Let's say I have an entity called thing. I'll have a file called
> thing.php which the users will actually call up in their browser,
> usually following links like thing.php?actio n=view&thing_id =123 or
> some such. Depending on the $_GET vars, thing.php will include
> another file called thing_form.php, thing_view.php, thing_list.php,
> etc., which has all the display logic and html they will see. It will
> also include a file thing.inc.php which has a class with all the
> functions and business logic for thing.
>
> Does this make sense, or am I pulling things apart too much? When
> I've done little play pages in the past I've lumped all this stuff
> together in one page, so will all the includes (at least two each time
> a user goes to thing.php) slow everything down? Also, I'd be
> interested in hearing how everyone likes to keep their
> directories--lots of little, specific purpose files or a few large
> files which do everything?[/color]

Sounds quite similar to schemes I've been using. I generally have
modularized systems (where a typical module is "user" or "document" or some
other entity in the domain of discourse). Each module offers the normal
CRUD (Create Read Update Delete) operations, and I usually have a handler
or driver which takes care of the mapping from _GET variables to what
should actually happen. How you do things from here depends on the
complexity on your application. In general, start simple, and break things
up on demand.

File sizes and what you put in what file really don't matter that much if
you ask me, the most important thing is that you have a well designed
application with fairly small specialized functions that are composed
properly and that have clear responsibilitie s. A good editor should make it
simple to browse the code.

André Næss

[André Næss]

Re: Updating only fields which users have changed

Jason:
[color=blue][color=green]
>> Yes there will be savings, but I regard it to be a case of premature
>> optimization, the update statements in question should finish fairly
>> quickly. On the other hand if you can avoid the redundant updates without
>> much extra code then I guess you might as well just go ahead and do it.[/color][/color]
[color=blue]
> In general, do most people just run everything in $_POST through a
> regular expression or something to look out for malicious entry? I
> guess I'm really wonderring if what I'm saving by trimming UPDATES and
> not validating unimportant data outweighs whatever cost there is to a
> larger $_POST.[/color]

I use a form handling system I've written which takes care of validation.
All I do is declare the types of the different fields and what constraints
apply to them (for example, an integer between 1 and 10 or more complex
types like date and credit card). This validation shouldn't be very time
consuming, so again there is little to save.
[color=blue]
> One more question, if it's not too much bother--do you find yourself
> representing your database fields as a bunch of class variables or an
> array or some such, or do you end up just using $_POST and the array
> returned by the database? This seems to defeat the 3-tier-ishness a
> little since adding a field requires updating the database and the
> class, but I don't really see any way around it that I like. I don't
> want to query the database for the table definition every time I
> create a new instance of an object.[/color]

There is no way around it, changes to the database must somehow be reflected
by changes in the code. If there was a closer fit between the DB and the
programming language then that might have been avoidable, and I guess it's
possible to write a generic intergration system which acts as a database
abstraction layer and allows you to design the database using some sort of
schema language which can then be used in PHP, but to me it seems like a
bit overkill :)

I have used both solutions where classes represents entities in the DB and
where I just use functions and pass the data along in arrays. I find the
latter simpler in PHP because of it's rather weak OOP support, but I guess
it's largely a matter of taste. In most cases the difference is merely
notational, e.g. you write $person->update($newDat a) (Or possibly
Person::update( $newData)) instead of update_person($ newData).

André Næss

André Næss

[Jason]

Re: Updating only fields which users have changed

André Næss <andrena.spamre allysucks@ifi.u io.no> wrote in message news:<bth5t5$ko 1$1@maud.ifi.ui o.no>...[color=blue]
> Jason:
>[color=green]
> > André Næss <andrena.spamre allysucks@ifi.u io.no> wrote in message
> > news:<btf44q$e7 4$1@maud.ifi.ui o.no>...[color=darkred]
> >>
> >> I see no reason not to choose option 1. This is the simplest solution,
> >> and I can't see any problems with it. Do you? Seeing as you list the
> >> other two options I mean...[/color]
> >
> > Thanks everyone for the responses. I think I was a little too generic
> > in my original post. Right now I'm trying to write some classes for
> > updating a database. Sometimes users will be submitting a form that
> > has fields for one record in one table, but sometimes they will be
> > using a form with 30 or 40 rows of fields for 30 or 40 records.
> >
> > I understand in the first case that my option one (simply constructing
> > an UPDATE statement with all the fields) is the simplest, and that
> > building a UPDATE statement of only the fields that have actually
> > changed probably won't save much on the database end. But what about
> > the latter case, where there will be 30 or 40 UPDATE statements? In
> > many cases only 1 or 2 of the records may be changed at all, so if the
> > script only submits 1 or 2 UPDATEs instead of 30 or 40 there should be
> > some savings, right?[/color]
>
> Yes there will be savings, but I regard it to be a case of premature
> optimization, the update statements in question should finish fairly
> quickly. On the other hand if you can avoid the redundant updates without
> much extra code then I guess you might as well just go ahead and do it.[/color]

I get your point. I'm actually re-writing a bunch of poorly-organized
code so I'm trying to think ahead. There are other issues as well.
For example, if I'm already checking to see if a field has changed, I
don't need to validate any of the non-changing data that's submitted.

In general, do most people just run everything in $_POST through a
regular expression or something to look out for malicious entry? I
guess I'm really wonderring if what I'm saving by trimming UPDATES and
not validating unimportant data outweighs whatever cost there is to a
larger $_POST.
[color=blue][color=green]
> > And here's another question. I've been thinking about the whole
> > separating business logic from presentation logic deal, and I've got
> > an idea but I'm not sure if I'm over thinking all of this.
> >
> > Let's say I have an entity called thing. I'll have a file called
> > thing.php which the users will actually call up in their browser,
> > usually following links like thing.php?actio n=view&thing_id =123 or
> > some such. Depending on the $_GET vars, thing.php will include
> > another file called thing_form.php, thing_view.php, thing_list.php,
> > etc., which has all the display logic and html they will see. It will
> > also include a file thing.inc.php which has a class with all the
> > functions and business logic for thing.
> >
> > Does this make sense, or am I pulling things apart too much? When
> > I've done little play pages in the past I've lumped all this stuff
> > together in one page, so will all the includes (at least two each time
> > a user goes to thing.php) slow everything down? Also, I'd be
> > interested in hearing how everyone likes to keep their
> > directories--lots of little, specific purpose files or a few large
> > files which do everything?[/color]
>
> Sounds quite similar to schemes I've been using. I generally have
> modularized systems (where a typical module is "user" or "document" or some
> other entity in the domain of discourse). Each module offers the normal
> CRUD (Create Read Update Delete) operations, and I usually have a handler
> or driver which takes care of the mapping from _GET variables to what
> should actually happen. How you do things from here depends on the
> complexity on your application. In general, start simple, and break things
> up on demand.[/color]

One more question, if it's not too much bother--do you find yourself
representing your database fields as a bunch of class variables or an
array or some such, or do you end up just using $_POST and the array
returned by the database? This seems to defeat the 3-tier-ishness a
little since adding a field requires updating the database and the
class, but I don't really see any way around it that I like. I don't
want to query the database for the table definition every time I
create a new instance of an object.
[color=blue]
> File sizes and what you put in what file really don't matter that much if
> you ask me, the most important thing is that you have a well designed
> application with fairly small specialized functions that are composed
> properly and that have clear responsibilitie s. A good editor should make it
> simple to browse the code.[/color]

True. I'm just trying to wrap my head around some of the big-picture
stuff. I tend to think in terms of user needs and requirements,
interface and information design, etc. first and then write code that
does whatever I need it to do. I'm trying to think more in terms of
code organization and reusability, though, as I work on more projects.

Thanks.

[Tony Marston]

Re: Updating only fields which users have changed

pmorrison@4lpi. com (Jason) wrote in message news:<cf188409. 0401070622.4ea7 3c2@posting.goo gle.com>...[color=blue]
> André Næss <andrena.spamre allysucks@ifi.u io.no> wrote in message news:<btf44q$e7 4$1@maud.ifi.ui o.no>...[color=green]
> >[/color][/color]
[color=blue]
> And here's another question. I've been thinking about the whole
> separating business logic from presentation logic deal, and I've got
> an idea but I'm not sure if I'm over thinking all of this.[/color]

What you are talking about here is the 3 tier architecture in which
you completely separate the presentation, business and data access
logic. I have created an entire development framework/infrastructure
using this architecure which is described in
http://www.tonymarston.net/php-mysql...plication.html. You can
run the application online to see it working, then download all the
code to see how it's done.

Tony Marston

http://www.tonymarston.net/

[Tony Marston]

Re: Updating only fields which users have changed

pmorrison@4lpi. com (Jason) wrote in message news:<cf188409. 0401060828.5f43 77f6@posting.go ogle.com>...[color=blue]
> 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[/color]

This will work with MySQL as it will only update those fields which
have actually changed.
[color=blue]
> 2) running a second SELECT statement and comparing the $_POST vars to
> the returned values, and only UPDATING those that have changed.[/color]

This is the method I use. It may seem overkill, but the reason is that
in the near future I am going to implement an audit trail system in
which case I will only want to log those fields which have actually
changed. This means that I must have a copy of the original data as
well as the new data submitted by the user. My audit trail will then
be able to show both the "before" and "after" values for individual
columns.

Another possible reason for redoing the SELECT before an update is
that you can include some of the original values in the WHERE clause
(in addition to the primary key) as this will not return an entry if
any of those values have been changed by another user. This prevents a
2nd update from overwriting the 1st update.
[color=blue]
> 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_or ig" value="blah">), comparing $_POST['field1'] with
> $_POST['field2'] and only updating those that have changed.[/color]

This is NOT a good idea as it bloats the size of the HTML file which
you send out, thus slowing down the transmission times. It also
requires code to add these extra values to the HTML output, then
another set of code to pick out and compare these values when the form
is submitted. With all this extra code the potential for accidentally
introducing bugs is enormous. It is easier and more reliable to simply
redo the sql SELECT, and it will not be any slower than executing all
that extra code to insert/extract all those extra hidden fields from
the HTML document.
[color=blue]
> 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?
>
> Thanks in advance.[/color]

No problem.

Tony Marston

http://www.tonymarston.net/

[Tony Marston]

Re: Updating only fields which users have changed

Jochen Daum <jochen.daum@ca ns.co.nz> wrote in message news:<kdfmvv8ij 4sfq33d277hlse7 opvjgm4di0@4ax. com>...[color=blue]
> Hi !
>
> On 6 Jan 2004 08:28:43 -0800, pmorrison@4lpi. com (Jason) wrote:
>[color=green]
> >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_o rig" 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?[/color]
>
> No this is a completely separate issue and you will have a complete
> fuck-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[/color]

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/

[Geoff Berrow]

Re: Updating only fields which users have changed

I noticed that Message-ID:
<cf188409.04010 60828.5f4377f6@ posting.google. com> from Jason contained
the following:
[color=blue]
>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_o rig" value="blah">), comparing $_POST['field1'] with
>$_POST['field2'] and only updating those that have changed.[/color]

I recently did one where I put a checkbox next to the record and
required the user to check the box if they want to update it. This also
serves as a visual reminder to the user of the records they have edited.
To save effort for JavaScript enabled browsers I added some JS that
automatically checks the box when the value is changed.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/