A little search bug...
Posted: Tue Jan 30, 2007 2:25 am
I have a MySQL result page from a table join which takes all of the users permissions and prints the reports for that user. There are lots of reports, so i have a search script, which limits the results per page and adds paginated numbers at the bottom. This is working fine for the first page of results, however when I click the other pages it shows the page without the MySQL query... its pretty much blank with the exception of the template. Why isnt this working on the other paginated pages? All the variables are there in the URL string at the end of my script...
member_market.php
Why isnt page two, three, four, etc of the pagination working but the first page is?
member_market.php
Code: Select all
<?php
session_start(); // Starts the session
$username = $_SESSION['valid_user'];
include('scripts.inc.php');
header("Cache-control: private"); // IE6 Fix. Why? Because it's rubbish
class Pager
{
function getPagerData($numHits, $limit, $page)
{
$numHits = (int) $numHits;
$limit = max((int) $limit, 1);
$page = (int) $page;
$numPages = ceil($numHits / $limit);
$page = max($page, 1);
$page = min($page, $numPages);
$offset = ($page - 1) * $limit;
$ret = new stdClass;
$ret->offset = $offset;
$ret->limit = $limit;
$ret->numPages = $numPages;
$ret->page = $page;
return $ret;
}
}
dbConnect();
// get the pager input values
$P_market = $_GET['market'];
$page = $_GET['page'];
$limit = 50;
$result = mysql_query("select count(*) from emt_report");
$total = mysql_result($result, 0, 0);
// work out the pager values
$pager = Pager::getPagerData($total, $limit, $page);
$offset = $pager->offset;
$limit = $pager->limit;
$page = $pager->page;
// use pager values to fetch data
$query = "SELECT u.id
, u.username
, r.id
, r.company
, r.description
, r.market1
, r.market2
, r.market3
, r.market4
, r.market5
, r.market6
, r.location
, r.date_year
, r.date_month
, r.source
, r.video
, r.audio
, r.pp
, r.execsum
, r.report_url
, r.exec_url
FROM user as u
INNER
JOIN user_reports as p
ON p.user_id = u.username
INNER
JOIN emt_report as r
ON r.id = p.report_id
WHERE username = '$username' AND MATCH(date_year, date_month, market1, market2, market3, market4, market5, market6) AGAINST ('$P_market' IN BOOLEAN MODE) ORDER BY date_year DESC, date_month DESC, company ASC LIMIT $offset, $limit";
$result = mysql_query($query);
// use $result here to output page content
echo 'You are viewing '.$limit.' results from '.$total.' reports.';
echo '<table width="699" border="0" cellspacing="0" cellpadding="4">';
while($row = mysql_fetch_assoc($result))
{
$company = $row['company'];
$description = $row['description'];
$market1 = $row['market1'];
$market2 = $row['market2'];
$market3 = $row['market3'];
$market4 = $row['market4'];
$market5 = $row['market5'];
$market6 = $row['market6'];
$location = $row['location'];
$date_year = $row['date_year'];
$date_month = $row['date_month'];
$source = $row['source'];
$video = $row['video'];
$audio = $row['audio'];
$pp = $row['pp'];
$execsum = $row['execsum'];
$report_url = $row['report_url'];
$exec_url = $row['exec_url'];
print("<p><b>$company</b><br><i>$description</i>
// OTHER VARIABLES LEFT OUT FOR SIMPLICITY
</p>");
$rank++;
}
// ----------------------------------------------------------------
// Below prints pagination in the footer (NEED HELP HERE) !!
// -----------------------------------------------------------------
// output paging system (could also do it before we output the page content)
if ($page == 1) // this is the first page - there is no previous page
echo "<<";
else // not the first page, link to the previous page
echo "<a href=\"/emt/test/member_market.php?page=" . ($page - 1) . "\"><<</a>";
for ($i = 1; $i <= $pager->numPages; $i++) {
echo " | ";
if ($i == $pager->page)
echo "$i";
else
// This is where I think the problem is happening, either this page isnt passing the right variables or something, but all of the needed variables are in the URL. "$P_market" is pulled for the MySQL query and "$i" is pulled for the page numbers.
echo "<a href=\"/emt/test/member_market.php?market=$P_market&page=$i\">$i</a>";
}
if ($page == $pager->numPages) // this is the last page - there is no next page
echo ">>";
else // not the last page, link to the next page
echo " <a href=\"/emt/test/member_market.php?market=$P_market&\member_market.php?page=" . ($page + 1) . "\">>></a>";
?>