PHP pagination - problem displaying correct content on additional pages

Question

I found this awesome code online to help with pagination, and it's working well, the only problem is: each page displays the same 4 rows.

Any ideas will be much appreciated

<?php
        //Include the PS_Pagination class
        include('includes/ps_pagination.php');

        //Connect to mysql db
        $conn = mysql_connect("localhost", "root", "root");
        mysql_select_db('database',$conn);
        $sql = "SELECT * FROM studies WHERE niche = '{$_GET['niche']}'";
        //Create a PS_Pagination object
        $pager = new PS_Pagination($conn,$sql,4,5);
        //The paginate() function returns a mysql
        //result set for the current page
        $rs = $pager->paginate();
        //Loop through the result set
        while($row = mysql_fetch_assoc($rs)) {

    $a=0;
    while ($a < $num) {

    $id=mysql_result($result,$a,"id");
    $title=mysql_result($result,$a,"title");
    $strategies=mysql_result($result,$a,"strategies");
    $client=mysql_result($result,$a,"client");
    $copy=mysql_result($result,$a,"copy");
    $thumbmedia=mysql_result($result,$a,"thumbmedia");
    $niche=mysql_result($result,$a,"niche");


    echo '<div class="container"><p class="subheadred"><a href="casestudy.php?id='.$id.'">'.$title.'</a></p></div>';

    echo '<div class="containerstudy"><div class="column1"><p class="subheadsmall">Strategies</p><p class="sidebarred">'.$strategies.'</p>';

    echo '<p class="subheadsmall">Client</p><p class="sidebargrey">'.$client.'</p></div>';

    echo '<div class="column2"><p class="bodygrey">'.substr($copy, 0, 300).'<a href="casestudy.php?id='.$id.'">...more</a></p></div>';

    echo '<div class="column3"><img src="images/'.$thumbmedia.'" height="160" /></div></div>';

    $a++;
    }

        }
        //Display the navigation
        echo $pager->renderNav();
    ?>

This is the included file:

<?php
/**
 * PHPSense Pagination Class
 *
 * PHP tutorials and scripts
 *
 * @package  PHPSense
 * @author   Jatinder Singh Thind
 * @copyright   Copyright (c) 2006, Jatinder Singh Thind
 * @link     http://www.phpsense.com
 */

// ------------------------------------------------------------------------

class PS_Pagination {
    var $php_self;
    var $rows_per_page; //Number of records to display per page
    var $total_rows; //Total number of rows returned by the query
    var $links_per_page; //Number of links to display per page
    var $sql;
    var $debug = false;
    var $conn;
    var $page;
    var $max_pages;
    var $offset;

    /**
     * Constructor
     *
     * @param resource $connection Mysql connection link
     * @param string $sql SQL query to paginate. Example : SELECT * FROM users
     * @param integer $rows_per_page Number of records to display per page. Defaults to 10
     * @param integer $links_per_page Number of links to display per page. Defaults to 5
     */

    function PS_Pagination($connection, $sql, $rows_per_page = 1, $links_per_page = 5) {
     $this->conn = $connection;
     $this->sql = $sql;
     $this->rows_per_page = $rows_per_page;
     $this->links_per_page = $links_per_page;
     $this->php_self = htmlspecialchars($_SERVER['PHP_SELF']);
     if(isset($_GET['page'])) {
      $this->page = intval($_GET['page']);
     }
    }

    /**
     * Executes the SQL query and initializes internal variables
     *
     * @access public
     * @return resource
     */
    function paginate() {
     if(!$this->conn) {
      if($this->debug) echo "MySQL connection missing<br />";
      return false;
     }

     $all_rs = @mysql_query($this->sql);
     if(!$all_rs) {
      if($this->debug) echo "SQL query failed. Check your query.<br />";
      return false;
     }
     $this->total_rows = mysql_num_rows($all_rs);
     @mysql_close($all_rs);

     $this->max_pages = ceil($this->total_rows/$this->rows_per_page);
     //Check the page value just in case someone is trying to input an aribitrary value
     if($this->page > $this->max_pages || $this->page <= 0) {
      $this->page = 1;
     }

     //Calculate Offset
     $this->offset = $this->rows_per_page * ($this->page-1);

     //Fetch the required result set
     $rs = @mysql_query($this->sql." LIMIT {$this->offset}, {$this->rows_per_page}");
     if(!$rs) {
      if($this->debug) echo "Pagination query failed. Check your query.<br />";
      return false;
     }
     return $rs;
    }

