in reply to Re^3: CGI Action call
in thread CGI Action call

Speaking of placeholders. Playing around here. Just conceptualizing.

Probably been done a million times before

Can something sort of like this this work for automatically selecting the fields needing updating?

Run a select * using user_id returning one record and load values into the $oldxxx variables then compare to the values submitted in the update query. 

            $placeholders;
            $replacement-fields;
            
            
            if ($password <> $oldpassword){
              $placeholders = $placeholders . "?,";
              $replacement-fields = $replacement-fields . "password = 
+'$password',";   
            }
            if ($forename <> $oldforename)){
              $placeholders = $placeholders . "?,";
              $replacement-fields = $replacement-fields . "forename = 
+'$forename',;
            }
            if ($lastname.value <> $oldlastname){
              $placeholders = $placeholders . "?,";
              $replacement-fields = $replacement-fields . "lastname = 
+'$lastname',";   
            }
            if ($business <> $oldbusiness){
              $placeholders = $placeholders . "?" 
              $replacement-fields = $replacement-fields . "business = 
+'$business',";   
            }
            if ($city <> $oldcity){
              $placeholders = $placeholders . "?," 
              $replacement-fields = $replacement-fields . "city = '$ci
+ty',";   
            }
            if ($zip <> $oldzip){
              $placeholders = $placeholders . "?,"   <----------------
+----------
              $replacement-fields = $replacement-fields . "zip = '$zip
+',";  <--- Problem with last , from last field requiring update. Pull
+ that off before using the two variables   
            }
            
            
           $query = "UPDATE users SET $placeholders where user_id = '$
+user_id'";

           $sth = $dbh->prepare($query);

           $sth->execute($replacement-fields) or die "Unable to execut
+e query: " . $sth->errstr;

[download]

Replies are listed 'Best First'.
Re^5: CGI Action call
by poj (Abbot) on Mar 17, 2018 at 08:12 UTC

    Use hash keys to avoid lots of variables. Push the field names, values onto arrays and use join to create the statement

    #!perl
use strict;

my $user_id = 1;

my $old = {
  password => 'password', forename  => 'forename',
  lastname => 'lastname', business => 'business',
  city     => 'city',     zip      => 'zip'
};

my $new = {
  password => 'password1',  forename  => 'forename',
  lastname => 'lastname1', business => 'business',
  city     => 'city1',     zip      => 'zip1'
};

my @fields;
my @values;
# compare old v new
for (sort keys %$new){
  if ($new->{$_} ne $old->{$_}){
    push @fields,"$_ = ?";
    push @values,$new->{$_};
  }
}

# skip if no change
if (@fields == 0){
  print "No update required\n";
} else {
  # build sql
  my $fields = join ',',@fields;
  my $stmt = "
   UPDATE table 
   SET $fields
   WHERE user_id = ?";
   # add id
   push @values,$user_id;
   
   # prepare and execute sql
   print "$stmt\n(@values)\n"; 
}

    [download]
    poj
[reply]
[d/l]
Re^5: CGI Action call
by Corion (Patriarch) on Mar 17, 2018 at 07:49 UTC

    Yes you can do that.

    But to do that in a sensible way, please learn about the data structures that Perl offers and also read the DBI documentation.

    ->execute takes a list, not a comma-separated string. So, instead of building a comma-separated string, which still has the same interpolation problems, use an array to collect the changed fields.

    Also, it seems you are reinventing your own ORM. You might want to take a look at DBIx::Class or Class::DBI instead.

[reply]
[d/l]

In Section Seekers of Perl Wisdom