Page 1 of 3

MYSQL Pagination Class

Posted: Thu Sep 29, 2005 5:26 pm
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());
	}
}

?>

Posted: Mon Oct 10, 2005 10:41 am
by seodevhead
error initializing function "initialize()" ??? Any ideas?

Posted: Mon Oct 10, 2005 11:32 am
by John Cartwright
Please post the exact error message, along with PHP version of OS?

Posted: Tue Oct 25, 2005 5:10 pm
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

Posted: Tue Oct 25, 2005 6:43 pm
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.

Posted: Wed Oct 26, 2005 1:20 am
by Jenk
Just a quick one, after a brief read and no testing - where does $result come from in the initialize() function? :)

Posted: Wed Oct 26, 2005 1:29 am
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()

Posted: Wed Oct 26, 2005 1:38 am
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..

Posted: Wed Oct 26, 2005 3:02 am
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

Posted: Wed Oct 26, 2005 1:36 pm
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.

Posted: Wed Oct 26, 2005 9:56 pm
by John Cartwright
I've made a couple changes, seems to be pretty stable now.

Posted: Fri Nov 25, 2005 9:19 am
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.

Posted: Fri Nov 25, 2005 11:33 am
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...

Posted: Sat Nov 26, 2005 4:08 am
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!?

Posted: Sat Nov 26, 2005 12:14 pm
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.