    /**
     * Display the link to the first page
     *
     * @access public
     * @param string $tag Text string to be displayed as the link. Defaults to 'First'
     * @return string
     */
    function renderFirst($tag='First') {
     if($this->page == 1) {
      return $tag;
     }
     else {
      return '<a href="'.$this->php_self.'?page=1">'.$tag.'</a>';
     }
    }

    /**
     * Display the link to the last page
     *
     * @access public
     * @param string $tag Text string to be displayed as the link. Defaults to 'Last'
     * @return string
     */
    function renderLast($tag='Last') {
     if($this->page == $this->max_pages) {
      return $tag;
     }
     else {
      return '<a href="'.$this->php_self.'?page='.$this->max_pages.'">'.$tag.'</a>';
     }
    }

    /**
     * Display the next link
     *
     * @access public
     * @param string $tag Text string to be displayed as the link. Defaults to '>>'
     * @return string
     */
    function renderNext($tag=' &gt;&gt;') {
     if($this->page < $this->max_pages) {
      return '<a href="'.$this->php_self.'?page='.($this->page+1).'">'.$tag.'</a>';
     }
     else {
      return $tag;
     }
    }

    /**
     * Display the previous link
     *
     * @access public
     * @param string $tag Text string to be displayed as the link. Defaults to '<<'
     * @return string
     */
    function renderPrev($tag='&lt;&lt;') {
     if($this->page > 1) {
      return '<a href="'.$this->php_self.'?page='.($this->page-1).'">'.$tag.'</a>';
     }
     else {
      return $tag;
     }
    }

    /**
     * Display the page links
     *
     * @access public
     * @return string
     */




    function renderNav() {
     for($i=1;$i<=$this->max_pages;$i+=$this->links_per_page) {
      if($this->page >= $i) {
       $start = $i;
      }
     }

     if($this->max_pages > $this->links_per_page) {
      $end = $start+$this->links_per_page;
      if($end > $this->max_pages) $end = $this->max_pages+1;
     }
     else {
      $end = $this->max_pages;
     }

     $links = '';
     $niche = $_GET['niche'];

     for( $i=$start ; $i<$end ; $i++) {
      if($i == $this->page) {
       $links .= " $i ";
      }
      else {
       $links .= ' <a href="'.$this->php_self.'?page='.$i.'&niche='.$niche.'">'.$i.'</a> ';
      }
     }

     return $links;
    }

    /**
     * Display full pagination navigation
     *
     * @access public
     * @return string
     */
    function renderFullNav() {
     return $this->renderFirst().'&nbsp;'.$this->renderPrev().'&nbsp;'.$this->renderNav().'&nbsp;'.$this->renderNext().'&nbsp;'.$this->renderLast(); 
    }

    /**
     * Set debug mode
     *
     * @access public
     * @param bool $debug Set to TRUE to enable debug messages
     * @return void
     */
    function setDebug($debug) {
     $this->debug = $debug;
    }
}
?>

Answer 1

Do you have page set in your query string?

Answer 2

I'm not sure what this pagination library does with the index of the rows, but you are always setting $a to 0. Perhaps the index is not relative to the page but to the overall recordset being returned?

In other words, what if you set $a to 0 on page 1, 4 on page 2, etc.

You should be able to use $rows_per_page and $page to calculate it. Or perhaps that is the $offset value, so that you set $a to offset and loop it until you hit $rows_per_page additional rows.

---

Edit clarifying solution:
So what I see you could do is:

$a=0;
while ($a < $pager->rows_per_page) {
    $row = $a + $pager->offset;

    $id=mysql_result($result,$row,"id");
    $title=mysql_result($result,$row,"title");
    $strategies=mysql_result($result,$row,"strategies");
    $client=mysql_result($result,$row,"client");
    $copy=mysql_result($result,$row,"copy");
    $thumbmedia=mysql_result($result,$row,"thumbmedia");
    $niche=mysql_result($result,$row,"niche");

    ...
    $a++;
}

Answer 3

I think that if you changed the paginate function, so that it would have one parameter $page and you would set $this->page = $page at the begining of the function, it would work.

You would also have to change the calling of this function in your code to $pager->paginate($_GET['page']).

The pager is also very inefficient, it gets the whole query just to find out, how many rows does the response have. I would use something like:

$all_rs = @mysql_query('SELECT COUNT(*) FROM (' . $this->sql . ')');
if(!$all_rs) {
        if($this->debug) echo "SQL query failed. Check your query.<br />";
        return false;
}
$this->total_rows = mysql_result($all_rs, 0, 0);
@mysql_close($all_rs);

Answer 4

Just at first glance i see your query is repeating itself from the beginning every time you run it, so that means every time you run it it searches from the beggining. you need a counter to tell it where to start from on the next page. something like:

$sql = "SELECT * FROM studies WHERE niche = '{$_GET['niche']}' limit $startPoint, $offset";

that way the offset is the same as the number of items you want per page and the $startPoint is the point at which the search beggins at the next iteration.

somehting like this would work:

if(!$startPoint){$startPoint = 0;}else{$startPoint = {$_GET['$startPoint'];}

within your code pass the start point back to the query and increasing it by the offset after each iteration.

Please bear in mind i've not taking into consideration stuff like injection, and the fact that the variable $num doesn't seem to come from anywhere in the 1st file. I cant see where you initialized it so as to test $a against it.

After looking at your code more in depth i found a few things that need to be changed to get it work in the least here is the changed code:

<?php
     //Include the PS_Pagination class
       include('includes/ps_pagination.php');

        //Connect to mysql db
        $conn = mysql_connect("localhost", "root", "root");
        mysql_select_db('database',$conn);
        $sql = "SELECT * FROM studies";// WHERE niche = '{$_GET['niche']}'";
        //Create a PS_Pagination object
        $pager = new PS_Pagination($conn,$sql,4,5);
        //The paginate() function returns a mysql
        //result set for the current page
        $rs = $pager->paginate();
        //Loop through the result set
        while($row = mysql_fetch_assoc($rs)) {

 //   $a=0;
 //   while ($a < $num) {

 //   $id=mysql_result($result,$a,"id");
 //   $title=mysql_result($result,$a,"title");
  //  $strategies=mysql_result($result,$a,"strategies");
 //   $client=mysql_result($result,$a,"client");
 //   $copy=mysql_result($result,$a,"copy");
 //   $thumbmedia=mysql_result($result,$a,"thumbmedia");
 //   $niche=mysql_result($result,$a,"niche");
    $id=$row['id'];
    $title=$row['title'];
    $strategies=$row['strategies'];
    $client=$row['client'];
    $copy=$row['copy'];
    $thumbmedia=$row['thumbmedia'];
    $niche=$row['niche'];

    echo '<div class="container"><p class="subheadred"><a href="casestudy.php?id='.$id.'">'.$title.'</a></p></div>';

    echo '<div class="containerstudy"><div class="column1"><p class="subheadsmall">Strategies</p><p class="sidebarred">'.$strategies.'</p>';

    echo '<p class="subheadsmall">Client</p><p class="sidebargrey">'.$client.'</p></div>';

    echo '<div class="column2"><p class="bodygrey">'.substr($copy, 0, 300).'<a href="casestudy.php?id='.$id.'">...more</a></p></div>';

    echo '<div class="column3"><img src="images/'.$thumbmedia.'" height="160" /></div></div>';

  //  $a++;
  //  }

        }
        //Display the navigation
        echo $pager->renderNav();
    ?>

Notice i have commented out some part that where not necessary

the variable $a was meaningless as it compared to $num which doesn't exist therefore nothing would show. Now these lines:

   //   $id=mysql_result($result,$a,"id");
     //   $title=mysql_result($result,$a,"title");
      //  $strategies=mysql_result($result,$a,"strategies");
     //   $client=mysql_result($result,$a,"client");
     //   $copy=mysql_result($result,$a,"copy");
     //   $thumbmedia=mysql_result($result,$a,"thumbmedia");
     //   $niche=mysql_result($result,$a,"niche");

where also wrong as you are trying to get the result set from $result. at no place had you put the result set into $result the class you have imported does that and puts the result set into an identifier called $rs.

Since you are using $row = mysql_fetch_assoc($rs) to read through the result set then to get the variables all you need to do is get the column row through an array like this

$id=$row['id'];

and so on. once you do that then the code should work as expected. However this brings us back to this:

$sql = "SELECT * FROM studies WHERE niche = '{$_GET['niche']}'";

i had to get the last part out in that this variable is not getting passed back into the query string (seeing you are using get), the other problem is the 1st time you load the page this variable 'niche' doesn't exist so the 1st time you run the code you will get a blank page, unless this script is accessed through a link that passes in this variable. at this point i have left it commented out as am not sure what you were trying to do through niche.

Hope that helps.