Page 1 of 1

A little search bug...

Posted: Tue Jan 30, 2007 2:25 am
by 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...

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


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

Posted: Tue Jan 30, 2007 4:41 am
by volka
Let the script begin with

Code: Select all

<?php
error_reporting(E_ALL); ini_set('display_errors', true);       
session_start(); // Starts the session
and append

Code: Select all

or die(mysql_error().': '.$query)
to both mysql_query calls.

Posted: Tue Jan 30, 2007 10:22 am
by pickle
Output the query you think is working. I'd bet it's not what you expect.

Posted: Tue Jan 30, 2007 11:26 pm
by ripcurlksm
Pickle, I am already using this query successfully. It lists usually over 400 + reports on one page. The purpose of this thread is to break this page up into paginated results. I have successfully got the first page of the paginated results to print. When I select the next page it is blank.

Volka, I have added the or die command to my result query as follows as well as error reporting in the first php call but there is nothing throwing an error:

Code: Select all

<?php
       error_reporting(E_ALL); ini_set('display_errors', true);    
        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")  or die(mysql_error().': '.$query);
    $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>";


?>
Why does my first page show results but none of the other pages?

Posted: Tue Jan 30, 2007 11:58 pm
by 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: Select all

$query = "SELECT * FROM emt_report ORDER BY date_year DESC, date_month DESC, company ASC";
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?

Posted: Wed Jan 31, 2007 1:07 am
by 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: Select all

SELECT * FROM emt_report, user_reports WHERE user_reports.user_id = '$username' AND user_reports.report_id = emt_report.report_id;

Here is my database schema:

Code: Select all

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

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

Posted: Wed Jan 31, 2007 10:03 am
by pickle
RE Your query: I noticed theres a FULLTEXT search going on in there - is $P_market going to be a user entered value? If not then there's, no reason (or at least I can't think of one) why you'd use FULLTEXT search.

RE Pagination: You say the page is 'pretty much' empty except for the template Are you getting absolutely nothing from the paging code (The part underneath your NEED HELP HERE comment)?

Posted: Wed Jan 31, 2007 12:04 pm
by ripcurlksm
This pagination issue is resolved, I need to figure out how to do a table join between two tables somehow with the MySQL query I posted from my previous post above.

Posted: Wed Jan 31, 2007 1:03 pm
by louie35
The two tables has to have some sort of relation between them to join the query like an ID or something.

Posted: Wed Jan 31, 2007 6:27 pm
by RobertGonzalez
First question: How are you passing the parameters to the pager? Are they passing correctly?
Second question: Can you pull all reports before checking the passed parameters, then use the params for the display loop instead of the in the query?