MYSQL Pagination Class

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

MYSQL Pagination Class

Post by John Cartwright »

Developed on: PHP 4.3
Last Updated: Jan 28th 2006

Code: Select all

<?php
/*
	
   Pagination 1.1.0

   Made by John Cartwright (2005)
   Tested and modified by members at http://forums.devnetwork.net
   Feel free to use/modify this script at will
   Please leave credit where due
 
   Sample Usage
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   $query = 'SELECT * FROM `table`'; 
   $paginate = new pagination($query, 15, (isset($_GET['start']) ? $_GET['start'] : 0));
   print_r($paginate->output());
  
   Output
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   Array
    (
       [0] => SELECT * FROM `news` LIMIT 0,2
       [1] => Prev | First | 1 | 2 | 3 | 4 | 5 | 6 | ... | Next | Last
       [2] => Viewing: [ 1 - 2 ] of 24
    )

   History
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   1.1.0 Fixes
		- Missing Number Bug Fixed
		- Added changable delimeter 
   
   Upcoming	
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   1.2.0 
    - Multiple DB support		
*/ 

class pagination 
{
	var $query;          //Query to be modified for paginating
	var $perPage;        //Maximum rows per page
	var $maxPage;        //Maximum pages shown at once (not including selected)
	var $current;        //Current page selected
	var $result;         //Result of query        
	var $totalNumRows;   //Amount of rows selected (non paginated)
	var $totalNumPages;  //Amount of pages
	var $BoundaryMin;    //Minimum boundary of page numbers
	var $BoundaryMax;    //Maximum boundary of page numbers
	var $numbers;        //Collection of formated pages

	function pagination($query, $perPage, $current, $maxPage = 4, $delimeter = '>') {
		$this->query     = $query;
		$this->delimeter = $delimeter;
		$this->perPage   = $perPage;
		$this->maxPage   = $maxPage;
		$this->current   = $current < 1 ? 1 : $current;
		$this->maxPageOffset();			
		$this->initialize();
	}
	
	function maxPageOffset() {
		if ($this->maxPage % 2 == 1 && $this->maxPage != 1) {
			$this->maxPage--;
		}
	}
	
	function initialize() {
		if ($this->performDatabaseCall()) {
			$this->postInitialize();		
		}
	}
	
	function postInitialize() {
		if ($this->current > $this->totalNumPages) {
			$this->current = $this->totalNumpages;
		}
		$this->formatSQL();	
		$this->getBoundaries();
		$this->buildNumbers();					
	}
	
	function performDatabaseCall() {
		$this->result = mysql_query($this->query) or die(mysql_error());
		
		if ($this->result) {
			$this->totalNumRows  = $this->getNumRows();
			$this->totalNumPages = $this->getNumPages();
			return true;
		}
	}

	function getNumRows() {
		return mysql_num_rows($this->result);
	}

	function getNumPages() {
		return ceil($this->totalNumRows / $this->perPage);
	}	

	function formatSQL() {    
		$this->query .= ' LIMIT '.(($this->current-1) * $this->perPage).','.$this->perPage;   
	}

	function viewing() {
		$this->newLimit = $this->current * $this->perPage;
		$this->viewing  = 'Viewing: [ ';
		
		if ($this->perPage != 1) {
			$this->viewing .= (($this->newLimit - $this->perPage) + 1) .' - '.  ($this->newLimit > $this->totalNumRows ? ($this->totalNumRows) : $this->newLimit);
		}
		else {
			$this->viewing .= $this->current;
		}
		
		$this->viewing .= ' ] of '. $this->totalNumRows;
		return $this->viewing;    
	}

	function balanceOffset() {		
		if ($this->BoundaryMin < 1) {
			$this->BoundaryMin = 1;			
			
			if ($this->current < $this->maxDivided) {
				$difference = $this->maxDivided - $this->current;
			}

			$this->BoundaryMax = $this->BoundaryMax + $difference + 1;
		}
		
		if ($this->BoundaryMax > $this->totalNumPages) {
			$difference = ($this->BoundaryMax - $this->totalNumPages);
			$this->BoundaryMin = ($this->BoundaryMin - $difference);
			$this->BoundaryMax = ($this->BoundaryMax - $difference);
		}			
	}

