1. Home
  2. Forums Index
  3. Server Side / Perl Server Side CGI Scripting 11:54 pm May 20, 2026

Forum Moderators: coopster & phranque

Message Too Old, No Replies

Perl MySql Problem

Data is not showing.

         

typomaniac

3:28 am on Aug 4, 2010 (gmt 0)

10+ Year Member Top Contributors Of The Month



Hi, I'm having a problem getting data to appear on a readout page. I have enclosed a copy of the script. I used this script before and it worked fine but now it only seems to want to present the pagination results, that is it shows the number of pages there should be (as to how many files should appear on a page) but I can't get the data itself to show. I know its opening the database because it is getting the number of records.

#!/usr/bin/perl
print"Content-type:text/html\n\n";
use DBI;
use CGI::Carp qw(fatalsToBrowser);

my(@pairs, $pair, $name, $value);
sub split_string
{
my ($string, $hash, $altdelim) = @_;
if($altdelim && $$string =~ m~;~) { @pairs = split(/;/, $$string); }
else { @pairs = split(/&/, $$string); }
foreach $pair (@pairs) {
($name,$value) = split(/=/, $pair);
$name =~ tr/+/ /;
$name =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
if (exists($hash->{$name})) {
$hash->{$name} .= ", $value";
} else {
$hash->{$name} = $value;
}
}
}
split_string(\$ENV{QUERY_STRING}, \%INFO, 1);
if ($ENV{REQUEST_METHOD} eq 'POST')
{
read(STDIN, my $input, $ENV{CONTENT_LENGTH});
split_string(\$input, \%FORM)
}

#####
$dbusername="root";
$dbpwd="";
$dbname="cwk";
$dbhostname="localhost";
$dbtablename="prytmp";
#$database = "dbi:mysql:".$dbname.":".$dbhostname.":3306";
$database = "dbi:mysql:".$dbname.":".$dbhostname;
$dbh = DBI->connect($database,$dbusername,$dbpwd) or die "Can?t connect to the DB: $DBI::errstr\n";
$epp = "10";
$start = 0;
@results=($dbh->prepare("select * from tablename order by number limit $start,$epp"));
$start=0;

$sth=$dbh->prepare("select * from tablename order by number limit $start,$epp");
$num_rows= $dbh->selectrow_array('select COUNT(*) FROM tablename');
$info=$num_rows;
$num = $info;# total number or entries(based on counter
$start = $INFO{'start'} + 0;# number of the entry currently on top(0- first page,50 on the second, depends on $epp
if ($start > $info) { $start = $info - 1 }
elsif($start < 0) { $start = 0; }
$test=int(($start/$epp)+1);#number of currently shown page
$output='page: ';
for($k=0;$k<$num;$k+=$epp){
$pagenum = int(($k/$epp)+1);
if($test == $pagenum) {
$output .=" $pagenum"; #this is the page we're currently on
} else {
$newstart = ($pagenum - 1)*$epp; #this will be the start variable for the page we link to;
$output .=qq~ <a href="adpro.pl?start=$newstart">$pagenum</a>~;
$menu=$output;
}
}
################
print"<html>\n";
print" <head>\n";
print" <title>Read Requests</title>\n";
print" </head>\n";
print" <body>\n";
print" <table align=\"center\"width=\"90%\"cellpadding=\"4px\"cellspacing=\"4px\"border=\"4px\"bordercolor=\"gold\"valign=\"top\">\n";
print" <form method=\"post\"action=\"adpro2.pl\">\n";
$i = 0;#alt color line one
while($ref=$sth->fetchrow_hashref()){
$sth->execute();
$bgcolor = ($i++ & 1) ? '#ffccff' : '#ffffce'; #alt color line two#fofofo

print" <tr bgcolor='$bgcolor'>\n";
print" <td width=\"10%\"><p class=\"c\">Ok &nbsp; NA<br /><input type=\"radio\"name=\"approve_$ref->{'sub_num'}\"value=\"app\">&nbsp;&nbsp;<input type=\"radio\"name=\"approve_$ref->{'number'}\"value=\"rej\"></p></td>\n";
print" <td width=\"90%\"border=\"6px\"bordercolor=\"gold\">\n";
print" <p>First Name: $ref->{'alias'} <br />\n";
print" Location: $ref->{'location'} <br />\n";
print" Email: <a href=mailto:$ref->{'email'}>$ref->{'email'} </a><br />\n";
print" Request: $ref->{'request'}</p>\n";
print" <input type=\"hidden\"name=\"epp\"value=\"$epp\">\n";
print" </td>\n";
print" </tr><tr>\n";
}
print" <td width=\"100%\"colspan=\"2\">\n";
print" <p class=\"c\"><input type=\"submit\"value=\"Process\">\n";
print" <br />$menu</p>\n";
print"</form>\n";
print" </table><br /><br />\n";

print" </body>\n";
print"</html>\n";

janharders

6:50 pm on Aug 4, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



