php/mysql question regarding editing related tables

I am working on a form for editing and updating mysql data in several
related tables like person, phoneNumber,and e-mailAddress.

I need to display data from all three in order to know what records I am
editing, but I don't want to do updates on unchanged records (i.e. if
only phoneNumber changes, no need to update the other two).

simplistically I could do the following:

1 query all 3 tables
2 store data in hidden fields
3 display data
4 user edit
5 submit
6 compare original data to data in form fields to determine changes (or
use hasChanged flags)
7 update accordingly
8 go to 1

I'm curious what logical approach others have taken.

William Gill wrote on 26/03/2008 15:33:
> I am working on a form for editing and updating mysql data in several
> related tables like person, phoneNumber,and e-mailAddress.
>
> I need to display data from all three in order to know what records I am
> editing, but I don't want to do updates on unchanged records (i.e. if
> only phoneNumber changes, no need to update the other two).
>
> simplistically I could do the following:
>
> 1 query all 3 tables

hope this means: one single request over joined tables

> 2 store data in hidden fields
> 3 display data
> 4 user edit
> 5 submit
> 6 compare original data to data in form fields to determine changes (or
> use hasChanged flags)
> 7 update accordingly

or update whatever changes.

the choice may depends on your server roles, it mysql & php run on the
same machine, php will have a persistant connexion to the base, in that
case you can let mysql decides (verifies) if update is actually needed.
if mysql server runs on a separate server and if the cost of request
forward is not null, check in php code if update(s) are required.

Sylvain.

Sylvain wrote:
> William Gill wrote on 26/03/2008 15:33:
 
>
> hope this means: one single request over joined tables
>

Yes.

> or update whatever changes.
>
> the choice may depends on your server roles, it mysql & php run on the
> same machine, php will have a persistant connexion to the base, in that
> case you can let mysql decides (verifies) if update is actually needed.
> if mysql server runs on a separate server and if the cost of request
> forward is not null, check in php code if update(s) are required.

Please expound on letting MySQL decide.  I thought if I issued an update
statement, it will update.  I don't want unchanged records to get new
timestamps, and I don't think it efficient to update unnecessarily.

Are you saying do a comparison to see if data has changed in the WHERE
clause?

I'm not sure generically same server/ different server applies, but get
your point.  If that becomes an issue I can change where the logic is
performed.

William Gill wrote on 26/03/2008 16:39:
>
> Please expound on letting MySQL decide.  I thought if I issued an update
> statement, it will update.  I don't want unchanged records to get new
> timestamps, and I don't think it efficient to update unnecessarily.

I wasn't thinking about timestamps and yes they may be changed
unnecessarily.

I was thinking about performance issues only. Updating even with same
data one string field requires a quasi null time; the update of indexes,
if they exist, requires not null time; here I expect mysql to be smart
enough to not recompute indexes if the indexed data was not changed.

OOH, string comparison coded on php does not take a null time.

> Are you saying do a comparison to see if data has changed in the WHERE
> clause?

no, and I don't imagine a where clause to match that point.
a stored procedure can be a valuable intermediate solution:
performing the string comparison on typed mysql variables
will be (more or less) faster than in php code, conditional
update of the field within that stored procedure will take
benefit of the live connexion to the table.

Sylvain.

Keep your hidden values to a minimum, it will get messy keeping
original and working data as POST data.

Basically what I do:

1.Read in data
2.Store copy of data in a SESSION array
3.POST form of data, get input
4.validate
5.compare data with SESSION
6.UPDATE as needed


I guess you could also re-request old data from the DB before your
compare as well.  Both of them keeps your POST form from being too
unwieldy.