	function truncateOffset() {
		if ($this->BoundaryMin < 1) {
			$this->BoundaryMin = 1;
		}
		if ($this->BoundaryMax > $this->totalNumPages) {
			$this->BoundaryMax = $this->totalNumPages;
		}
	}
	
	function getBoundaries() {
		switch ($this->current) : 
			case ($this->totalNumPages) :
				$this->BoundaryMax = $this->totalNumPages;
				$this->BoundaryMin = (($this->BoundaryMax - $this->maxPage));					
			break;
			case (1) : 
				$this->BoundaryMin = 1;			
				$this->BoundaryMax = ($this->current + $this->maxPage);
			break;
			default: 
				$this->maxDivided  = ceil($this->maxPage / 2);
				$this->BoundaryMin = ($this->current - $this->maxDivided);
				$this->BoundaryMax = ($this->current + $this->maxDivided);										
				$this->balanceOffset();$this->truncateOffset();
				break;  
		endswitch;
		
		$this->truncateOffset();			
	}

	function buildNumbers($string = '') {
		for ($x = $this->BoundaryMin; $x <= $this->BoundaryMax; $x++) {
			if ($x == $this->BoundaryMin && $this->BoundaryMin != 1) {
				$string .= '... '.$this->delimeter.' ';
			}				
			if ($this->current == $x) {
				$string .= '<span style="font-weight: bold">'.$x.'</span> '.$this->delimeter.' ';
			}
			else {
				$string .= '<a href="?start='.$x.'">'.$x.'</a> '.$this->delimeter.' ';
			}	
			if (($x == $this->BoundaryMax) && ($this->BoundaryMax < $this->totalNumPages)) {
				$string .= '... '.$this->delimeter.' ';
			}						
		}
		
		$this->numbers = $string;
	}

	function buildOutput() {
		$output  = ($this->current == 1 ? 'Prev '.$this->delimeter.' ' : '<a href="?start='.($this->current - 1).'">Prev</a> '.$this->delimeter.' ');
		$output .= '<a href="?start=1">First</a> '.$this->delimeter.' ';		
		$output .= $this->numbers;
		$output .= ($this->current == ($this->totalNumPages) ? 'Next '.$this->delimeter.' ' : '<a href="?start='.($this->current + 1).'" >Next</a> '.$this->delimeter.' ');			
		$output .= '<a href="?start='.($this->totalNumPages).'">Last</a>';	
		return $output;
	}

	function output() {
		return array($this->query, $this->buildOutput(), $this->viewing());
	}
}

?>
Last edited by John Cartwright on Mon Jan 30, 2006 10:15 pm, edited 27 times in total.
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post by seodevhead »

error initializing function "initialize()" ??? Any ideas?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Please post the exact error message, along with PHP version of OS?
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

i found a flaw. the max pages thing is off by 1. i don't get the first record in the db ever. i tweaked it a bit and got the next and prev working right and the first and last working right but its too late to figure out how to get the middle numbers (current page) stuff working right. other than that this is a very handy feature, lovin it.

i am runnin PHP 5 incase that helps
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Please post code revisions.. hopefully I'll be able to figure it out..
Not sure about php5 though, havn't really switched over yet.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Just a quick one, after a brief read and no testing - where does $result come from in the initialize() function? :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Jenk wrote:Just a quick one, after a brief read and no testing - where does $result come from in the initialize() function? :)
$this->query()
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Of course.. silly me, it's an assignment, not a comparison (which is what I thought it was) :roll:

Maybe another cup of coffee is in order..
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

Code: Select all

class pagination {
    /*
        @@ $query - Your query you need paginated
        @@ $perPage - How many records per page
        @@ $current - The current page your on
        @@ maxPage - The maximum amount of pages viewed at one time
    */
    function pagination($query, $perPage, $current, $maxPage = 7, $gallery) {
        $this->gallery = $gallery;
        $this->query   = $query;
        $this->perPage = $perPage;
        $this->maxPage = $maxPage;
        $this->current = $current < 0 ? 0 : $current;
        $this->begin = $this->current == 0 ? true : false;
        $this->initialize();
    }
    