your script looks a bit like a maintainance-nightmare, but the (first?) problem is here: 
while($ref=$sth->fetchrow_hashref()){
$sth->execute();


that needs to be 
$sth->execute();
while($ref=$sth->fetchrow_hashref()){


because you have to execute a query before you can get its results.
I love this part: 
$num_rows= $dbh->selectrow_array('select COUNT(*) FROM tablename');
$info=$num_rows;
$num = $info;

;)

rocknbil

8:08 pm on Aug 4, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Couple other things seem kinda' "hinky." Look at this.

$epp = "10";
$start = 0;
@results=($dbh->prepare("select * from tablename order by number limit $start,$epp"));
$start=0;

You have $start defined twice, so at this point you have .... limit 0, 10. Then the exact same query is in $sth:

$sth=$dbh->prepare("select * from tablename order by number limit $start,$epp");

... and you never use @results. But it gets better. Later in the script you do

$start = $INFO{'start'} + 0;

The conclusion? Any result set you get it is always going to be limit 0, 10, because the actual select with the limit remains unchanged by the pagination bits. I am guessing you never went farther than 10 records to discover this.

Overall, besides moving the execute, you need to move the select statement **after** your pagination thingy so it picks up any set by $INFO.

Save a copy and try this, starting with the $dbh set to the end. I've bolded changed or moved lines. 


$dbh = DBI->connect($database,$dbusername,$dbpwd) or die "Can?t connect to the DB: $DBI::errstr\n"; 
$epp = "10"; 
$start = 0; 
#Removed @results, it may have had some purpose but I can't see it. 
# 
# Just set it to $num here, see previous post 
$num = $dbh->selectrow_array('select COUNT(*) FROM tablename'); 
if (defined($INFO{'start'})) { $start = $INFO{'start'}; } 
if ($start > $num) { $start = $num - 1; } # changed from $info 
elsif($start < 0) { $start = 0; } ## Huh? could probably delete. 
$test = ($epp > 0)?int(($start/$epp)+1):1; #Avoid division by 0, J.I.C. 
$output='page: '; 
for($k=0;$k<$num;$k+=$epp){ 
 $pagenum = ($epp > 0)?int(($k/$epp)+1):1; # ditto 
 if($test == $pagenum) { 
 $output .=" $pagenum"; #this is the page we're currently on 
 } else { 
 $newstart = ($pagenum - 1)*$epp; #this will be the start variable for the page we link to; 
 $output .=qq~ <a href="adpro.pl?start=$newstart">$pagenum</a>~; 
 $menu=$output; 
 } 
} 
################ Use qq and avoid the toothpick syndrome 

print qq~ 
<html><head><title>Read Requests</title></head> 
<body> 
<table align="center" width="90%" cellpadding="4px"  
cellspacing="4px" border="4px" bordercolor="gold" valign="top"> 
<form method="post"action="adpro2.pl"> 
~; 
# 
$i = 0;#alt color line one 
$sth=$dbh->prepare("select * from tablename order by number limit $start,$epp"); 
# ... limit will now be whatever's input, 10, 10 for example 
$sth->execute(); 
while($ref=$sth->fetchrow_hashref()){ 
 $bgcolor = ($i++ & 1) ? '#ffccff' : '#ffffce'; #alt color line two#fofofo 
 print "<tr bgcolor='$bgcolor'>\n"; 
 print "<td width="10%"><p class="c">Ok &nbsp; NA<br />"; 
 print "<input type="radio"name="approve_$ref->{'sub_num'}"value="app">&nbsp;&nbsp;"; 
 print "<input type="radio"name="approve_$ref->{'number'}"value="rej"></p></td>\n"; 
 print "<td width="90%"border="6px"bordercolor="gold">\n"; 
 print "<p>First Name: $ref->{'alias'} <br />\n"; 
 print "Location: $ref->{'location'} <br />\n"; 
 print "Email: <a href=mailto:$ref->{'email'}>$ref->{'email'} </a><br />\n"; 
 print "Request: $ref->{'request'}</p>\n"; 
 print "<input type="hidden"name="epp"value="$epp">\n"; 
 print "</td>\n"; 
 print "</tr><tr>\n"; 
} 
print "<td width="100%"colspan="2">\n"; 
print "<p class="c"><input type="submit"value="Process">\n"; 
print "<br />$menu</p>\n"; 
print"</form>\n"; 
print "</table><br /><br />\n"; 
print "</body>\n"; 
print"</html>\n";


May contain syntax errors . . . you can just alter changed lines if you want.

typomaniac

12:31 am on Aug 6, 2010 (gmt 0)

10+ Year Member Top Contributors Of The Month



First my apologies...I was testing the script in an XAMPP environment on my own pc which is a Windows machine. I uploaded the scripts to a webserver for a realtime check but its still not working.

The way the script originally was I get the following response:
Can't call method "prepare" on an undefined value

I also tried the script as was modified by rocknbil and the response was:
Can't call method "selectrow_array" on an undefined value

I've been on the search and have found nothing which really has worked so far.

janharders

8:47 am on Aug 6, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



It seems your database connection fails.
Take a closer look at 
$dbh = DBI->connect($database,$dbusername,$dbpwd) or die "Can?t connect to the DB: $DBI::errstr\n";

usually, it should die with a message. do you manipulate $dbh before using prepare or selectrow_array?

typomaniac

3:56 am on Aug 9, 2010 (gmt 0)

10+ Year Member Top Contributors Of The Month



Many thanks rocknbil/janharders. Things are working okay now. Tons of spaghetti were removed from the code.
As far as the error was concerned:
The server(ipowerweb)uses a connection string, i.e.,

$db = DBI->connect('dbi:mysql:$dbname:accountname.ipowermysql.com:3306','username','password') or die('Couldnt connect');
I was connecting to the database but the undefined value turned out to be $dbi. I assigned $dbi=$db; and everything opened up.

As far as the problem using the xampp environment, there was nothing wrong there either other than good old fashioned hairbraining on my part. The menu was showing but no results. The problem---sort by sub_num was typed out as sort by num(wrong table name--double duh on my part).

I searched extensively through many sites trying to find the answer to the "undefined value" problem and found that many many people have had to deal with it and most answers referred to the database connection. Makes me wonder if changing $dbh = DBI->connect(..... to $db = DBI->connect( .... and adding the value $dbh=$db would solve many problems.

Once again I say thanks. After a week of pulling my hair out you saved me from total baldness---I do have one hair left..lol
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / Perl Server Side CGI Scripting 11:54 pm May 20, 2026