Re^10: CGI Action call

#Search Records & Return Found 3 buttons
# 0 Member ID
# 1 Last Name 
# 2 Business Name 
elsif ($action eq "updatetable_167"){
    warn("Entered updatetable_167");
    my $kind = $query->param('kind');
     my $searchterm = $query->param('searchterm');
    my $result;
    warn("searchterm = '$searchterm'");

    my $searchfield = "";

    if ($kind == 0) {
      $searchfield = 'user_id';
    } 
    if ($kind == 1) {
      $searchfield = 'lastname';    
    }
    if ($kind == 2) {
      $searchfield = 'business';    
    }
    
    warn("searchfield =  '$searchfield'");
        
#    my $stmt = "SELECT * FROM users WHERE $searchfield = ?"; comment 
+out for testing
    my $stmt = "SELECT * FROM users WHERE user_id = 15";

    warn("statement =  '$stmt'");     #of course meaning nothing with 
+placeholder ?
    
    my $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stm
+t . "\nDBI returned: \n", $dbh->errstr;
    
    $sth->execute($searchterm) or die "Unable to execute query: " . $s
+th->errstr;    
    
    warn("Finished sub search record");    
    
    
    $result = generateResponseHash($sth);
    
#   code # below is used to populate an html table and sent back to th
+e client. Works fine for testing     
#    $result = generateMemberDataSet($sth);
    
    if ($result == 1) {
      warn("Failed Search: '$searchfield' equal to '$searchterm' "); 
    }      
}
[download]

sub generateResponseHash{
   my $sth = shift;   
 
   $sth->execute () or die "Unable to execute query: " . $sth->errstr;

   my $count = $sth->rows;

   warn("count = '$count'");
   if ($count == 0) {
     return 1;
     }
   
   my $ref = $sth->fetchrow_hashref ();
   
#   print "Hello Output File\n"; #test stdout redirect to dumper.log w
+orks.

   warn("generateResponseHash ref: '$ref'");
   my $hash = hash_display_listing ($ref);
   $sth->finish ();
   completeResultHash( $hash ); 

}
[download]

Looks like sub below doe not generate a hash form the data supplied.

