Re^4: New CGI Action Call

UPDATE table SET business = ? ..
       ^^^^^ this should be the table name users
[download]

If you add RaiseError=>1 to your connect statement like this

my $dbh = DBI->connect($dsn, $user, $pw, { RaiseError=>1 } );

you don't need all those or die statements

or die "Error Preparing:\n" . $stmt . "\nDBI returned: \n", $dbh->errs
+tr;
..
or die "Unable to execute query: " . $sth->errstr;
[download]

sub update_insert_Record {

  warn("522 update_insert_Record");
  my $post = $query->param("post");
  return if ($post);
  
  my $user_id = $query->param('user_id');

  my @form = qw(id username password pin position forename
                lastname business address1 address2 city state zip
                email phone_home phone_cell comments MJ MD DD DP);
    
  # UPDATE existing record
  my %newData = map{ $_ => $query->param{$_} } @form;  
    
  #Recover existing values from database and fill variables
  my $stmt = 'SELECT * FROM users 
              WHERE user_id = ?';

  warn("statement =  '$stmt'");    

  my $sth = $dbh->prepare($stmt);
  $sth->execute($user_id);    
  my $oldData = $sth->fetchrow_hashref();   

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

  # skip if no change
  if (@fields == 0){
    print "No update required\n";
  } else {
    # build sql
    my $fields = join ',',@fields;
    my $stmt = "UPDATE users SET $fields WHERE user_id = ?";
    #add id
    push @values,$user_id;
 
    warn("statement =  '$stmt'");    
 
    # prepare and execute sql
    # print "$stmt\n(@values)\n";         
    $sth = $dbh->prepare($stmt);
    $sth->execute(@values);
  }
}
[download]

update : changed first @fields to @form

poj

Comment on Re^4: New CGI Action Call Select or Download Code

Replies are listed 'Best First'.
Re^5: New CGI Action Call by tultalk (Monk) on Mar 25, 2018 at 17:00 UTC
Gad!! Correcting "table" to "user" and it worked. Humiliating. On to simplification as you suggest. Thanks	[reply]
Re^6: New CGI Action Call by marto (Cardinal) on Mar 25, 2018 at 17:47 UTC
You were told this an hour prior to being told again. Testing your SQL separately would be wise.	[reply]
Re^7: New CGI Action Call by tultalk (Monk) on Mar 26, 2018 at 00:03 UTC
I always do that unless I am overly confident. Seriously, I do test them. Speaking of SQL: Statement below in "=for comment" tests fine. The error messages when I execute within program say otherwise. I can't see the problem. `$stmt = "insert into users (user_id,username,password,pin,pos +ition,forename,lastname,business,address1,address2,city,state,zip, em +ail, phone_home, phone_cell, MJ, MD, DD, DP, comments) values ((SELEC +T AUTO_INCREMENT FROMinformation_schema.TABLES WHERE TABLE_SCHEMA=DAT +ABASE() AND TABLE_NAME='users'),CONCAT('bwm', (SELECT AUTO_INCREMENT +FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE()ANDTABLE_ +NAME='users')), ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";` [download] `my $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $ +stmt . "\nDBI returned: \n", $dbh->errstr; $value = $sth->execute ($password, $pin, $position, $forename, $ +lastname, $business, $address1, $address2, $city, $state, $zip, $emai +l, $phone_home, $phone_cell, $MJ, $MD, $DD, $DP, $comments) or die "U +nable to execute query: " . $sth->errstr; my $new_id = $sth->{'mysql_insertid'};` [download] =for comment insert into users (user_id, username,password,pin,position, fore +name,lastname,business, address1, address2,city,state,zip, email ,pho +ne_home, phone_cell,MJ,MD,DD,DP, comments) values ( (SELECT AUTO_INCR +EMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AN +D TABLE_NAME='users'), CONCAT('bwm', (SELECT AUTO_INCREMENT FROM info +rmation_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='u +sers')), 'vmmv', 'BbwmB','3', 'Lucifer','Jackson','BWMLLC', '1610 W U +dall St','Apt 1', 'Jasonville','UT','87167','dingy@yahoo.com','(203) +418-5467','(203) 245-4567','2017-01-31','120.00', '2018-03-21','2019- +03-20','Blah Blah') =cut [download]	[reply] [d/l] [select]
Re^8: New CGI Action Call by marto (Cardinal) on Mar 26, 2018 at 09:26 UTC
Re^8: New CGI Action Call by poj (Abbot) on Mar 26, 2018 at 06:54 UTC