#/usr/bin/perl -w
use strict; 
use DBI; 
use CGI ':standard'; 
#use CGI::Carp 'fatalsToBrowser';

# constants
my $locn     = 'Phoenix, AZ USA';
my $pagesize = 5;

# variables
my $LastName = param('LastName');
my $reqpage  = param('reqpage') || 1;
my $name_like = substr($LastName,0,3).'%';

# connect to database
my $dbh = get_dbh();

# get record count 
# calc page count and offset
my $rec_count = rec_count();
my $page_count = int($rec_count / $pagesize);
++$page_count if ($rec_count % $pagesize);
my $offset = ($reqpage-1) * $pagesize;

# start page
print header,start_html(
   -title=>'PAGE TITLE',
   -style=>{ -code=>'margin-top: 0px; margin-left: 0px; margin-right: 0px;'} );

# simple form for testing
print qq!<form action="" method="post">
  <input type="text" name="LastName" value="$LastName"/>
  <input type="submit" value="LastName"/>
</form>!;   

# search results
results();

# page links
print page_links() if $page_count > 1;

debug();
print end_html();

# return count
sub rec_count {
  my $sql = "SELECT count(*) from members 
             WHERE (Location = ? and LastName LIKE ?)"; 
  my $rec_count = $dbh->selectrow_array($sql,undef,$locn,$name_like);
  return $rec_count;
}

# search result
sub results {

  my $sql = qq! SELECT ID,LastName,Location FROM members
                WHERE (Location = ? and LastName LIKE ?) LIMIT ?,?!;

  my $sth=$dbh->prepare($sql); 
  $sth->execute($locn,$name_like,$offset,$pagesize);

  my $table = qq!<table width="100%" cellpadding="3" cellspacing="0" border="1">
  <tr bgcolor="#e0e0e0">
   <td>ID</td>
   <td>LastName</td>
   <td>Location</td>
  </tr>!;
  
  while (my @f = $sth->fetchrow_array){
    $table .= qq!<tr>
	<td>$f[0]</td>
	<td>$f[1]</td>
	<td>$f[2]</td>
  </tr>!;
  }

  $table .= q!</table><br/>!; 
  print $table;
}

# page links
sub page_links {

  my $links;
  if ($reqpage > 1){
    my $prev = $reqpage - 1;
    $links = qq!<a href="?LastName=$LastName;reqpage=$prev">PREV</a> &nbsp;!;
  }
  
  for my $i (1..$page_count){
    if ($i == $reqpage){
	  $links .= qq!<b>$i</b> &nbsp;!;
	} else {
	  $links .= qq!<a href="?LastName=$LastName;reqpage=$i">$i</a> &nbsp;!;
	}
  }
  
  if ($reqpage < $page_count){
    my $next = $reqpage + 1;
    $links .= qq!<a href="?LastName=$LastName;reqpage=$next">NEXT</a> &nbsp;!;
  }
  
  return $links;
}


# connect
sub get_dbh{
  my $database = ''; my $user = ''; my $pw = '';
  my $dsn = "dbi:mysql:$database:localhost:3306"; 
  my $dbh = DBI->connect($dsn, $user, $pw, { RaiseError=>1, AutoCommit=>1 } );
  return $dbh;
}

# debug
sub debug {
 print qq!<hr/><pre>
 LastName   = |$LastName|
 reqpage    = $reqpage

 rec_count  = $rec_count
 pagesize  = $pagesize
 page_count = $page_count
 offset     = $offset
</pre>!
}