#!/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 users'); # 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; } ##

##



  
     




Search Field
Search Term


User Id 

Lastname

Business





Response: 







Offset :


SQL
SQL
Request: