Undefined Value Error Message

[Milti]

I have the following code in a PERL cgi which calls a MySQL database. When calling the cgi I get a "Can't call method 'Prepare' on an undefined value". Sometimes one can't see the forest for the trees and vice versa. Can someone show me the error?

my $reqpage= param('reqpage');

unless ($reqpage > 1) {$reqpage = "1";}
 
my  $pagesize = "5"; 
my $firstresult = (($reqpage - 1) * $pagesize) + 1;
my $lastresult = $firstresult + $pagesize - 1;
if ($lastresult > $result_count) {
$lastresult = $result_count;
}

my $offset = (($reqpage-1) * $pagesize); 


my $limit = "5";
my $result;
my @result;


my $LastName=param('LastName');

        my $n="3";
        my $LastName= substr($LastName,0,$n);
        my $query="$LastName%";

my $sql = "Select AccountID,LastName,FirstName,Location,Specialty,Intr
+oduction,View from members WHERE (Location = 'Phoenix, AZ USA' and La
+stName LIKE ?) Limit $offset,$limit";
my $sth=$dbh->prepare("$sql"); 
$sth->execute($query)  or die "Connection Error: $DBI::errstr\n";

I will really appreciate any assistance provided!!

[davido]

It means that $dbh isn't defined, which probably means that either you didn't execute the code required to connect to the database, or that the code to connect to the database failed. The snippet of code you provided to us doesn't show the line where you do connect, so we can't be sure exactly what is wrong. But that's where you should look first.

[Milti]

Dave, you were right but there is another problem. Here's the entire code.

#/usr/bin/perl -w
    use strict; 
    use DBI; 

    use CGI qw(:standard); 
    use POSIX qw(ceil floor);           


my $LastName=param('LastName');
my $reqpage= param('reqpage');

unless ($reqpage > 1) {$reqpage = "1";

&count;
}
if ($reqpage > 1) {
&search;
}

sub count {

     my $dbh = DBI->connect('dbi:mysql:membersdb','member','memberpass
+wd')
 or die "Connection Error: $DBI::errstr\n";


    # Count how many rows are there in a table, so that we can use it 
+for $pagenum.  
        

my $LastName=param('LastName');

        my $n="3";
        my $LastName= substr($LastName,0,$n);
        my $query="$LastName\%";
    my $sql = "select count(*) from members WHERE (Location = 'Phoenix
+, AZ USA' and LastName LIKE ?)"; 

    my $sth = $dbh->prepare ("$sql"); 

    $sth->execute($query) || quit();   

    my $result_count = $sth->fetchrow_array;    
$sth->finish;
        my  $pagesize = "5"; 
    if ($result_count != 0) {
my $pagecount = int($result_count / $pagesize);
if (($pagecount * $pagesize) != $result_count) {
$pagecount++;
}

$sth->finish;
$dbh->disconnect ();
&search;

sub search {
     my $dbh = DBI->connect('dbi:mysql:membersdb','member','memberpass
+wd')
 or die "Connection Error: $DBI::errstr\n";
my $reqpage= param('reqpage');

unless ($reqpage > 1) {$reqpage = "1";}
 
my  $pagesize = "5"; 
my $firstresult = (($reqpage - 1) * $pagesize) + 1;
my $lastresult = $firstresult + $pagesize - 1;
if ($lastresult > $result_count) {
$lastresult = $result_count;
}

my $offset = (($reqpage-1) * $pagesize); 


my $limit = "5";
my $result;
my @result;


my $LastName=param('LastName');

        my $n="3";
        my $LastName= substr($LastName,0,$n);
        my $query="$LastName%";
        
my $sql = "Select AccountID,LastName,FirstName,Location,Specialty,Intr
+oduction,View from members WHERE (Location = 'Phoenix, AZ USA' and La
+stName LIKE ?) Limit $offset,$limit";
my $sth=$dbh->prepare("$sql"); 
$sth->execute($query)  or die "Connection Error: $DBI::errstr\n";


print header(), start_html;
my $page = "E:/companyname/website/htdocs/resources/header_1.htm";
open (PAGE, "$page") || die "Couldn't open $page";
while (<PAGE>) {print;}



print "<br><p align=\"center\"><font size=\"5\"><b>&nbsp;&nbsp;<u>Your
+ Search Results</u></b></font><br><br>";


while (my @results = $sth->fetchrow_array)

{
my $AccountID = $results [0];
my $LastName = $results [1];
my $FirstName = $results [2];
my $Location = $results [3];
my $Specialty = $results [4];
my $Introduction = $results [5];
my $View = $results [6];
my $Image = $results [0];

print <<"(END TABLE HTML)";
<center>
<Table width="700" style="border-collapse: collapse;border: 1px solid 
+#000000" bordercolor="#111111">
<tr><td width="90" valign="top"><img src="http://www.website.com/resou
+rces/$Image" height="100" width=100" alt = "No Image Available"></td>
+<td align="left"><p align=\"left\"><font size=\"4\"><ul><li><b>Name&n
+bsp;----&nbsp;$FirstName&nbsp;$LastName</b><br>Location&nbsp;----&nbs
+p;$Location<br>
Specialty&nbsp;----&nbsp;$Specialty<br><br>$Introduction<br><br>$View<
+/li></ul></font></p>
</td></tr></table>
(END TABLE HTML)
##++$count;
}
my $prev_page;
my $next_page;
my $prev_link;
my $next_link;
my $pagelinks;
my $pageno;
my $thislink;
my $pagesize = "5";


$prev_page = $reqpage - 1;
$next_page = $reqpage + 1;
if ($reqpage == 1) {
$prev_link = "";

} else {
$prev_link = "<a href=\search_test14.pl?reqpage=$prev_page>Previous</a
+>&nbsp;";

} 
if ($reqpage == $pagecount) {
$next_link = "";

} else {
$next_link = "<a href=\search_test14.pl?reqpage=$next_page>NEXT</a>";

} 
if ($pagecount > 1) {
$pagelinks = $prev_link;
$pageno = 0;
while ($pageno < $pagecount) {
$pageno++;
if ($pageno == $reqpage) {
$thislink = " <b>$pageno</b> ";

} else {
$thislink = "<a href=\search_test14.pl?reqpage=$pageno>$pageno</a>&nbs
+p;"

}
$pagelinks = $pagelinks . $thislink;
}
$pagelinks = $pagelinks . "   " . $next_link;

print "<br><p align=\"center\"><font size=\"3\">$pagelinks</font></p>"
+;

} else {
$pagelinks = "";
}
}

print <<"(END FOOTER HTML)";

 <head>
  <title>FOOTER</title>
 </head>
 
  
   <body topmargin="0" leftmargin="0" rightmargin="0">

   
<table width="800">
<tr>
<td width="800"><p align="left"><a href="http://www.diversitylink.com"
+>Home</a></p>
</td>
    </tr>
    <tr>
      <td width="100%">
      <img src="http://www.website.com/images/blk.gif" height="1" widt
+h="100%"></td>
    </tr>
 
    </table> 

<table width="800" cellpadding="0"><tr><td width="100%"><p align="cent
+er"><font size="1" face="Arial">
            
            © My Company<br>
            All Rights Reserved<br></font></td>
       </tr></table>
(END FOOTER HTML)


end_html ();

$sth->finish;
$dbh->disconnect ();
}
}
exit;

I am trying to get results from a table in the MySQL database and show 5 results per page. The initial search does just that having determined the correct number of pages required, and shows links 1-2-3-etc-Next on the bottom of the page. However when page 2, 3, or Next are called the $Name value is ignored and everything in the column 'Location' is counted again and everything is presented with the correct number(5 rows) per page. Why is the name value being ignored?? Help Please!!!

[graff]

Your code looks like spaghetti. I was actually surprised that it compiles, but I'm not surprised to see these warnings:

"my" variable $LastName masks earlier declaration in same scope at 107
+8385 line 32.
Variable "$result_count" will not stay shared at 1078385 line 63.
"my" variable $LastName masks earlier declaration in same scope at 107
+8385 line 78.
"my" variable $pagesize masks earlier declaration in same scope at 107
+8385 line 124.
Unrecognized escape \s passed through at 1078385 line 133.
Variable "$pagecount" will not stay shared at 1078385 line 136.
Unrecognized escape \s passed through at 1078385 line 140.
Unrecognized escape \s passed through at 1078385 line 152.
1078385 syntax OK

The cases of '"my" variable … masks earlier declaration' will probably mess things up.

The "unrecognized escapes" are cases where you seem to have an unnecessary backslash (or maybe you intended to put \" but didn't?)

The variables that "will not stay shared" happen because you're defining the "search" sub inside the scope of defining the "count" sub, the variables in question are declared (lexically scoped) inside "count", before sub search begins, and then they're being used inside "search".

That's a strange (and wrong) way to lay out subroutines. For example, consider what happens at start-up if the "reqpage" parameter you get is greater than 1: the "search" sub will be called without the "count" sub being called first, but the "$result_count" variable is only assigned a value (from a query) in the "search" "count" sub, which hasn't been called in this case. That would probably explain the problem you're describing.

There's also a missing double-quote at line 111 (the over-long line in the "heredoc"; this doesn't affect compiling, but it'll screw up your page display. (I noticed that because I used emacs with colorized text, and after that line, the coloring was inverted for code vs. string values).

So let me suggest:

• FIX YOUR INDENTATION so that it is consistent with the actual structure of the code.
• Move all of sub search { … } so it's completely outside of sub count { … }
• Figure out how the "unshared" variables in "search" (which are now undeclared) are going to be assigned the values that they're supposed to have when the script runs with reqpage > 1
• Maybe google around for "paging database query results in perl cgi" - I think it should be pretty easy to find some useful examples to work from.
When you see how other people handle paging, you'll probably want to start over on this script.

(Updated to get the sub names right in the middle paragraph, and a grammar blot in the suggestion list.)

[Don Coyote]

The numerous declarations of the $LastName variable have already been mentioned to you. After resolving the issue that you are overwriting that variable in your while $sth->fetch_array() routine, I look to where the $LastName variable is reposted to the page within a form so that the next call to your cgi script will have a param with the name LastName but I cannot see one.

The problem is that you do not repost a form from which your cgi script can collect the LastName param. If the search form is written from

print header(), start_html;
my $page = "E:/companyname/website/htdocs/resources/header_1.htm";
open (PAGE, "$page") || die "Couldn't open $page";
while (<PAGE>) {print;}

then the search will submit an empty paramater. Outside of this there is nowhere that the page you write has a form which includes a $LastName param

I would not suggest adding the param to the links as the search should be properly submitted via POST through a form.

You should also validate your incoming paramaters using the untainting idiom

my $LastName=param('LastName');
# pattern should match sql field requirement 
if($LastName =~ /^([\w\s]+)$/){
  $LastName = $1;
}else{
 print invalid search request .html
}

my $reqpage= param('reqpage');

if($reqpage =~ /^(\d+)$/){
  $reqpage = $1;
}else{
 print invalid page request .html
}

relying on sticky param here will not (afaik) work as you are not creating a cgi object, only using the class methods. Also with concurrent search requests a sticky param may get confused.

Supply a form where the $LastName paramater is given, for your script to be delivered the param it is expecting.

[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