Re^21: CGI Action call

Example demo cgi of how you could combine the 2 subs into 1 and use OFFSET

#!/usr/bin/perl
# search.cgi
use strict;
use DBI;
use CGI;
use CGI::Carp 'fatalsToBrowser';
use JSON;

my $dbh    = get_dbh();
my $query  = CGI->new(); 

# searchtype ; 0-search 1-first 2-prev 3-next 4-last
# fieldno   ; 0-user_id 1-lastname 2-business 
my $criteria = {
  searchterm => $query->param('searchterm') || '',
  fieldno    => $query->param('fieldno') || 0,
  searchtype => $query->param('searchtype') || 0,
  offset     => $query->param('offset') || 0 
};

# query database
my ($result,$err,$sql) = search_Records($dbh,$criteria);

# output
my $json = JSON->new->pretty();
my $json_str = $json->encode({ 
    result   => $result, 
    criteria => $criteria,
    sql      => $sql });

#print "Access-Control-Allow-Origin: *\n";    
print "Content-Type: application/json\n\n";
print $json_str;
exit($err);

sub search_Records {

  my ($dbh,$criteria) = @_;
  my $offset = $criteria->{'offset'};
  my $term   = $criteria->{'searchterm'};
  my $type   = $criteria->{'searchtype'};
  $type = 0 if $type > 4;

  # searchno 0-user_id 2-lastname 3-business
  my @fields  = ('user_id','lastname','business');
  my $fieldno = $criteria->{'fieldno'};
  $fieldno = 0 if $fieldno > 2;
  
  my $field = $fields[$fieldno];
  my $sort = $field; # sort by search field
  
  my ($record_count) = $dbh->selectrow_array('SELECT COUNT(*) FROM use
+rs');

  # search to find offset
  if ($type == 0){
   ($offset) = $dbh->selectrow_array("
     SELECT COUNT(*) 
     FROM users 
     WHERE $field < ?",undef,$term);
  } elsif ($type == 1){
    $offset = 0; # First record  
  } elsif ($type == 2){
    --$offset;  # Previous record  
  } elsif ($type == 3){
    ++$offset; # Next record
  } elsif ($type == 4){
    $offset = $record_count-1; # Last record  
  }

  # apply limits
  $offset = 0 if $offset < 0;      
  $offset = $record_count-1 if $offset >= $record_count;

  # prep and execute SQL
  my $f = '*';
  $f = join ',',qw(user_id business lastname); # limit for testing
  
  my $stmt = "
     SELECT $f FROM users 
     ORDER BY $sort,user_id 
     LIMIT $offset,1";          

  warn("$stmt");
  my $sth = $dbh->prepare($stmt);
  $sth->execute();

  my $navResult = $sth->fetchrow_hashref();
  my $count = $sth->rows; 
  warn("count = '$count'");
  
  # return results
  my $err = 0; 
  if ($count == 0) {
    $navResult = { 'Count' => $count};
    $err = 1; 
  } else {
    $navResult->{'offset'}  = $offset; 
  }
  return ($navResult,$err,$stmt);
}

sub get_dbh{
  my $database = "test"; my $user = "user"; my $pw = "password";
  my $dsn = "dbi:mysql:$database:localhost:3306"; 
  my $dbh = DBI->connect($dsn, $user, $pw, { 
  PrintError=>1, RaiseError=>1 } );
  return $dbh;
}
[download]

HTML form to test it.

<html>
<head>
  <script src="jquery-3.1.1.min.js"></script>
  <script type="text/javascript">

  function test(searchtype){
    $("#criteria").empty() ;
    $("#result").empty();
  
    var url   = "http://localhost/test/search.cgi";
    var fieldno = $("input[type=radio][name=fieldno]:checked" ).val();
    
    var param  = {
      "searchterm": $("#searchterm").val(),
      "searchtype": searchtype,
      "fieldno" : fieldno,
      "offset" : $("#offset").val()
    };
      
    $.getJSON(url, param, function(data){
       $.each(data.criteria, function(key, value){
         $("#criteria").append(key+" = "+value+" | "); 
       });
       $.each(data.result, function(key, value){
         $("#result").append(key+" = "+value+"<br/>"); 
       }); 
       $("#offset").val( data.result.offset );
       $("#sql").val( data.sql );
    })  
 }    
</script>   
</head>
<body>
<table width="50%">
<tr align="left">
<th>Search Field</th>
<th>Search Term</th>
</tr>
<tr><td>
User Id <input type="radio" name="fieldno" value="0" checked/><br/>
Lastname<input type="radio" name="fieldno" value="1"/><br/>
Business<input type="radio" name="fieldno" value="2"/><br/>
</td><td>
<input type="text" id="searchterm" name="searchterm"/>
<button onClick="test(0)">Search</button>
</td></tr></table>
<hr/><b>Response: </b><div id="result"></div><br/>
<button onClick="test(1)">First</button>
<button onClick="test(2)">Prev</button>
<button onClick="test(3)">Next</button>
<button onClick="test(4)">Last</button>
<hr/>
<h3>Offset :
<input type="text" id="offset" name="offset"/>
</h3>
<h3>SQL</h3>
<textarea id="sql" name="sql" cols="120" rows="5">SQL</textarea>
<hr/><b>Request:</b><div id="criteria"></div>
</body>
</html>
[download]

poj

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

Replies are listed 'Best First'.
Re^22: CGI Action call by tultalk (Monk) on Apr 01, 2018 at 23:37 UTC
Got it working with the handler. Works perfectly so far. Went to town, inside outside upside down!! (Kids verse). Very pleased. Used unique (searchterm & record id) as suggested in MySQL forum. Table stays open and accessible throughout repeated calls which I had questioned. Happy camper. Thanks	[reply]