    function initialize() {
        if ($result = $this->query()) {
            $this->getNumRows();
            $this->getNumPages();
            $this->current = $this->current > $this->TotalNumPages ? $this->TotalNumPages : $this->current;
        }
        $this->formatSQL();
        $this->getBoundaries();
        $this->numbers = $this->buildNumbers();
        $this->preFormat();        
    }
            
    function query() {
        $this->result = mysql_query($this->query) or die(mysql_error());
        return $this->result;
    }
    
    function getNumRows() {
        $this->TotalNumRows = mysql_num_rows($this->result);
        return $this->TotalNumRows;
    }
    
    function formatSQL() {    
        $this->query .= ' LIMIT '.($this->current * $this->perPage).','.$this->perPage;   
    }
    
    function getNumPages() {
        $this->TotalNumPages = ceil($this->TotalNumRows / $this->perPage);
    }
          
    function viewing() {
        $this->newLimit = $this->current * $this->perPage + $this->perPage;
        return $this->viewing = 'Viewing: [ '.($this->current * $this->perPage) .' - '.  ($this->newLimit > $this->TotalNumRows ? ($this->TotalNumRows) : $this->newLimit).' ] of '. $this->TotalNumRows;    
    }
    
    function getBoundaries() {
        $this->BoundaryMin = $this->current - 3;
        $this->BoundaryMax = $this->current + 3;
        if ($this->BoundaryMin < 1) {
            $this->BoundaryMin = 0;
            $this->BoundaryMax = $this->maxPage;
        }
        if ($this->BoundaryMax > $this->TotalNumPages) {
            $difference = ($this->BoundaryMax - $this->TotalNumPages);
            $this->BoundaryMax = $this->TotalNumPages;
            $this->BoundaryMin = ($this->BoundaryMin - $difference);
        }
    }
    
    function buildNumbers($string = '') {    
        for ($x = $this->BoundaryMin; $x <= $this->BoundaryMax; $x++) {
            if ($x < $this->TotalNumPages) {
                if ($this->current == $x) {
                    $string .= '<span style="font-weight: bold">'.($this->begin ? $x + 1 : $x).'</span> | ';
                }
                else {
                    $string .= '<a href=?gallery='.$this->gallery.'&start='.($this->begin ? $x + 1 : $x).'>'.($this->begin ? $x + 1 : $x).'</a> | ';
                }
            }
        }
        return $string;
    }
    
    function preFormat() {
        $this->output  = ($this->current == 0 ? 'Prev | ' : '<a href="?gallery='.$this->gallery.'&start='.($this->current - 1).'">Prev</a> | ');
        $this->output .= ($this->current == ($this->TotalNumPages) ? 'Next | ' : '<a href="?gallery='.$this->gallery.'&start='.($this->current + 1).'">Next</a> | ');
        $this->output .= $this->numbers;
        $this->output .= '<a href="?gallery='.$this->gallery.'&start=0">First</a> | ';
        $this->output .= '<a href="?gallery='.$this->gallery.'&start='.($this->TotalNumPages - 1).'">Last</a>';
    }
    
    function output() {
        return array($this->query, $this->output, $this->viewing());
    }
    
}
i added in gallery because i need that for my script but i really just changed some numbers around. the middle page number things are real screwed up right now since i did not have time to finish last night and no time now for i got class. the rest works perfect
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

ok i have tested this, not extensivly but with different settings. it works nice for me :D
EDIT: i found some bugs when you try to view page 5 and up. ill fix that then edit this post again
EDIT 2: ehhe, silly me. i did somthing stupid and won't tell you what it was! its fixed now. tested up to 6 pages and working fine

Code: Select all

class pagination {
    /*
        @@ $query - Your query you need paginated
        @@ $perPage - How many records per page
        @@ $current - The current page your on
        @@ maxPage - The maximum amount of pages viewed at one time
    */
    function pagination($query, $perPage, $current, $maxPage, $gallery) {
        $this->gallery = $gallery;
        $this->query   = $query;
        $this->perPage = $perPage;
        $this->maxPage = $maxPage;
        $this->current = $current < 1 ? 0 : $current;
        $this->begin = $this->current == 0 ? true : false;
        $this->initialize();
    }
    
    function initialize() {
        if ($result = $this->query()) {
            $this->getNumRows();
            $this->getNumPages();
            $this->current = $this->current > $this->TotalNumPages ? $this->TotalNumPages : $this->current;
        }
        $this->formatSQL();
        $this->getBoundaries();
        $this->numbers = $this->buildNumbers();
        $this->preFormat();        
    }
            
