A little search bug...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ripcurlksm
Forum Commoner
Posts: 34
Joined: Sun Aug 08, 2004 9:17 pm

A little search bug...

Post 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?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Output the query you think is working. I'd bet it's not what you expect.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
ripcurlksm
Forum Commoner
Posts: 34
Joined: Sun Aug 08, 2004 9:17 pm

Post 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?
ripcurlksm
Forum Commoner
Posts: 34
Joined: Sun Aug 08, 2004 9:17 pm

Post 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?
ripcurlksm
Forum Commoner
Posts: 34
Joined: Sun Aug 08, 2004 9:17 pm

Post 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:
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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)?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
ripcurlksm
Forum Commoner
Posts: 34
Joined: Sun Aug 08, 2004 9:17 pm

Post 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.
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post by louie35 »

The two tables has to have some sort of relation between them to join the query like an ID or something.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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?
Post Reply