I have a form with a navigator control I made. Works fine except when I want to scroll with a different order in place.

The 4 SQL statements work fine in PHPAdmin. I issue an "alter table users order by lastname asc and the table is so ordered. Shows as such in browse. I execute the various SQL statements below and the returned records are as expected. next, next, previous, beginning and end. The ORDER remains in place.

On the form I search for last name and set the alter table based on the search selected. This all works as intended per my logging. When I try to scroll the records with the navigator it is returning records based on the user_id order. I tried correcting this by reasserting the alter table again with each scroll but to no avail.

Don't understand why the process works in PHP MyAdmin but not with my code.

#====================================
#Navigation navigate_Records Buttons 1-4
# 1 Beginning
# 2 Previous 
# 3 Next
# 4 End    

sub navigate_Records {

    my $kind = $query->param('kind');
    my $rowpointer = $query->param('searchterm'); 
    my ($sth, $stmt);
    my @row;
    warn("Entered update_tables.cgi Open Database return First Record 
+with action: = '$action'");   
    warn("Search Parameter = '$kind'");
    warn("rowpointer = '$rowpointer'");    

    my $minrowpointer;
    my $maxrowpointer;
    my $searchfield;

    warn("sortindex =  '$sortindex'"); #Set by selection in search_Rec
+ords
    if (!$sortindex) {
      $sortindex = 0;
    }
    if ($sortindex == 0){
        $searchfield = 'user_id';
        $minrowpointer = 1;
        $maxrowpointer = 14999;
       } 
    elsif ($sortindex == 1) {
        $searchfield = 'lastname';    
        $minrowpointer = 'a%';
        $maxrowpointer = 'z%';
       }
    elsif ($sortindex == 2) {
        $searchfield = 'business';    
        $minrowpointer = 'a%';
        $maxrowpointer = 'z%';
       }        
    
    

    #kind 0 & 3 ok with direct hard coded SQL 
    
    if ($kind == 0){  
        #beginning record Tested Good

        $stmt= "SELECT * FROM ( SELECT * FROM users WHERE $searchfield
+ <= ? AND $searchfield >= ? ORDER BY $searchfield ASC LIMIT 1 ) sub O
+RDER BY $searchfield ASC"; 

        # example user_id 
#        $stmt = "SELECT * FROM ( SELECT * FROM users WHERE user_id <=
+ 14999 AND user_id >= 1 ORDER BY user_id ASC LIMIT 1 ) sub ORDER BY u
+ser_id ASC"; #Works fine hard coded
        
        #example lastname SELECT * FROM ( SELECT * FROM users WHERE la
+stname <='z%' AND lastname >= 'a%'ORDER BY lastname ASC LIMIT 1 ) sub
+ ORDER BY lastname ASC #Works fine hard coded

        #example business SELECT * FROM ( SELECT * FROM users WHERE bu
+siness <='z%' AND business >= 'a%'ORDER BY business ASC LIMIT 1 ) sub
+ ORDER BY business ASC 
    }
    
    if ($kind == 1){
        #previous record Tested Good
        $stmt = "SELECT * FROM users WHERE $searchfield <= (SELECT MAX
+($searchfield) FROM users WHERE $searchfield < ?) ORDER BY $searchfie
+ld DESC LIMIT 1";
    }                                            
  
    if ($kind == 2){
        #next record Tested Good
        $stmt = "SELECT * FROM users WHERE $searchfield >= (SELECT MIN
+($searchfield) FROM users WHERE $searchfield > ?) ORDER BY $searchfie
+ld ASC LIMIT 1";
   
    } 

    if ($kind == 3){
        #Last record Tested Good
        
        $stmt = "SELECT * FROM ( SELECT * FROM users WHERE $searchfiel
+d <= ? AND $searchfield >= ? ORDER BY $searchfield DESC LIMIT 1 ) sub
+ ORDER BY $searchfield ASC"; 
        
        #example user_id 
#        $stmt = "SELECT * FROM ( SELECT * FROM users WHERE user_id <=
+14999 AND user_id >= 1 ORDER BY user_id DESC LIMIT 1 ) sub ORDER BY u
+ser_id ASC"; #Works fine hard coded
                
        #example lastname SELECT * FROM ( SELECT * FROM users WHERE la
+stname >='z%' AND lastname >= 'a%'ORDER BY user_id DESC LIMIT 1 ) sub
+ ORDER BY lastname ASC #Works fine hard coded

        #example business SELECT * FROM ( SELECT * FROM users WHERE bu