sub hash_display_listing{
my $ref = @_;
#warn("hash display_listing: '$ref->{"id"}'");

  my $hash = (
    'id'        =>$ref->{"id"},
    'user_id'   =>$ref->{"user_id"},
    'username'  =>$ref->{"username"},
    'password'  =>$ref->{"password"},
    'pin'       =>$ref->{"pin"},
    'position'  =>$ref->{"position"},
    'forename'  =>$ref->{"forename"},
    'lastname'  =>$ref->{"lastname"},
    'business'  =>$ref->{"business"},
    'address1'  =>$ref->{"address1"},
    'address2'  =>$ref->{"address2"},
    'city'      =>$ref->{"city"},
    'state'     =>$ref->{"state"},
    'zip'       =>$ref->{"zip"},
    'phone_home'=>$ref->{"phone_home"},
    'phone_cell'=>$ref->{"phone_cell"},
    'email'     =>$ref->{"email"},
    'comments'  =>$ref->{"comments"},
    'MJ'        =>$ref->{"MJ"},
    'MD'        =>$ref->{"MD"},
    'DD'        =>$ref->{"DD"},
    'DP'        =>$ref->{"DP"},
   );

print Dumper ($hash);
[download]

    my $stmt = "SELECT * FROM users WHERE $searchfield = ?";
    
    warn("statement =  '$stmt'");    
    
    my $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stm
+t . "\nDBI returned: \n", $dbh->errstr;
    
    $sth->execute($searchterm) or die "Unable to execute query: " . $s
+th->errstr;
[download]

Using the placeholder ? in select I get "1" printed the dumper.log HELP!!!

Comment on Re^10: CGI Action call Select or Download Code

Replies are listed 'Best First'.
Re^11: CGI Action call by poj (Abbot) on Mar 14, 2018 at 15:36 UTC
In addition to Corion's comments the first line of this sub is incorrect, the scalar `$ref` will be 1, the count of elements in `@_` `sub hash_display_listing{ # my $ref = @_; my ($ref) = @_; # or # my $ref = shift ;` [download] `$sth->execute($searchterm) or die "Unable to execute query: " . $sth->errstr; warn("Finished sub search record"); $result = generateResponseHash($sth);` [download] You are calling `$sth->execute()` twice, first time with a parameter and again in `generateResponseHash($sth)` without parameter Breaking long code into subroutines is usually good practice but be careful not to overdo it :) Read more... (2 kB) poj	[reply] [d/l] [select]
Re^12: CGI Action call by tultalk (Monk) on Mar 14, 2018 at 20:34 UTC
JSON I cut off all the sub calls after the action updatetable_167 call. my $stmt = "SELECT * FROM users WHERE $searchfield = ?"; warn("statement = '$stmt'"); my $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stm +t . "\nDBI returned: \n", $dbh->errstr; $sth->execute($searchterm) or die "Unable to execute query: " . $s +th->errstr; my $refresult = $sth->fetchrow_hashref(); my $count = $sth->rows; warn("count = '$count'"); if ($count == 0) { warn("Failed Search: '$searchfield' equal to '$searchterm' "); exit(1); } else { warn("count = '$count'"); my $json = JSON->new; $json->canonical(1); $json = encode_json($refresult); print $json; warn("Finished print '$json_str'"); exit(0); } [download] output: `{"DD":"2019-01-30","DP":"2018-12-31","MD":"120.00","MJ":"2018-01-30"," +address1":"1471 Meeks Rd","address2":null,"business":"JZ Electroplati +ng","city":"Warran","comments":"This is a test entry","email":"jze@ya +hoo.com","forename":"John","id":57,"lastname":"Zinzer","password":"12 +34","phone_cell":"517-240-1004","phone_home":"517-233-4378","pin":"Jb +wmZ","position":"General Member","state":"MI","user_id":19,"username" +:"bwm19","zip":"45789-2334"}` [download] Don;t understand why not in order after `$json->canonical(1);`	[reply] [d/l] [select]
Re^13: CGI Action call by Corion (Patriarch) on Mar 14, 2018 at 20:44 UTC
Why do you think the keys are not in sorted order? To me, they look sorted: `{ "DD":"2019-01-30", "DP":"2018-12-31", "MD":"120.00", "MJ":"2018-01-30", "address1":"1471 Meeks Rd", "address2":null, "business":"JZ Electroplating", "city":"Warran", "comments":"This is a test entry", "email":"jze@yahoo.com", "forename":"John", "id":57, "lastname":"Zinzer", "password":"1234", "phone_cell":"517-240-1004", "phone_home":"517-233-4378", "pin":"JbwmZ", "position":"General Member", "state":"MI", "user_id":19, "username":"bwm19", "zip":"45789-2334" }` [download] At least under the assumption that "sorted" means "sorted as sort would sort", I don't see any contradition.	[reply] [d/l]
Re^14: CGI Action call by tultalk (Monk) on Mar 15, 2018 at 08:41 UTC
Re^15: CGI Action call by Corion (Patriarch) on Mar 15, 2018 at 08:48 UTC
Re^13: CGI Action call by poj (Abbot) on Mar 14, 2018 at 20:55 UTC
I think you are confusing the OO and Functional interfaces. Use either OO `my $json = JSON->new; $json->canonical(1); my $json_str = $json->encode($hashref); print $json_str;` [download] or Functional `my $json_str = to_json($hashref, {canonical => 1});` [download] poj	[reply] [d/l] [select]
Re^11: CGI Action call by Corion (Patriarch) on Mar 14, 2018 at 14:23 UTC
my $hash = ( 'id' =>$ref->{"id"}, 'user_id' =>$ref->{"user_id"}, 'username' =>$ref->{"username"}, 'password' =>$ref->{"password"}, 'pin' =>$ref->{"pin"}, 'position' =>$ref->{"position"}, 'forename' =>$ref->{"forename"}, 'lastname' =>$ref->{"lastname"}, 'business' =>$ref->{"business"}, 'address1' =>$ref->{"address1"}, 'address2' =>$ref->{"address2"}, 'city' =>$ref->{"city"}, 'state' =>$ref->{"state"}, 'zip' =>$ref->{"zip"}, 'phone_home'=>$ref->{"phone_home"}, 'phone_cell'=>$ref->{"phone_cell"}, 'email' =>$ref->{"email"}, 'comments' =>$ref->{"comments"}, 'MJ' =>$ref->{"MJ"}, 'MD' =>$ref->{"MD"}, 'DD' =>$ref->{"DD"}, 'DP' =>$ref->{"DP"}, ); [download] That's not how you initialize a hash reference. Please consider reading tye's References Quick Reference and perlreftut to learn about how to construct references. Also, the output of `Dumper( $hash )` should have shown you that what you expect is not what Perl puts into `$hash` without the surroundings of your CGI script. Consider reducing code you don't understand to a bare minimum instead of testing things inside a CGI program.	[reply] [d/l] [select]