multiple database updates from 1 form

[Comcast]

Greetings,

I am writing a very simple script that pulls a resultset out of a database.
These rows are then made into form variables where the user has the option
to update multiple entries, hit submit and the system updates the
appropriate fields. I am using the convention of "variable[rowid]" in the
input form, it allows me to loop through all the results and update the
database.

This solution is working fine, but I hate the idea that I'm updating data
that isn't changing. I've considered keeping state when a form is shown,
then comparing the values between the original state, and if its different
then update the database.

I hate to re-invent the wheel, how is this handled in other applications?
I've tried to divine it out of some other programs, but I'm having trouble
finding an application that is doing something like what I am doing.

Any guidance here is appreciated.

--Brian

[B. Johannessen]

Re: multiple database updates from 1 form

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Comcast wrote:[color=blue]
> I am using the convention of "variable[rowid]" in the input form, it
> allows me to loop through all the results and update the database.
>
> This solution is working fine, but I hate the idea that I'm updating
> data that isn't changing.
>
> I hate to re-invent the wheel, how is this handled in other
> applications?[/color]

How about adding hidden form values "orig[rowid]" to the form,
and comparing "variable[rowid]" and "orig[rowid]" before you
do the update?


Bob

-----BEGIN PGP SIGNATURE-----
Comment: B. Johannessen <bob@db.org> - http://db.org/contact/en/

iD8DBQFAPEXEooi sUyMOFlgRAtyhAJ 0XgzeLE7LFICP1D ZQGZA/BbN+8OgCdFWjE
KhQ7tVyoQiPWrct cr6hpvUQ=
=Gk6p
-----END PGP SIGNATURE-----

[Fred H]

Re: multiple database updates from 1 form

[color=blue]
> This solution is working fine, but I hate the idea that I'm updating data
> that isn't changing. I've considered keeping state when a form is shown,
> then comparing the values between the original state, and if its
> different
> then update the database.[/color]

There's absolutely nothing wrong with updating data that hasn't really
changed.
It's actually much less work, and the code is much cleaner, than if you
try to
check for whether the data has changed or not.

But if you really want to, I'd suggest that you do a query to the database
when you recieve data from your form, and do a match then, instead of
sending
"orgdata" to the form and back to the script.

Then you should produce something like this:
$new['...']; //Assoc array with all form vars
$org['...']; //Assoc array with data from db

First you should check that $new and $org has all
the same fields:

$is_equal = true;
foreach($org as $key => $val) {
if(!isset($new[$key])) $is_equal = false;
}

If $is_equal is true when you're done, you're fit to go.

Now you need to loop throgh these two arrays and find
out which fields that has changed.

$upd = array(); //To store undated field->value pairs in.

foreach($org as $key => $val) {
if($org[$key] != $new[$key]) {
if(is_numeric($ org[$key])
array_push($upd ,"$key = $val");
else
array_push($upd ,"$key = '$val'");
}//EndOf if(data has changed)
}//EndOf foreach

This loop can of course be merged with the one that
checks for "equality", but I separated them for clarity.
If you merge them, you just use your $is_equal var as a
"safe to update db"-flag.

Now you need to produce a query string that you can pass to
the database:

$query = "UPDATE yourtable SET ".implode(",",$ upd)." WHERE id =
".$org['id'];

NB: This code has not been tested, and is likely to contain errors
and bugs. So use it as a suggestion to how you -could- solve the
problem, and not as a solution.

--
Fred H

void FredH::Contact( ) {
TextToSpeach.sa y("frode at age dee dee dot en oh");
}

[David Mackenzie]

Re: multiple database updates from 1 form

On Wed, 25 Feb 2004 04:17:51 GMT, "Comcast" <ewvea3dgc700 1 .@.
sneakemail.com> wrote:
[color=blue]
>This solution is working fine, but I hate the idea that I'm updating data
>that isn't changing. I've considered keeping state when a form is shown,
>then comparing the values between the original state, and if its different
>then update the database.[/color]

You needn't worry about things like that. The database is capable of
optimising itself.

From:

http://www.mysql.com/documentation/mysql/bychapter/manual_SQL_Syntax.html#UPDATE

"If you set a column to the value it currently has, MySQL notices this
and doesn't update it."

I'm sure other RDBMSs have similar optimisations.

--
David ( @priz.co.uk )

[Eric Bohlman]

Re: multiple database updates from 1 form

David Mackenzie <me@privacy.net > wrote in
news:2fso30pk56 h5b9h82pqptbg7t 8h5m76emu@4ax.c om:
[color=blue]
> On Wed, 25 Feb 2004 04:17:51 GMT, "Comcast" <ewvea3dgc700 1 .@.
> sneakemail.com> wrote:
>[color=green]
>>This solution is working fine, but I hate the idea that I'm updating
>>data that isn't changing. I've considered keeping state when a form is
>>shown, then comparing the values between the original state, and if
>>its different then update the database.[/color]
>
> You needn't worry about things like that. The database is capable of
> optimising itself.
>
> From:
> http://www.mysql.com/documentation/m..._SQL_Syntax.ht
> ml#UPDATE
>
> "If you set a column to the value it currently has, MySQL notices this
> and doesn't update it."
>
> I'm sure other RDBMSs have similar optimisations.[/color]

Sometimes, though, you want to keep track of when an item was last
modified, and in that case updating everything at once will destroy that
ability.

[Comcast]

Re: multiple database updates from 1 form

Thanks for the replys everyone. I think I like the javascript solution.
There is already a javascript dependancy on the application, so if they
dont' have it, the system wouldn't work anyway.

Thanks for your help!

--Brian