+siness >='z%' AND business >= 'a%'ORDER BY business DESC LIMIT 1 ) su
+b ORDER BY business ASC 
    }

    warn("statement =  '$stmt'");
    
    $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stmt .
+ "\nDBI returned: \n", $dbh->errstr;

#    warn("'$maxrowpointer' '$minrowpointer' '$searchfield' '$rowpoint
+er'");

       if (($kind == 0) || ($kind == 3)){
       warn("kind = '$kind' maxrowpointer = '$maxrowpointer'  minrowpo
+inter = '$minrowpointer'"); 
       $sth->execute ($maxrowpointer,$minrowpointer) or die "Unable to
+ execute query: " . $sth->errstr;
     } 
    elsif (($kind == 1) || ($kind == 2)){
       warn("kind = '$kind' rowpointer = '$rowpointer'"); 
       $sth->execute($rowpointer) or die "Unable to execute query: " .
+ $sth->errstr;    
    }
     
    my $navResult = $sth->fetchrow_hashref();
    
    warn("navResult = '$navResult'");
    
    my $count = $sth->rows;

    warn("count = '$count'");
if ($count == 0) {
      my %navFail = ( Count => $count);
      my $json = JSON->new; 
      $json->canonical(1);
      $json = encode_json(\%navFail);
        print "Content-Type: application/json\n\n";
      print $json;
      warn("Finished print 0 count $json");    
      exit(1);     }
    else {
      warn("count = '$count'");
      my $json = JSON->new; 
      $json->canonical(1);
      $json = encode_json($navResult);
      print "Content-Type: application/json\n\n";      
      print $json;
      warn("Finished print $json");    
      exit(0);
    }

}

[download]

More code

#Search Records & Return Found 3 buttons
# 0 Member ID
# 1 Last Name 
# 2 Business Name 
#elsif ($action eq "search_Records")

 sub search_Records {
    warn("Entered search_Records");
    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';    
    }

    $sortindex = $kind;
    
    restoreOrder($kind);

    #Sort Index For The Three Kinds Of ORDER Supposed to be global.
    
    warn("sortindex =  '$sortindex'");
    
    warn("searchfield =  '$searchfield'");
    
    $stmt = "SELECT * FROM users WHERE $searchfield = ? ORDER BY ? ASC
+";
#    my $stmt = "SELECT * FROM users WHERE user_id = 15 ORDER BY user_
+id ASC";
    
    warn("statement =  '$stmt'");    
    
    $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stmt .
+ "\nDBI returned: \n", $dbh->errstr;
    
    $sth->execute($searchterm, $searchfield) or die "Unable to execute
+ query: " . $sth->errstr;    
#    $sth->execute() or die "Unable to execute query: " . $sth->errstr
+;    

    my $searchResult = $sth->fetchrow_hashref();

    my $count = $sth->rows;

    warn("count = '$count'");
    if ($count == 0) {
      warn("Failed Search: '$searchfield' equal to '$searchterm' "); 
      my %searchFail = ( SearchError => $kind);
      my $json = JSON->new; 
      $json->canonical(1);
      $json = encode_json(\%searchFail);
        print "Content-Type: application/json\n\n";
      print $json;
      warn("Finished print 0 count $json");    
      exit(1);
     }  #{"SearchError":0} Good
    else {
      warn("count = '$count'");
      my $json = JSON->new; 
      $json->canonical(1);
      $json = encode_json($searchResult);
      print "Content-Type: application/json\n\n";
      print $json;
      warn("Finished print $json");    
      exit(0);
    }
}

[download]

And yet more code


#==================================
#Set up "order by clause" depending on searchfield
sub restoreOrder{ 
    my $searchIndex = shift;  
   
    if ($searchIndex == 0)  {
       $searchfield = "user_id"; 

     }
    elsif ($searchIndex == 1)  {
       $searchfield = "lastname";

     }
    elsif ($searchIndex == 1)  {
       $searchfield =  "business";
    }

   warn("in restoreOrder index = '$searchIndex'");
    
    my $stmt = "alter table users order by $searchfield asc";
    
    my $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stm
+t . "\nDBI returned: \n", $dbh->errstr;    
    
    DBI->trace(3,$dbitracelog); 
    
    $sth->execute() or die "Unable to execute query: " . $sth->errstr;
+    

}

[download]

In reply to Re^6: CGI Action call by tultalk
in thread CGI Action call by tultalk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.