A little search bug...

[ripcurlksm]

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...

[B]member_market.php[/B]
[code=php:0]
<?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 "&nbsp;<a href=\"/emt/test/member_market.php?market=$P_market&\member_market.php?page=" . ($page + 1) . "\">>></a>";


?>


[/code]

Why isnt page two, three, four, etc of the pagination working but the first page is?

[ripcurlksm]

Ok so there is problem with my MySQL search query. I modified my MySQL query to simply pull all rows and the pagination works fine. This is odd that it returns the right results only for the first page..

[code=php:0]$query = "SELECT * FROM emt_report ORDER BY date_year DESC, date_month DESC, company ASC";[/code]

Anyway how can I fix/troubleshoot my MySQL query, I am combining and querying three tables so that users get access to the proper data. How can I simplify my MySQL query and still get the correct results?

[ripcurlksm]

Maybe I need help with my MySQL query? I need to query one user permissions table, get the results and match them up with the report_id's of another table. My query needs to look something like this:

[code=php:0]
SELECT * FROM emt_report, user_reports WHERE user_reports.user_id = '$username' AND user_reports.report_id = emt_report.report_id;
[/code]


Here is my database schema:

[code]
emt_report
===============================
id  |  title  |  company
===============================
1    |  Green  |  Crayola
2    |  Red    |  Bic
3    |  Blue    |  Papermate



user_reports
===========================
user_id  |  report_id
===========================
Bob      |  1
Bob      |  2
Sam      |  1
Sam      |  3

[/code]


[B]My database query from my first post, using the table join, works fine, but is there a cleaner way to write it?[/B] This is frustratingly! ARR  :sick: