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

Yes, as is documented in DBI.

Also, your SQL seems faulty to me as the "previous record" seems to skip users:

create table users ( user_id integer not null primary key, name varcha
+r(16) );

insert into users values (1,'Fred');
insert into users values (2,'Barney');
insert into users values (3,'Wilma');
insert into users values (4,'Betty');
insert into users values (5,'Pebbles');


SELECT *
FROM users
WHERE user_id < (SELECT MAX(user_id)
                 FROM users WHERE user_id < 4) 
ORDER BY user_id DESC LIMIT 1

[download]

(SQL fiddle)

This suggests that the "previous" entry for "Betty" (user id 4) is "Barney" (user id 2), and not user id 3 as I would expect.

The same, but inverse holds for the other SQL.

Replies are listed 'Best First'.
Re^6: CGI Action call
by tultalk (Monk) on Mar 27, 2018 at 17:00 UTC

    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]
[reply]
[d/l]
[select]

      Why use sub selects here

      SELECT * FROM users WHERE $searchfield <= (
  SELECT MAX($searchfield) FROM users WHERE $searchfield < ?) 
ORDER BY $searchfield DESC 
LIMIT 1

      [download]

      instead of the simpler

      SELECT * FROM users 
WHERE $searchfield < ? 
ORDER BY $searchfield DESC 
LIMIT 1

      [download]
      poj
[reply]
[d/l]
[select]
      alter table users order by lastname asc

      [download]

      I don't think that this is how SQL works. In fact, I'm surprised that such a statement parses at all and doesn't raise an error.

      In SQL, tables don't have any kind of order that you can change. SELECT statements have an order clause and if you want to have a specific order, you need to specify that in every SELECT statement.

[reply]
[d/l]

        The alter table MyTable order by MyField works in PHP Admin and is in the MySQL docs as well as testing and explanation in Work Log Wl#3681 Report BUG#24562

        MySQL Docs: ALTER TABLE tbl_name [alter_specification ORDER BY col_name , col_name ...

        My question remains: Why it works in PHP MyAdmin and not in my program?????

        Best regards

[reply]
Re^6: CGI Action call
by tultalk (Monk) on Mar 12, 2018 at 22:08 UTC

    That may be. I threw it together and locked it to return related to fixed row number for testing other parts.

    Another to-do

[reply]
Re^6: CGI Action call
by tultalk (Monk) on Mar 15, 2018 at 12:05 UTC

    Need some insight. Have been staring at this since last night. Placeholder issue

    if ($kind == 3){
        #Last record

        $stmt = "SELECT * FROM ( SELECT * FROM users WHERE ? <= ? AND 
+? >= ? ORDER BY ? ASC LIMIT 1 ) sub ORDER BY ? 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

    [download]

    placeholder substitution

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

    warn("'$searchfield' '$maxrowpointer' '$searchfield' '$minrowpoint
+er' '$searchfield'  '$searchfield'"); #verified correct values in var
+iables at this point
       
    $sth->execute ($searchfield, $maxrowpointer,$searchfield,$minrowpo
+inter, $searchfield, $searchfield) or die "Unable to execute query: "
+ . $sth->errstr;

    my $refresult = $sth->fetchrow_hashref();
    
    my $count = $sth->rows;

    [download]

    This works fine with hard coded vaues instead of placeholders and also testing MySQL directly. count = 1 When running with the placeholders, count = 0

    Result with hard code JSON output below

    {"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":"Z
+inzer","password":"1234","phone_cell":"517-240-1004","phone_home":"51
+7-233-

4378","pin":"JbwmZ","position":"General Member","state":"MI","user_id"
+:19,"username":"bwm19","zip":"45789-2334"}

    [download]
[reply]
[d/l]
[select]

      What is the value of $searchfield? You don't show it to us so we have to guess. This makes it much harder to provide you concise help. Please adjust your debugging process and tell us what you see/output instead of making us guess.

      My guess is that $searchfield contains the string id or some other string that you want to be interpreted as column name.

      Did you read the section about placeholders in DBI?

      Most likely, you skipped over the part where it tells you where placeholders can be used:

      Placeholders, also called parameter markers, are used to indicate values in a database statement that will be supplied later, ...
      With most drivers, placeholders can't be used for any element of a statement that would prevent the database server from validating the statement and creating a query execution plan for it.

      Naturally, you can't supply the name of a column as a placeholder because the database can't validate your statement before seeing the column name.

      What happens instead is that your database treats all placeholders as values and never compares the columns but just values of the placeholders.

[reply]
[d/l]
[select]

        My guess is that $searchfield contains the string id or some other string that you want to be interpreted as column name.

        You are correct, however:

        Regarding placeholdrs: You stated and the perl docs state that placeholders cannot be used for elements of the SQL statement such as field names.

        The code below demonstates use of a placeholder for a field name (lastname) and this works.

        I am asking because I have a much more complicated statement with 6 placeholders (4 field names and 2 values) which fails.It works fine with hard coded field names. I am trying to use one statement for 3 different queries each using different sets of fields/values

        I am trying to understand why the one below works

        sub 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';    
    }
    
    #Sort Index For The Three Kinds Of ORDER Supposed to be global.
    
    $sortindex = $kind;
    warn("sortindex =  '$sortindex'");
    
    warn("searchfield =  '$searchfield'");
    
    my $stmt = "SELECT * FROM users WHERE $searchfield = ? ORDER BY ? 
+ASC";
    
    warn("statement =  '$stmt'");    
    
    my $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stm
+t . "\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]

        warn log

        
[Mon Mar 19 19:01:04 2018] update_tables.cgi: statement =  'SELECT * F
+ROM users WHERE lastname = ? ORDER BY ? ASC' at update_tables.cgi lin
+e 462.

[Mon Mar 19 19:01:04 2018] update_tables.cgi: count = '1' at update_ta
+bles.cgi line 486.

[Mon Mar 19 19:01:04 2018] update_tables.cgi: Finished print 

{"DD":"2018-01-30","DP":"2018-12-31","MD":"120.00","MJ":"2017-01-30","
+address1":"1345 Griffin Ave","address2":"$47","business":"ZI Inc","ci
+ty":"Deluth","comments":"Another test at the Z enc","email":"tz@voyag
+er.net","forename":"Adam","id":58,"lastname":"Zorky","password":"xxxx
+xx","phone_cell":"(517) 240-1004","phone_home":"(345) 247-1655","pin"
+:null,"position":"General Member","state":"MN","user_id":2,"username"
+:"bwm2","zip":"38456"} at update_tables.cgi line 492.

        [download]
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom