Hello All,
I know this is more of a Perl question then DBI, but I posted this a couple of
days ago the one the Perl list without any results. What I am trying to do is
limit a search result to 5 per page. Below is the bulk of the script minus all
the HTML stuff. If someone maybe to lend some assitance, I would greatly
appreciated, more then you know. Been at this most of the day! When testing, I
know I'm getting a results of 4, but when the first results pages loads(set the
$Page = 0), it doesn't show any results, if I comment out the LIMIT clause, then
it display all the results. Everything else works perfectly! :)
TIA
#######################################################################
my $cgi = new CGI;
my $Page = $cgi->param('page') || "0";
my $State = $cgi->param('start');
my $Zipcode = $cgi->param('zipcode');
my $City = $cgi->param('city');
my $State = $cgi->param('State');
my $Category = $cgi->param('categories');
$Category or error("Unable to process search request. You must select a
Category","No Category Selected");
my $KeyWords = $cgi->param('keywords');
my $MaxPerPage = $conf{'limit_display'}; # set to 5
my $CatCode = substr($Category,0,2);
if ($KeyWords) {
$KeyWords =~ s![\'\"]!!;
$KeyWords =~ s!_!\\\_!;
$KeyWords =~ s!%!\\\%!;
$KeyWords =~ s!^\s+!!;
$KeyWords =~ s!\s+$!!;
my @words = split(' ', $KeyWords);
$Searchwords = join(' ',@words);
$Searchwords_q = quote("$Searchwords");
}
my $CatDisplay = CategoryDisplay($Category);
my $City_q = quote($City);
# Start Building Search Query
$sql = qq|SELECT bi.bus_name,bi.address,bl.city,bl.state,
bl.zipcode,h.hr_descript,bi.phone,bi.comments,bi.unique_url,
CONCAT(bi.contact_fname," ",bi.contact_lname) AS name|;
if (defined($Searchwords)) {
$sql .= qq| FROM bus_info bi,bus_search bs,bus_loc bl,hours h
WHERE bi.category = '$Category'
AND MATCH bs.keywords AGAINST ($Searchwords_q)
OR (bs.cat_prefix = '$CatCode' AND bs.bus_id = bi.info_id)|;
if ($Zipcode) {
$sql .= qq| AND bl.zipcode LIKE '$Zipcode%'|;
}
elsif ($City) {
$sql .= qq| AND bl.city = $City_q|;
}
elsif ($State !~ /ALL/) {
$sql .= qq| AND bl.state = '$State'|;
}
} # close if ($Seachwords)
else
{
$sql .= qq| FROM bus_info bi,bus_loc bl,hours h WHERE bi.category ='$Category'|;
if($Zipcode) {
$sql .= qq| AND bl.zipcode LIKE '$Zipcode%'|;
}
elsif ($City) {
$sql .= qq| AND bl.city = $City_q|;
}
elsif ($State !~ /ALL/) {
$sql .= qq| AND bl.state = '$State'|;
}
}
$sql .= qq| AND bi.info_id = bl.loc_id AND h.hr_code = bi.hrs_open
GROUP BY bi.bus_name ASC|;
if ($Page == 0) {
$sql .= qq| LIMIT $MaxPerPage|;
} else {
$sql .= qq| LIMIT $Start,$MaxPerPage|;
}
$sth = query($sql) or dbError();
my $data = $sth->fetchall_arrayref({});
$rows = $sth->rows;
$rows or error("Sorry, no search results where found","No Results Found!");
my $total_results = $rows
my $NextPage = ($Page + 1);
my $PrevPage = ($Page - 1);
my $PreviousPage = $PrevPage > 0 ? $PrevPage : "1";
my $start_row = (($Page) * $MaxPerPage);
# A bunch HTML stuff
# The mini forms to generate the Next or Previous Page.
<table border="0" width="3%" cellpadding="0">
<tr>
<td width="3%"><p align="left">
<form action="/cgi-bin/search1.cgi" method="post">
<input type="hidden" name="categories" value="$Category">
<input type="hidden" name="city" value="$City">
RESULTS
if ($State !~ /ALL/i) {
print<<RESULTS;
<input type="hidden" name="state" value="$State">
RESULTS
} else {
print<<RESULTS;
<input type="hidden" name="state" value="">
RESULTS
}
print<<RESULTS;
<input type="hidden" name="zipcode" value="$Zipcode">
<input type="hidden" name="keywords" value="$KeyWords">
<input type="hidden" name="page" value="$PreviousPage">
<input type="hidden" name="start" value="$start_row">
<input type="hidden" name="total" value="$total_results">
<input type="submit" value="PREVIOUS PAGE">
</form>
</td>
<td width="3%"><p align="left">
<form action="/cgi-bin/search1.cgi" method="post">
<input type="hidden" name="categories" value="$Category">
<input type="hidden" name="city" value="$City">
RESULTS
if ($State !~ /ALL/i) {
print<<RESULTS;
<input type="hidden" name="state" value="$State">
RESULTS
} else {
print<<RESULTS;
<input type="hidden" name="state" value="">
RESULTS
}
print<<RESULTS;
<input type="hidden" name="zipcode" value="$Zipcode">
<input type="hidden" name="keywords" value="$KeyWords">
<input type="hidden" name="page" value="$NextPage">
<input type="hidden" name="start" value="$start_row">
<input type="hidden" name="total" value="$total_results">
<input type="submit" value="NEXT PAGE">
</form>
</td>
</tr>
</table>
RESULTS
Mike(mickalo)Blezien
========================================
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Tel: 1(225) 686-2002
=========================================