    function query() {
        $this->result = mysql_query($this->query) or die(mysql_error());
        return $this->result;
    }
    
    function getNumRows() {
        $this->TotalNumRows = mysql_num_rows($this->result);
        return $this->TotalNumRows;
    }
    
    function formatSQL() {    
        $this->query .= ' LIMIT '.($this->current * $this->perPage).','.$this->perPage;   
    }
    
    function getNumPages() {
        $this->TotalNumPages = ceil($this->TotalNumRows / $this->perPage);
    }
          
    function viewing() {
        $this->newLimit = $this->current * $this->perPage + $this->perPage;
        return $this->viewing = 'Viewing Images: [ '.($this->current * $this->perPage) .' - '.  ($this->newLimit > $this->TotalNumRows ? ($this->TotalNumRows) : $this->newLimit).' ] of '. $this->TotalNumRows;    
    }
    
    function getBoundaries() {
        $this->BoundaryMin = $this->current - 3;
        $this->BoundaryMax = $this->current + 3;
        if ($this->BoundaryMin < 1) {
            $this->BoundaryMin = 0;
            $this->BoundaryMax = $this->maxPage;
        }
        else if ($this->BoundaryMax > $this->TotalNumPages) {
            $difference = ($this->BoundaryMax - $this->TotalNumPages);
            $this->BoundaryMax = $this->TotalNumPages;
            $this->BoundaryMin = ($this->BoundaryMin - $difference);
        }
    }
    
    function buildNumbers($string = '') {    
        for ($x = $this->BoundaryMin; $x <= $this->BoundaryMax; $x++) {
            $b=$x+1;
            if ($x < $this->TotalNumPages) {
                if ($this->current == $x) {
                    $string .= '<span style="font-weight: bold">'.$b.'</span> | ';
                }
                else {
                    $b = $x+1;
                    $string .= '<a href=?gallery='.$this->gallery.'&start='.$x.'>'.$b.'</a> | ';
                }
            }
        }
        return $string;
    }
    
    function preFormat() {
        $this->output  = ($this->current == 0 ? 'Prev | ' : '<a href="?gallery='.$this->gallery.'&start='.($this->current - 1).'">Prev</a> | ');
        $this->output .= ($this->current == ($this->TotalNumPages-1) ? 'Next | ' : '<a href="?gallery='.$this->gallery.'&start='.($this->current + 1).'">Next</a> | ');
        $this->output .= $this->numbers;
        $this->output .= '<a href="?gallery='.$this->gallery.'&start=0">First</a> | ';
        $this->output .= '<a href="?gallery='.$this->gallery.'&start='.($this->TotalNumPages - 1).'">Last</a>';
    }
    
    function output() {
        return array($this->query, $this->output, $this->viewing());
    }
    
}

//how i use the $start is like this, and it works just fine for me
$start = (empty($_GET['start']) ? 0 : $_GET['start']);
i took out all of my $gallery stuff BUT i have not tested it without that but there is no reason it would not work without it in there.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I've made a couple changes, seems to be pretty stable now.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Seems to be a couple of issues.

The navigation isn't correct for pages where there is on 1 page of results or 2 pages of results.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Pimptastic wrote:Seems to be a couple of issues.

The navigation isn't correct for pages where there is on 1 page of results or 2 pages of results.
Dang, one of these days I need to test this class, I didn't anticipate so many problems off the shelves. Sometime this weekend I'll run some rigorous tests...
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

actually, there seems to be more problems....when there are multiple pages, there isn't a link the last page.

i.e. There are 6 pages of results the navigation looks like this

Prev Next 1 2 3 4 5 First Last

There is no link to page 6!?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Pimptastic wrote:actually, there seems to be more problems....when there are multiple pages, there isn't a link the last page.

i.e. There are 6 pages of results the navigation looks like this

Prev Next 1 2 3 4 5 First Last

There is no link to page 6!?
Have you tried clicking next? In the beginning it will only show the first 5 (which is incorrect and am fixing)

Edit | I see wehre the problem is coming in, I'm doing some testing now and will have a tested version posted.
Locked