[Solved]: How to get only changed parameter names from CGI script on form submit?

[Perl300]

Hello Monks,

I have dynamic webpage which on submit calls itself (a cgi script). There are multiple HTML text boxes on the form. On form submit all these form element names and their values (parameter names and values) are submitted to cgi script.

I can get this entire list of parameter name and values using $query->Dump; Any change in these parameters will have to be updated in DB so I am running DB queries for each of those parameters which results in many DB queries being fired and slowing down the response on webpage. I can't run all the updates in single query as well due to some business logic. So what I am thinking is to run the queries only for those elements/parameters that have been changed.

Is there any way to get list of only those parameters that have changed, so that I can run DB queries only for those parameters and not for all? Or any other better way to do it?

Please forgive me if the description doesn't make it clear but I tried my best and can answer any other specific question to make it clearer.

[NetWallah]

Similar to Corion's suggestion:

While generating the form, include hidden fields containing the md5 (or smaller-sized) checksums for the "sent" content of each field.

On return, compare the checksum with the received content, and act accordingly.

[Corion]

Why not ask the database about the current state and then build the UPDATE actions as necessary on form submission?

Alternatively, send the user two variables for each checkbox, one indicating the old state and one indicating the new state. If the two differ, do the update.

[tye]

No, you want to update the values that the user updated, not the ones that differ from the DB. As a general principle, if a value changes in the DB after the page was loaded and before the form was submitted, then you should preserve the value in the DB, not clobber somebody else's update with this user's non-update. That generally leads to fewer problems and surprises.

[tangent]

An alternative way to do it would be to split up your form into discrete elements, perhaps modelled on your database tables, and where each element or group of elements is wrapped in its own form. For each of these forms you would have separate "Update" buttons, so a much smaller number of parameters are sent to the CGI script.

[muba]

I am running DB queries for each of those parameters

Somehow I doubt that each of these parameters correspond to columns in different tables. Most likely, you'll only need to update one or two tables for each submit in most circumstances. So instead of updating the database for each parameter¹, you could do one or two mass updates²

# 1

# (I don't know which module you're using for your $query->Dump expres
+sion,
#  so I'll simply be using the old school CGI.pm here)

use CGI 'param';
use DBI;
my $dbh = DBI->connect(...) or die ...;
my $id = param 'id';

# Hit the database with an UPDATE for every parameter
for my $p (param) {
    next if $p eq 'id';
    my $sql = sprintf("UPDATE mytable SET %s = ? WHERE id = ?", $p)
    $dbh->do($sql, undef, param($p), $id);
}

# 2

# (again, just CGI.pm)

use CGI 'param';
use DBI;
my $dbh = DBI->connect(...) or die ...;

my $id = param 'id';
my %update = map { $_ ne 'id' ? ($_ => param($_)) : () } param;
my @bind = ();
# First, build one large UDPATE statement
# and collect the bind values while we're at it
my $sql = "UPDATE mytable SET " .            # Doing this from the top
+ of my head,
          join(", ",                         # untested, but it should
+ get the 
            grep defined, map {              # idea across
                if ($_ ne 'id') {
                    push @bind, param($_);
                    sprintf "%s = ?", $_
                }
                else { undef }
            } param
          )
;

# And then hit the database with that UPDATE statement just once.
$dbh->do($sql, undef, bind);

[Perl300]

Thank you all for your inputs. I finally did create a hidden element and added and array of all parameter values before form is submitted into that hidden element.

After the form is submitted I comparing all the values from this hidden element with the values sent in form and when I find any difference then I am running DB query only for the corresponding parameter. It needed a bit of formatting to make both the array elements in same format and sequence so only difference in parameter values will be caught.