Pagination code using php and mysql

Hi Geeks,
We have gone through some user requests for pagination code for php mysql project.So we have created a simple class where you can easily create pagination for any grid or listing. following are the simple steps for making a pagination system in php.

Step 1: Create a class file ‘Paginator.class.php’ in your project with following code snippet

<?php
 
class Paginator {
 
     private $_conn;
        private $_limit;
        private $_page;
        private $_query;
        private $_total;
		
	 public function __construct( $conn, $query ) {
		 
		$this->_conn = $conn;
		$this->_query = $query;
	 
		$rs= $this->_conn->query( $this->_query );
		$this->_total = $rs->num_rows;
		 
	}
	public function getData( $limit = 10, $page = 1 ) {
     
    $this->_limit   = $limit;
    $this->_page    = $page;
   
 
    if ( $this->_limit == 'all' ) {
        $query      = $this->_query;
    } else {
		$offset=( ( $this->_page - 1 ) * $this->_limit );
        $query      = $this->_query . " LIMIT " . $offset . ", $this->_limit";
    }
    $rs             = $this->_conn->query( $query );
	 
   $results=array();
    while ( $row = $rs->fetch_assoc() ) {
        $results[]  = $row;
    }
 
    $result         = new stdClass();
    $result->page   = $this->_page;
    $result->limit  = $this->_limit;
    $result->total  = $this->_total;
    $result->data   = $results;
 
    return $result;
	}
	
	public function append_existing_query_string($qstring)
	{
		if(isset($_GET))
		{
			foreach($_GET as $k=>$v)
			{
				$qstring.="&".$k."=".$v;
			}
		}
		return $qstring;
	}
	
	public function createLinks( $links, $list_class ) {
    if ( $this->_limit == 'all' ) {
        return '';
    }
 
    $last       = ceil( $this->_total / $this->_limit );
 
    $start      = ( ( $this->_page - $links ) > 0 ) ? $this->_page - $links : 1;
    $end        = ( ( $this->_page + $links ) < $last ) ? $this->_page + $links : $last;
 
    $html       = '<ul class="' . $list_class . '">';
 
    $class      = ( $this->_page == 1 ) ? "disabled" : "";
	    
	//
	$qstring='?limit=' . $this->_limit . '&page=' . ( $this->_page - 1 );
	$qstring=$this->append_existing_query_string($qstring);
	//
	
    $html       .= '<li class="' . $class . '"><a href="'.$qstring.'">&laquo;</a></li>';
 
    if ( $start > 1 ) {
		
		//
		$qstring='?limit=' . $this->_limit . '&page=1';
		$qstring=$this->append_existing_query_string($qstring);
		//
        $html   .= '<li><a href="'.$qstring.'">1</a></li>';
        $html   .= '<li class="disabled"><span>...</span></li>';
    }
 
    for ( $i = $start ; $i <= $end; $i++ ) {
        $class  = ( $this->_page == $i ) ? "active" : "";
		//
		$qstring='?limit=' . $this->_limit . '&page=' . $i;
		$qstring=$this->append_existing_query_string($qstring);
		//
        $html   .= '<li class="' . $class . '"><a href="'.$qstring.'">' . $i . '</a></li>';
    }
 
    if ( $end < $last ) {
        $html   .= '<li class="disabled"><span>...</span></li>';
		//
		$qstring='?limit=' . $this->_limit . '&page=' . $last;
		$qstring=$this->append_existing_query_string($qstring);
		//
        $html   .= '<li><a href="'.$qstring.'">' . $last . '</a></li>';
    }
 
    $class      = ( $this->_page == $last ) ? "disabled" : "";
	
	$qstring='?limit=' . $this->_limit . '&page=' . ( $this->_page + 1 );
	$qstring=$this->append_existing_query_string($qstring);
	
    $html       .= '<li class="' . $class . '"><a href="'.$qstring.'">&raquo;</a></li>';
 
    $html       .= '</ul><span style="float:right;color:blue">Total records : '.$this->_total.'</span>';
 
    return $html;
	}

}
?>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

<?php

class Paginator {

private $_conn;

private $_limit;

private $_page;

private $_query;

private $_total;

public function __construct( $conn, $query ) {

$this->_conn = $conn;

$this->_query = $query;

$rs= $this->_conn->query( $this->_query );

$this->_total = $rs->num_rows;

}

public function getData( $limit = 10, $page = 1 ) {

$this->_limit = $limit;

$this->_page = $page;

if ( $this->_limit == 'all' ) {

$query = $this->_query;

} else {

$offset=( ( $this->_page - 1 ) * $this->_limit );

$query = $this->_query . " LIMIT " . $offset . ", $this->_limit";

}

$rs = $this->_conn->query( $query );

$results=array();

while ( $row = $rs->fetch_assoc() ) {

$results[] = $row;

}

$result = new stdClass();

$result->page = $this->_page;

$result->limit = $this->_limit;

$result->total = $this->_total;

$result->data = $results;

return $result;

}

public function append_existing_query_string($qstring)

{

if(isset($_GET))

{

foreach($_GET as $k=>$v)

{

$qstring.="&".$k."=".$v;

}

return $qstring;

}

public function createLinks( $links, $list_class ) {

if ( $this->_limit == 'all' ) {

return '';

}

$last = ceil( $this->_total / $this->_limit );

$start = ( ( $this->_page - $links ) > 0 ) ? $this->_page - $links : 1;

$end = ( ( $this->_page + $links ) < $last ) ? $this->_page + $links : $last;

$html = '<ul class="' . $list_class . '">';

$class = ( $this->_page == 1 ) ? "disabled" : "";

$qstring='?limit=' . $this->_limit . '&page=' . ( $this->_page - 1 );

$qstring=$this->append_existing_query_string($qstring);

$html .= '<li class="' . $class . '"><a href="'.$qstring.'">«</a></li>';

if ( $start > 1 ) {

$qstring='?limit=' . $this->_limit . '&page=1';

$qstring=$this->append_existing_query_string($qstring);

$html .= '<li><a href="'.$qstring.'">1</a></li>';

$html .= '<li class="disabled"><span>...</span></li>';

}

for ( $i = $start ; $i <= $end; $i++ ) {

$class = ( $this->_page == $i ) ? "active" : "";

$qstring='?limit=' . $this->_limit . '&page=' . $i;

$qstring=$this->append_existing_query_string($qstring);

$html .= '<li class="' . $class . '"><a href="'.$qstring.'">' . $i . '</a></li>';

}

if ( $end < $last ) {

$html .= '<li class="disabled"><span>...</span></li>';

$qstring='?limit=' . $this->_limit . '&page=' . $last;

$qstring=$this->append_existing_query_string($qstring);

$html .= '<li><a href="'.$qstring.'">' . $last . '</a></li>';

}

$class = ( $this->_page == $last ) ? "disabled" : "";

$qstring='?limit=' . $this->_limit . '&page=' . ( $this->_page + 1 );

$qstring=$this->append_existing_query_string($qstring);

$html .= '<li class="' . $class . '"><a href="'.$qstring.'">»</a></li>';

$html .= '</ul><span style="float:right;color:blue">Total records : '.$this->_total.'</span>';

return $html;

}

Step 2: Include the paging class before extracting data to show

<?php

include 'Paginator.class.php';

?>

<?php

include 'Paginator.class.php';

Step 3: Now extract the data for any page as following

<?php
//create a connection to mysql
	$conn       = new mysqli('SERVER HOST NAME','MYSQL USER NAME', 'MY SQL USER PASSWORD','PROJECT DATABASE NAME' );
	
	//DEFINE LIMIT for PER PAGE now 25 is page limit
    $limit      = ( isset( $_GET['limit'] ) ) ? $_GET['limit'] : 25;
	
	//DEFAULT PAGE NUMBER if No page in url
    $page       = ( isset( $_GET['page'] ) ) ? $_GET['page'] : 1;
	
	//Number of frequency links to show at one time ; 
    $links      = ( isset( $_GET['links'] ) ) ? $_GET['links'] : 7;
   
   //your query here in query varriable
    $query  = "Select * from customers  ";
 
 //create a paging class object with connection and query parameters
    $Paginator  = new Paginator( $conn, $query );
 
 //get the results from paginator class
    $results    = $Paginator->getData( $limit, $page ); 
 ?>

<?php

//create a connection to mysql

$conn = new mysqli('SERVER HOST NAME','MYSQL USER NAME', 'MY SQL USER PASSWORD','PROJECT DATABASE NAME' );

//DEFINE LIMIT for PER PAGE now 25 is page limit

$limit = ( isset( $_GET['limit'] ) ) ? $_GET['limit'] : 25;

//DEFAULT PAGE NUMBER if No page in url

$page = ( isset( $_GET['page'] ) ) ? $_GET['page'] : 1;

//Number of frequency links to show at one time ;

$links = ( isset( $_GET['links'] ) ) ? $_GET['links'] : 7;

//your query here in query varriable

$query = "Select * from customers ";

//create a paging class object with connection and query parameters

$Paginator = new Paginator( $conn, $query );

//get the results from paginator class

$results = $Paginator->getData( $limit, $page );

Step 4 : Now we are ready with data. So just showing the data and links as following

> Show the data first and after it showing the pagination links as following:

<table class="table table-striped">
              <thead>
                <tr>
               
                  <th>Customer Id</th>
                  <th>Customer Name</th>
				  <th>Customer Email</th>
                  <th>Customer Address</th>
                 
                </tr>
              </thead>
              <tbody>
				<?php
			 
				if(isset($results) && count( $results->data ) > 0){
				 
				 for( $i = 0; $i < count( $results->data ); $i++ ) { ?>
				<tr>
                <td><?php echo $results->data[$i]['id']; ?></td>
                <td><?php echo $results->data[$i]['name']; ?></td>
                <td><?php echo $results->data[$i]['email']; ?></td>
                <td><?php echo $results->data[$i]['address']; ?></td>
               
				  
				</tr>
				<?php }
				} ?>
               
              </tbody>
            </table>

<thead>

<tr>

<th>Customer Id</th>

<th>Customer Name</th>

<th>Customer Email</th>

<th>Customer Address</th>

</tr>

</thead>

<tbody>

<?php

if(isset($results) && count( $results->data ) > 0){

for( $i = 0; $i < count( $results->data ); $i++ ) { ?>

<tr>

<td><?php echo $results->data[$i]['email']; ?></td>

<td><?php echo $results->data[$i]['address']; ?></td>

</tr>

<?php }

} ?>

</tbody>

</table>

> Show the paging links after the data

<p><?php echo $Paginator->createLinks( $links, $class="pagination"); ?></p>

1

<p><?php echo $Paginator->createLinks( $links, $class="pagination"); ?></p>

Published by

Jeetendra Singh