Hi All,
I've sorta painted myself into a corner with a program I'm working on and I'm finding it rather difficult finding a solution to my problem.
My Problem:
I've got a sub routine accepting a search term from a web form and then querying a mysql database. "If" there is only one record meeting the search term the sub works beautifully. OTOH, if there are more then one record that meet the search terms it displays all the records at one time. This makes for a very messy display of the information being returned.
My Question:
How can I each individual record being returned on a one record per page basis? For instance, If there are more there is more then one record being returned to be displayed to the screen how can I display a link at the bottom of the page to retreive the next record of how ever many are being returned meeting the search terms?
My Code:
[code]
sub search_cust_entry
{
&read_config;
# connect to the database
$dbh = DBI->connect("DBI:mysql:$conflist{'db_name'}:$conflist{'db_host'}","$conflist{'db_user'}","$conflist{'db_passwd'}") or die "Cannot Connect: $!
";
# incoming form data
if ( $FORM{'search_name'} =~ /[A-Z,a-z]/ )
{
my $rowcount = 0;
my $fc = 0;
my $search_term = "%$FORM{'search_name'}%";
$sth = $dbh->prepare("describe customer") or die "Sorry...there was an error: $!
";
$sth->execute;
my
@dbField;
my $bgcolor;
while ( $row_hash = $sth->fetchrow_hashref )
{
my $fieldname = $row_hash->{'Field'};
push(
@dbField, $fieldname);
}
print "Content-Type: text/html
";
header("Results of Customer Search");
#print "
@dbField";
my $temp_term = $search_term;
$temp_term =~ s/%//g;
print "
Your Search Term Was: $temp_term
";
print "
";
foreach my $FIELD_NAME ( @dbField )
{
chomp( $FIELD_NAME );
$sth = $dbh->prepare("select $FIELD_NAME from customer where customer_name like '$search_term'") or die "Sorry...there was an error: $!
";
$sth->execute;
while ( $row_hash = $sth->fetchrow_hashref ) #### begin While loop
{
my $field_value = $row_hash->{$FIELD_NAME};
$FIELD_NAME =~ s/_/ /;
if ( ($rowcount % 2) > 0 )
{
$bgcolor = "#FFFFFF";
}
else
{
$bgcolor = "#E9E9E9";
}
if ( $FIELD_NAME eq "customer number" )
{
print "
";
print "$FIELD_NAME | "; print "$field_value "; print " "; print " |
";
print "
";
}
else
{
print "
";
print "$FIELD_NAME | $field_value |
";
print "
";
}
$rowcount++;
} ### end while loop
}
print "
";
print "$rowcount
";
&footer;
exit;
}
}
[/code]
Thanks in advance,
--
Mark
"If I can't code I'm not going to be a happy camper!"
Comments
Your best solution probably involves using a LIMIT in the query. Taking your query as an example:-
SELECT $FIELD_NAME
FROM customer
WHERE customer_name LIKE '$search_term'
LIMIT $recNum, 1
If $recNum is 0, it will return the first record, if it is 1 it will return the second, etc. You should check the number of records in the table too before doing this. Also be careful that $recNum really is a number.
SELECT COUNT(*)
FROM customer
WHERE customer_name LIKE '$search_term'
Would give you the number of available records so you could...
die "Invalid record!" unless $FORM{'recNum'} < $numRecs && $FORM{'recNum'} >= 0 && $FORM{'recNum'} =~ /^d+$/;
$numRecs would contain the number of records. Note that you should do this:-
$FORM{'recNum'} ||= 0;
To default it to 0 if there is no value supplied in the query string. Your links are easy to generate, just remember to check if they are needed.
I think this will give you enough to go on, post again if not.
Jonathan
###
for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
(tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
/(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");
Thanks for the info...its sort of working, but then its sort of not.
The Results:
http://mdw1982.dyndns.org/simpletrack/customer_search_results.html
The link above is just a static example of what is being returned by the program. Below is the code that is being used to make those results. what am I doing wrong?
If I set [red]while ( $recnum > 0 )[/red] to be [red]while ( $recnum > -1 )[/red] I get slightly different results, but I still don't get "all" the rows returned after the initial first record is returned.
The Code:
[code]
sub search_cust_entry
{
my $recnum;
&read_config;
# connect to the database
$dbh = DBI->connect("DBI:mysql:$conflist{'db_name'}:$conflist{'db_host'}","$conflist{'db_user'}","$conflist{'db_passwd'}") or die "Cannot Connect: $!
";
# incoming form data
if ( $FORM{'search_name'} =~ /[A-Z,a-z]/ )
{
my $rowcount = 0;
my $search_term = "%$FORM{'search_name'}%";
$sth = $dbh->prepare("describe customer") or die "Sorry...there was an error: $!
";
$sth->execute;
my @dbField;
my $bgcolor;
while ( $row_hash = $sth->fetchrow_hashref )
{
my $fieldname = $row_hash->{'Field'};
push(@dbField, $fieldname);
}
# get count for number of records which meet search terms
$sth = $dbh->prepare("select count(customer_name) as cust_count from customer where customer_name like '$search_term'");
$sth->execute;
while ( $row_hash = $sth->fetchrow_hashref )
{
$recnum = $row_hash->{'cust_count'};
}
print "Content-Type: text/html
";
header("Results of Customer Search");
#print "@dbField
";
my $temp_term = $search_term;
$temp_term =~ s/%//g;
print "
Your Search Term Was: $temp_term
";
[red]while ( $recnum > 0 )[/red]
{
print "
";
foreach my $FIELD_NAME ( @dbField )
{
chomp( $FIELD_NAME );
$sth = $dbh->prepare("select $FIELD_NAME from customer where customer_name like '$search_term' limit $recnum,1") or die "Sorry...there was an error: $!
";
$sth->execute;
while ( $row_hash = $sth->fetchrow_hashref ) #### begin While loop
{
my $field_value = $row_hash->{$FIELD_NAME};
$FIELD_NAME =~ s/_/ /;
if ( ($rowcount % 2) > 0 )
{
$bgcolor = "#FFFFFF";
}
else
{
$bgcolor = "#E9E9E9";
}
if ( $FIELD_NAME eq "customer number" )
{
print "
";
print "
";
print "$field_value
";
print "
";
print "
print "
}
else
{
print "
";
print "
print "
}
$rowcount++;
} ### end while loop
} # end of $FIELD_NAME foreach loop
print "
print "$rowcount
";
$recnum--;
} # end of $recnum while loop
&footer;
exit;
}
}
[/code]
--
Mark
"If I can't code I'm not going to be a happy camper!"
: Hi Jonathan,
:
: Thanks for the info...its sort of working, but then its sort of not.
:
: The Results:
: http://mdw1982.dyndns.org/simpletrack/customer_search_results.html
:
: The link above is just a static example of what is being returned by the program. Below is the code that is being used to make those results. what am I doing wrong?
:
Just not coding it right. :-) See green bits.
: If I set [red]while ( $recnum > 0 )[/red] to be [red]while ( $recnum > -1 )[/red] I get slightly different results, but I still don't get "all" the rows returned after the initial first record is returned.
:
I thought the idea was to show one on a page and provide links to allow you to browse through them, one on each page?
: The Code:
: [code]
: sub search_cust_entry
: {
: my $recnum;
: &read_config;
: # connect to the database
: $dbh = DBI->connect("DBI:mysql:$conflist{'db_name'}:$conflist{'db_host'}","$conflist{'db_user'}","$conflist{'db_passwd'}") or die "Cannot Connect: $!
";
:
:
: # incoming form data
: if ( $FORM{'search_name'} =~ /[A-Z,a-z]/ )
: {
: my $rowcount = 0;
: [green]my $recnum = $FORM{'recnum'} =~ /^d+$/ ? $FORM{'recnum'} : 1;
: my $numrows;[/green]
: my $search_term = "%$FORM{'search_name'}%";
: $sth = $dbh->prepare("describe customer") or die "Sorry...there was an error: $!
";
: $sth->execute;
: my @dbField;
: my $bgcolor;
:
: while ( $row_hash = $sth->fetchrow_hashref )
: {
: my $fieldname = $row_hash->{'Field'};
: push(@dbField, $fieldname);
: }
: [green]$sth->finish;[/green]
:
: # get count for number of records which meet search terms
: $sth = $dbh->prepare("select count(customer_name) as cust_count from customer where customer_name like '$search_term'");
: $sth->execute;
: [green]$numrows = $sth->fetchrow_array();
: $sth->finish;[/green]
:
: print "Content-Type: text/html
";
: header("Results of Customer Search");
: #print "@dbField
";
: my $temp_term = $search_term;
: $temp_term =~ s/%//g;
: print "Your Search Term Was: $temp_term
";
:
: [green]# Check if we have a valid record number, otherwise use 0.
: $recnum = 1 if $recnum > $numrows || $recnum < 1;
:
: # Take one off it (limit uses base 0).
: $recnum --;
:
: # We only need to print one table - only one per page.[/green]
: print "
";
: foreach my $FIELD_NAME ( @dbField )
: {
: chomp( $FIELD_NAME );
: $sth = $dbh->prepare("select $FIELD_NAME from customer where customer_name like '$search_term' limit $recnum,1") or die "Sorry...there was an error: $!
";
: $sth->execute;
: while ( $row_hash = $sth->fetchrow_hashref ) #### begin While loop
: {
: my $field_value = $row_hash->{$FIELD_NAME};
: $FIELD_NAME =~ s/_/ /;
: if ( ($rowcount % 2) > 0 )
: {
: $bgcolor = "#FFFFFF";
: }
: else
: {
: $bgcolor = "#E9E9E9";
: }
: if ( $FIELD_NAME eq "customer number" )
: {
: print "
";
: print "
";
: print "$field_value
";
: print "
";
: print "
: print "
: }
: else
: {
: print "
";
: print "
: print "
: }
: $rowcount++;
: } ### end while loop
: } # end of $FIELD_NAME foreach loop
: print "
: print "$rowcount
";
: [green]# $recnum--;
: # end of $recnum while loop
:
: # Add code to print next and previous links, e.g.
: if ($recnum > 0) {
: print qq{Prev };
: }
: if ($recnum + 1 < $numrows)
: my $linkto = $recnum + 1;
: print qq{Next };
: }
: [/green]
: &footer;
: exit;
: }
: }
: [/code]
Or something like that.
Jonathan
###
for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
(tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
/(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");
: : Hi Jonathan,
: :
: : Thanks for the info...its sort of working, but then its sort of not.
: :
: : The Results:
: : http://mdw1982.dyndns.org/simpletrack/customer_search_results.html
: :
: : The link above is just a static example of what is being returned by the program. Below is the code that is being used to make those results. what am I doing wrong?
: :
: Just not coding it right. :-) See green bits.
:
: I thought the idea was to show one on a page and provide links to allow you to browse through them, one on each page?
hehehehe... true enough on both counts. I'm still looking at your additions and attempting to wrap my brain around exactly what is going on. The first page of data displays wonderfully, but when clicking the "next" link I get an error ( 500 ) in the browser. When I tail -f apache's event log its telling that I'm getting premature headers.
here's the code for the links:
[code]
if ($recnum > 0) {
print qq{
print qq{Prev };
print qq{
}
if ($recnum + 1 < $numrows)
{
$linkto = $recnum + 1;
print qq{
print qq{Next };
print qq{
}
[/code]
Since I'm coming back into the same sub routine that I have displaying the first page of data I'm wondering if there isn't something out of kilter. This program is taking me places I've never been and may account for why I may be sounding rather thick headed about this.
--
Mark
"If I can't code I'm not going to be a happy camper!"
: additions and attempting to wrap my brain around exactly what is
: going on.
Answering questions where large amounts of code is posted is always "fun", though in this case it couldn't really have been much shorter. So don't worry, I had to do a little brain wrapping before I could reply too. :-)
: The first page of data displays wonderfully, but when
: clicking the "next" link I get an error ( 500 ) in the browser. When
: I tail -f apache's event log its telling that I'm getting premature
: headers.
:
: here's the code for the links:
:
: [code]
: if ($recnum > 0) {
: print qq{
: print qq{Prev };
: print qq{
: }
: if ($recnum + 1 < $numrows)
: {
: $linkto = $recnum + 1;
: print qq{
: print qq{Next };
: print qq{
: }
: [/code]
:
OK, you forget that query string data needs to be passed to the next instance of the script. That includes the string being searched for. So you need to also put this in the query string, just as it would appear when you visit the page the first time, and tac the recnum bit on the end of that. At the moment the script doesn't get a string to search for, so it's bailing out. Admittedly, you would probably be better of printing a nicer error message. ;-)
: Since I'm coming back into the same sub routine that I have
: displaying the first page of data I'm wondering if there isn't
: something out of kilter.
The second call to the script is independent of the first call. Data isn't preserved between executions. (mod_perl kinky-ness aside, but of course you never *rely* on that behaviour).
: This program is taking me places I've never been and may account for
: why I may be sounding rather thick headed about this.
:
No worries, most things are a little strange the first time.
Jonathan
###
for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
(tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
/(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");
: : additions and attempting to wrap my brain around exactly what is
: : going on.
: Answering questions where large amounts of code is posted is always "fun", though in this case it couldn't really have been much shorter. So don't worry, I had to do a little brain wrapping before I could reply too. :-)
: OK, you forget that query string data needs to be passed to the next instance of the script. That includes the string being searched for. So you need to also put this in the query string, just as it would appear when you visit the page the first time, and tac the recnum bit on the end of that. At the moment the script doesn't get a string to search for, so it's bailing out. Admittedly, you would probably be better of printing a nicer error message. ;-)
aaaaaaaaaah HA! I see where you're going.
--
Mark
"If I can't code I'm not going to be a happy camper!"
O...my brain hurts... I'm getting a [red]premature end of script headers[/red] error when I click on the "Next" link when the first page displays. After reading the code for this routine over and over and over to really get the flow of the sub in my head with the new code you added I'm getting a real good picture of how everything is working now.
I can't shake the feeling that there's something right in front of me that I'm overlooking. Below is the sub in its current form. Everything you've added to this sub appears in Red.
[code]
sub search_cust_entry
{ [red]
my $recnum;
my $linkto;[/red]
&read_config;
# connect to the database
$dbh = DBI->connect("DBI:mysql:$conflist{'db_name'}:$conflist{'db_host'}",
"$conflist{'db_user'}","$conflist{'db_passwd'}") or die "Cannot Connect: $!
";
# incoming form data
if ( $FORM{'search_name'} )
{
print "Content-Type: text/html
";
header("Results of Customer Search");
my $rowcount = 0;[red]
my $recnum = $FORM{'recnum'} =~ /^d+$/ ? $FORM{'recnum'} : 1;
my $numrows;[/red]
my $search_term = "%$FORM{'search_name'}%";
$sth = $dbh->prepare("describe customer") or die
"Sorry...there was an error: $!
";
$sth->execute;
my @dbField;
my $bgcolor;
while ( $row_hash = $sth->fetchrow_hashref )
{
my $fieldname = $row_hash->{'Field'};
push(@dbField, $fieldname);
}
$sth->finish;
# get count for number of records which meet search terms
$sth = $dbh->prepare("select count(customer_name)
as cust_count from customer where customer_name like
'$search_term'") or die "Something is wrong with and I cannot
continue: $!
";
$sth->execute;[red]
$numrows = $sth->fetchrow_array();[/red]
$sth->finish;
#print "@dbField
";
my $temp_term = $search_term;
$temp_term =~ s/%//g;
print "Your Search Term Was: $temp_term
";
[red]
# Check if we have a valid record number, otherwise use 0.
$recnum = 1 if $recnum > $numrows || $recnum < 1;
# Take one off it (limit uses base 0).
$recnum --;[/red]
# We only need to print one table - only one per page.
print "
";
foreach my $FIELD_NAME ( @dbField )
{
chomp( $FIELD_NAME );
$sth = $dbh->prepare("select $FIELD_NAME from customer where customer_name like '$search_term' limit $recnum,1") or die "Sorry...there was an error: $!
";
$sth->execute;
while ( $row_hash = $sth->fetchrow_hashref ) #### begin While loop
{
my $field_value = $row_hash->{$FIELD_NAME};
$FIELD_NAME =~ s/_/ /;
if ( ($rowcount % 2) > 0 )
{
$bgcolor = "#FFFFFF";
}
else
{
$bgcolor = "#E9E9E9";
}
if ( $FIELD_NAME eq "customer number" )
{
print "
";
print "
";
print "$field_value
";
print "
";
print "
print "
}
else
{
print "
";
print "
print "
}
$rowcount++;
} ### end while loop
} # end of $FIELD_NAME foreach loop
print "
print "$rowcount
";
[red] # added by Jonathan to control seperate page
# display when more then one record is return on search
# Add code to print next and previous links, e.g.
if ($recnum > 0) {
# clean search term value
$search_term =~ s/%//g;
print qq{
print qq{Prev };
print qq{
}
if ($recnum + 1 < $numrows)
{
# clean search term value
$search_term =~ s/%//g;
$linkto = $recnum + 1;
print qq{
print qq{Next };
print qq{
}
[/red]
&footer;
exit;
}
}
[/code]
--
Mark
"If I can't code I'm not going to be a happy camper!"
[code]sub search_cust_entry
{
my $recnum;
my $linkto;
&read_config;
# connect to the database
$dbh = DBI->connect("DBI:mysql:$conflist{'db_name'}:$conflist{'db_host'}",
"$conflist{'db_user'}","$conflist{'db_passwd'}") or die "Cannot Connect: $!
";
# incoming form data
if ( $FORM{'search_name'} )
{
print "Content-Type: text/html
";
[/code]
It must be in that section. Otherwise, we'd get a header. So either:-
1) The sub is never called.
2) Bad things happen in &read_config.
3) if ( $FORM{'search_name'} ) evaluates false so no header is printed.
I suspect it may be (1). What's your code for deciding whether to call the sub?
Jonathan
###
for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
(tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
/(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");
: Well, the bit of code we can look to is fairly small at least.
:
: [code]sub search_cust_entry
: {
: my $recnum;
: my $linkto;
: &read_config;
:
: # connect to the database
: $dbh = DBI->connect("DBI:mysql:$conflist{'db_name'}:$conflist{'db_host'}",
: "$conflist{'db_user'}","$conflist{'db_passwd'}") or die "Cannot Connect: $!
";
:
:
: # incoming form data
: if ( $FORM{'search_name'} )
: {
: print "Content-Type: text/html
";
: [/code]
:
: It must be in that section. Otherwise, we'd get a header. So either:-
:
: 1) The sub is never called.
: 2) Bad things happen in &read_config.
: 3) if ( $FORM{'search_name'} ) evaluates false so no header is printed.
:
: I suspect it may be (1). What's your code for deciding whether to call the sub?
:
: Jonathan
Well &read_config; calls a sub in the admin.pl file that reads the config file and gets db name and db host information. the db username and passwd is being gathered from a cookie, at least thats the plan once I can get past this point. I'm calling the getCookie sub in other parts of the program and that works ok.
I suspected that I wasn't getting any header being sent to the browser so I place the header call at the beginning of the sub, but that didn't make any difference at the time.
Edit: I just tested it again, this time calling the getCookie sub for the db user and passwd and now its printing the first data page, but there's no header coming out. I'm beginning to think this blasted program is haunted!
--
Mark
"If I can't code I'm not going to be a happy camper!"
: : Well, the bit of code we can look to is fairly small at least.
: :
: : [code]sub search_cust_entry
: : {
: : my $recnum;
: : my $linkto;
: : &read_config;
: :
: : # connect to the database
: : $dbh = DBI->connect("DBI:mysql:$conflist{'db_name'}:$conflist{'db_host'}",
: : "$conflist{'db_user'}","$conflist{'db_passwd'}") or die "Cannot Connect: $!
";
: :
: :
: : # incoming form data
: : if ( $FORM{'search_name'} )
: : {
: : print "Content-Type: text/html
";
: : [/code]
: :
: : It must be in that section. Otherwise, we'd get a header. So either:-
: :
: : 1) The sub is never called.
: : 2) Bad things happen in &read_config.
: : 3) if ( $FORM{'search_name'} ) evaluates false so no header is printed.
: :
: : I suspect it may be (1). What's your code for deciding whether to call the sub?
: :
: : Jonathan
:
: Well &read_config; calls a sub in the admin.pl file that reads the config file and gets db name and db host information. the db username and passwd is being gathered from a cookie, at least thats the plan once I can get past this point. I'm calling the getCookie sub in other parts of the program and that works ok.
:
: I suspected that I wasn't getting any header being sent to the browser so I place the header call at the beginning of the sub, but that didn't make any difference at the time.
:
: Edit: I just tested it again, this time calling the getCookie sub for the db user and passwd and now its printing the first data page, but there's no header coming out. I'm beginning to think this blasted program is haunted!
:
Question - what is the "enter_cust_search" for in the query string? Should it not be more like:-
enter_cust_search=1
What is it set to the first time the page loads?
Jonathan
###
for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
(tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
/(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");