Re: Undefined Value Error Message

[Anonymous Monk]

Code-review tip: I do not see a definition for $limit, either. And if, as I believe to be the case, the offset and limit can be supplied as parameters ("?"), they should be. (Basically, never interpolate any values into an SQL string.) This code seems to have several logic errors in it. Carefully desk-check what you have just posted and you will see what I mean.

[Milti]

Thanks! I think I had $limit defined but I have used placeholders for the limit clause in the SELECT statement. However I have the same problem. When pages are called from the pagelinks the $LastName variable is ignored. A new search is conducted but the entire location field is searched with no regard for the WHERE LastName Like ? clause. I don't understand what is happening. It's as if the variable $LastName=param('LastName'); isn't being retained after the initial search. Can someone provide a clue as to what is going on?

[davido]

Open an output file to the STDERR filehandle, and pepper your code with print STDERR "\$thisvar=($thisvar)\n"; statements to test whatever assumptions you're making about the programs state at various stages throughout the execution. ...of course I don't mean literally "$thisvar", use variable names that you think you know what they contain. Verify that they do. Also use the same technique to verify that you've arrived at logic branches you expected to arrive at, or that you've looped where you expected to loop.

Then review the log file. If your testing of assertions and expectations is thorough enough, you'll find exactly where the problem is. If I were to debug your code, and if the issue weren't immediately apparent, I would do the same thing. If you run into trouble during this process, let us know where you're hung up.

[poj]

You need to persist the $LastName variable between page refreshes, this example uses the query string

#/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" b
+order="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> &nb
+sp;!;
    }
  }
  
  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>!
}

poj

[Milti]

Thank you "poj" and others who offered help. I now understand what I was doing wrong and --- everything works!. Thanks again "poj" for your very clear explanation. Milti