homepage Welcome to WebmasterWorld Guest from 54.167.75.155
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
Forum Library, Charter, Moderators: coopster & jatar k & phranque

Perl Server Side CGI Scripting Forum

    
Perl MySql Problem
Data is not showing.
typomaniac




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

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




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

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




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

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




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

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




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

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




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved