Paging Class using PHP and MySQL

Based on my last post on paging using PHP and MySQL, I have coded this class. It is very easy to implement and it will save your time. Here is a code snippet to show how simple to use it.

//include the class file
include('Pager.php');
 
//making connection to the database
mysql_connect('localhost', 'root', '');
mysql_select_db('test');
 
//prepare SQL
$sql = 'SELECT * FROM books';
 
//create an object of Pager passing the SQL
$pager = new Pager($sql);
 
//set the url. this is the current page
$pager->url = 'index.php';
 
//set number of rows. by default it is 10
$pager->rowPerPage = 5;
 
//build the pager
$pager->build();
 
//get paged data
$rows = $pager->getPagedData();

Click the download link given below for a copy of the class. It also includes an working example.

Download

Share

15 Comments

  • By Fahmi, April 24, 2008 @ 11:42 pm

    Thanks for nice work Sajjad Bhai….
    I will try to implement it in my current project…

  • By Arafat Rahman, April 27, 2008 @ 1:50 pm

    Everyday we need pagination.
    Hope this paging class may make our life easy.

  • By Andrew, April 3, 2009 @ 2:20 pm

    Hello,
    In the internet I found yours php paging script.
    Unfortunatly I am new in php, and I’d like to make a pics gallery 2×2, with paging, but couldn’t make a edit in yours script that could show 2 pics in line and 2 pics in colum.
    If not so hard may bee you could help me?

  • By Mohammad Sajjad Hossain, April 3, 2009 @ 2:39 pm

    @Andrew
    Actually it depends on how you show/present the rows that you get using the script. Let me give an example based on your requirement.

    Suppose you have used my script/class, provided row per page 4 and the result is in

    $rows

    variable. Now in your view file (where you are going to show the pictures) do the following:

    <table width="100%">
    <?php
        $columNo = 1;
     
        foreach($rows as $row)
        {
            if($columnNo == 1) //first column
            {
                echo '<tr>';
            }
     
            echo '<td><img src="' . $row['image_path'] . '" alt="" /></td>';
            $columnNo++;
     
            if($columnNo > 2) //as you want to show 2 pictures in one line
            {
                echo '</tr>';
                $columnNo = 1;
            }
        }
     
        //in case if the page has 3 photos
        if($columnNo == 2)
        {
           echo '<td></td></tr>';
        }
    ?>
    </table>

    I have not tested this code, but it should work as intended. Let me know if it has solved your problem or not.

  • By Andrew, April 6, 2009 @ 1:44 pm

    Hello,
    thank you very much it helps me and of course it works 🙂
    And the last one question are:

    in Pager.php file you was wrote about which items now are showing:

    return ‘Showing ‘ . $rangeStart . ‘ – ‘ . $rangeEnd . ‘ of ‘ . $this->totalRecords;

    But I prefered to translate in my native language, and I want to use utf-8 encoding, but I don’t know where I could this encoding change, because when I try to write char in the internet page I saw a symbol (unknown char), but not a char. May bee it is not so difficult to change it?

    Thank you again very much.

  • By Mohammad Sajjad Hossain, April 6, 2009 @ 1:51 pm

    Hi Andrew,
    You may try this meta tag (in head section)…

    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  • By Andrew, April 7, 2009 @ 10:24 am

    Yes, this line I am add in html document, but it doesn’t work,
    but when I change encoding to Baltic it works, but didn’t propertlyshown other letters.
    But I found way of this situation, I have enter an ASCII code and it works great 🙂

  • By Mohammad Sajjad Hossain, April 7, 2009 @ 10:28 am

    I think if you have used Unicode then that should have worked with that meta tag. Anyway, alls well that ends well 😉

  • By Tanvir, June 28, 2009 @ 10:29 am

    Dear brother sajjadhossain it is important and necessary script. It well be very much if you make complex.

  • By Mohammad Sajjad Hossain, June 28, 2009 @ 11:30 am

    Thank you Tanvir bhai.

    Actually its a base. I assume “complex” means more customizable. You may try to add some new functions to it to make it more customizable. If you do so, please do not forget to share those with us. 🙂

  • By Josh Beauregard, September 20, 2011 @ 1:17 am

    Thank you it was quick and easy to implment

  • By Dashmesh Singh, April 18, 2014 @ 3:49 pm

    Thanks for such a good work…i have a query ..this script does not work if i uses it with where caluse .Please suggest how can i pass $reg value or v_date value to second page for pagination

     
    $url ='mrd_view.php';
     
    //set number of rows. by default it is 10
    $pager->rowPerPage = 3;
     
    //build the pager
    $pager->build();
     
    //get paged data
    $rows = $pager->getPagedData();
  • By Mohammad Sajjad Hossain, April 18, 2014 @ 5:03 pm

    Thanks Dashmesh. I think you are talking about query string. You may use the following class instead. It is a modified version.

    To build the links with query string do the following:

    $pager->setQueryVariables(array('var1_name' => 'var1_value'));

    Here is the modified class:

    /**
    * Class: Pager
    *
    * This class can be used for pagination
    *
    * @author Mohammad Sajjad Hossain
    *         [email protected], [email protected]
    */
    class Pager{
      var $url = '';
      var $sql = '';
      var $pageIndex = 1;
      var $rowPerPage = 10;
      var $totalRecords = 0;
      var $totalPages = 0;
      var $startIndex = 0;
      var $endIndex = 0;
      var $nextLink = '';
      var $previousLink = '';
     
      var $pageIndexVar = 'pageIndex';
     
      var $queryVars = array();
     
      function Pager($sql = ''){
    	  $this->sql = $sql;
    	  $index = isset($_REQUEST[ $this->pageIndexVar ]) ? $_REQUEST[ $this->pageIndexVar ] : 1;
    	  $this->pageIndex = ((int) $index == 0) ? 1 : $index;
      }
     
      function getTotalRecords(){
    	  if($this->sql){
    		  //counting total number of rows
    		  $sql = 'SELECT COUNT(*) FROM (' . $this->sql . ') abc';
    		  $query = mysql_query($sql);
    		  $row = mysql_fetch_row($query);
    		  $this->totalRecords = $row[0];
    	  }
     
    	  return $this->totalRecords;
      }
     
      function getTotalPages(){
    	  if($this->sql){
    		  $this->totalPages = (int) ceil($this->totalRecords/$this->rowPerPage);
    	  }
     
    	  return $this->totalPages;
      }
     
      function setPageIndex(){
    	  if($this->pageIndex > $this->totalPages){
    		  $this->pageIndex = $this->totalPages;
    	  }
      }
     
      function setStartIndex(){
    	  $this->startIndex = ($this->pageIndex - 1) * $this->rowPerPage;
      }
     
      function setEndIndex(){
    	  if($this->totalRecords > $this->rowPerPage){
    		  $this->endIndex = $this->startIndex + $this->rowPerPage;
    	  }
    	  else{
    		  $this->endIndex = $this->totalRecords;
    	  }
     
    	  if($this->pageIndex == $this->totalPages){
    		  if($this->totalRecords < $this->totalPages * $this->rowPerPage){
    			  $this->endIndex = $this->totalRecords;
    		  }
    	  }
      }
     
      function getRangeText(){
    	  if($this->totalRecords > 0){
    		  $rangeStart = $this->startIndex + 1;
     
    		  if(($this->totalRecords < $this->rowPerPage) || ($this->pageIndex == $this->totalPages)){
    			  $rangeEnd = $this->totalRecords;
    		  }
    		  else{
    			  $rangeEnd = $this->endIndex;
    		  }
     
    		  return 'Showing '  . $rangeStart . ' - ' . $rangeEnd . ' of ' . $this->totalRecords;
    	  }
      }
     
      function build(){
    	  $this->getTotalRecords();
    	  $this->getTotalPages();
    	  $this->setPageIndex();
    	  $this->setStartIndex();
    	  $this->setEndIndex();
     
    	  $queryString = $this->getQueryString();
     
    	  // link for next page
    	  $this->nextLink = $this->url . '?' . $this->pageIndexVar . '=' . ($this->pageIndex + 1) . $queryString;
     
    	  // link for previous page
    	  $this->previousLink = $this->url . '?' . $this->pageIndexVar . '=' . ($this->pageIndex - 1) . $queryString;
     
    	  $this->sql .= ' LIMIT ' . $this->startIndex . ', ' .  $this->rowPerPage;
      }
     
      function getPagedData(){
    	  $query = mysql_query($this->sql);
     
    	  $rows = array();
     
    	  if($query){
    		  while($row = mysql_fetch_assoc($query)){
    			  $rows[] = $row;
    		  }
    	  }
    	  return $rows;
      }
     
      function getNumberLinks(){
    	  $links = '';
     
    	  for($i = 1; $i <= $this->totalPages; $i++){
    		  $links .= '<a href="' . $this- rel="nofollow">url . '?'  . $this->pageIndexVar . '=' . $i . $this->getQueryString() . '">' . $i . '</a> ';
    	  }
     
    	  return $links;
      }
     
      function getQueryString(){
    	  $queryString = '';
     
    	  if(!empty($this->queryVars)){
    		  $nvps = array();
     
    		  foreach($this->queryVars as $var => $value){
    			  $nvps[] = "$var=$value";  
    		  }
    		  $queryString = '&' . implode('&' , $nvps);
    	  }
     
    	  return $queryString;
      }
     
      function setQueryVariables($varsArray){
    	  $this->queryVars = $varsArray;
      }
    }
  • By Dashmesh Singh, April 23, 2014 @ 11:48 am

    thanks for the reply sir….is it not possible to pass variable with url
    e.g. view_mrd.php?id=$_POST[‘id]’

Other Links to this Post

  1. Paging using PHP and MySQL | Dream House — April 24, 2008 @ 11:30 pm

RSS feed for comments on this post. TrackBack URI

Leave a comment

blog