Page 1 of 2

Yet another MYSQL Pagination Class

Posted: Wed Nov 01, 2006 12:06 pm
by JayBird
Well, here is my attempt at a pagination class. It is very loosely based on the class by JCart here but I found it to be a little too buggy (no offense JCart).

Simple, but versatile is what i was going for here. See what you think.

Code: Select all

<?php
/**
 * A pagination class 
 *
 * @version     0.1
 * @author      Mark Beech
 * @date        1st November 2006
 * @license     http://www.gnu.org/licenses/lgpl.txt Lesser GNU Public License
 *
 * @copyright Copyright &copy; 2006 Mark Beech - All Rights Reserved.
 *
 *   This library is free software; you can redistribute it and/or
 *   modify it under the terms of the GNU Lesser General Public
 *   License as published by the Free Software Foundation; either
 *   version 2.1 of the License, or any later version.
 *
 *   This library is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *   Lesser General Public License for more details.
 *
 *   You should have received a copy of the GNU Lesser General Public
 *   License along with this library; if not, write to
 *
 *   The Free Software Foundation, Inc.,
 *   51 Franklin Street,
 *   Fifth Floor,
 *   Boston,
 *   MA  02110-1301  USA
 *
 *    "Mark Beech" <mbeech@mark-beech.co.uk>
 *
 */
/*

USAGE:
	$paginate = new paginate($sql, 3, (isset($_GET['start']) ? $_GET['start'] : 1));
	$pagination = $paginate->getOutput();
	
OUTPUT:
    [next] 			=> Next >>
    [prev] 			=> << Prev
    [first] 		=> First
    [last] 			=> Last
    [pageLinks] 	=> 1 | 2 | 3 | 4 ...
    [query] 		=> SELECT 	* FROM `products` LIMIT 0,3
    [viewing] 		=> 1 to 3 of 13
	
OTHER POINTS OF INTEREST:
	Each link has its own CSS class definition e.g. the default class name is 'paginate', therefore:
		'Next >>' link will have the CSS class name 'paginateNext'.
		'<< Prev' link will have the CSS class name 'paginatePrev'.
		'<< First' link will have the CSS class name 'paginateFirst'.
		'<< Last' link will have the CSS class name 'paginateLast'.
	The page number links are simple 'paginate'
	
*/

/**
 * A pagination class that returns various usuful pieces of data
 */ 
class paginate
{
	var $query;						// modified query
	var $perPage;					// number of results to show per page
	var $maxPage;					// the last page of the query result
	var $currentPage;				// the current select page
	var $totalResults;				// total number of results
	var $result;					// the result resource
	var $pageLinksBefore = 3;		// the number of page link we want to display after the current page
	var $pageLinksAfter = 3;		// the number of page link we want to display before the current page
	var $pageNumDelimeter = " | ";  // the elimeter we want to use to glue the page numbers together
	var $linkClass = "pagination";	// the CSS style used for ALL links generated by this class
	var $currentQueryString;		// if there are an values in the query strign, we will be able to keep them on the page links
	
	/**
	 * Sets required vars upon generation of the paginate class
	 * @param  string	sql query 
	 * @param  int		number of results per to show per page
	 * @param  int		page we are currently viewing 
	 * @param  bool		TRUE or FALSE - keep any values from the current query string
	 * @return void
	 */ 
	function paginate($query, $perPage, $currentPage, $maintainQueryString=TRUE)
	{
		$this->query 				= $query;
		$this->perPage 				= $perPage;
		$this->currentPage 			= $currentPage;
		$this->maintainQueryString 	= $maintainQueryString;
		
		if($this->maintainQueryString)
			$this->getQueryString();
		
		$this->totalResults();
		$this->createNav();
		$this->alterQuery();
		$this->otherNavElements();
	}
	
	/**
	 * Stores the current query string (minus the start value) so we can insert it into the pagination links
	 * @return void
	 */ 
	function getQueryString()
	{
		if(!empty($_GET))
		foreach($_GET as $k=>$v)
			if($k != "start")
				$this->currentQueryString .= $k."=".$v."&";
	}

	/**
	 * Gets the total number of results from the database
	 * @return void
	 */ 
	function totalResults()
	{
		$this->result = mysql_query($this->query);
		$this->totalResults = mysql_num_rows($this->result);
		
		if($this->totalResults != 0)
		{
			$this->maxPage = ceil(($this->totalResults / $this->perPage));
		}
	}
	
	/**
	 * Creates the page link numbers
	 * @return void
	 */ 
	function createNav()
	{
		if($this->totalResults != 0)
		{
			if(($this->currentPage - $this->pageLinksBefore) < 1)
				$this->lowerBoundary = 1;
			else
				$this->lowerBoundary = $this->currentPage - $this->pageLinksBefore;
				
			if(($this->currentPage + $this->pageLinksAfter) > $this->maxPage)
				$this->upperBoundary = $this->maxPage;
			else
				$this->upperBoundary = $this->currentPage + $this->pageLinksAfter;
		
			for($x = $this->lowerBoundary; $x <= $this->upperBoundary; $x++)
			{
				if($this->currentPage == $x)
					$this->pageNums[] = '<strong><a href="?'.$this->currentQueryString.'start='.$x.'" class="'.$this->linkClass.'">'.$x.'</a></strong>';
				else
					$this->pageNums[] = '<a href="?'.$this->currentQueryString.'start='.$x.'" class="'.$this->linkClass.'">'.$x.'</a>';
			}
			
			if(($this->currentPage - $this->pageLinksBefore) > 1)
				$pageLinks = "... ";
				
			$pageLinks .= implode($this->pageNumDelimeter, $this->pageNums);
			
			if(($this->currentPage + $this->pageLinksAfter) < $this->maxPage)
				$pageLinks .= " ...";
				
			$this->pageLinks = $pageLinks;
		}
	}
	
	/**
	 * Alters the query to limit the results according to the page the user is viewing
	 * @return void
	 */ 
	function alterQuery()
	{
		if($this->currentPage != 1)
			$this->query .= ' LIMIT '.(($this->currentPage-1) * $this->perPage).','.$this->perPage;
		else
			$this->query .= ' LIMIT '.(($this->current) * $this->perPage).','.$this->perPage;
	}
	
	/**
	 * Create out other navigation elements, Prev, Next, First, Last etc.
	 * @return void
	 */ 
	function otherNavElements()
	{
		if($this->currentPage != 1)
		{
			$this->prevLink = '<a href="?'.$this->currentQueryString.'start='.($this->currentPage - 1).'" class="'.$this->linkClass.'Prev"><< Prev</a>';
		}
		if($this->currentPage != $this->maxPage)
		{
			$this->nextLink = '<a href="?'.$this->currentQueryString.'start='.($this->currentPage + 1).'" class="'.$this->linkClass.'Next">Next >></a>';
		}
		
		$this->firstLink = '<a href="?'.$this->currentQueryString.'start=1" class="'.$this->linkClass.'First">First</a>';
		
		$this->lastLink = '<a href="?'.$this->currentQueryString.'start='.$this->maxPage.'" class="'.$this->linkClass.'Last">Last</a>';
		
		if($this->currentPage == 1)
			if($this->maxPage == 1)
				$this->viewing = '1 to '.$this->totalResults.' of '.$this->totalResults;
			else
				$this->viewing = '1 to '.($this->currentPage * $this->perPage).' of '.$this->totalResults;
		elseif($this->currentPage == $this->maxPage)
			$this->viewing = ((($this->currentPage * $this->perPage) - $this->perPage) + 1).' to '.$this->totalResults.' of '.$this->totalResults;
		else
			$this->viewing = ((($this->currentPage * $this->perPage) - $this->perPage) + 1).' to '.($this->currentPage * $this->perPage).' of '.$this->totalResults;
	}
	
	/**
	 * Package all the data into a nice array
	 * @return  array  Array of the generated links etc.
	 */ 
	function getOutput()
	{
		$elements['next'] 		= $this->nextLink;
		$elements['prev'] 		= $this->prevLink;
		$elements['first'] 		= $this->firstLink;
		$elements['last'] 		= $this->lastLink;
		$elements['pageLinks'] 	= $this->pageLinks;
		$elements['query'] 		= $this->query;
		$elements['viewing']	= $this->viewing;
		
		return $elements;
	}
}
?>
Example 1

Code: Select all

<?php

$sql = "SELECT * FROM `products`";

/* instatiate a new instance of the pagination class */
$paginate = new paginate($sql, 9, (isset($_GET['start']) ? $_GET['start'] : 1));

$pagination = $paginate->getOutput();
?>
Example 1 returns

Code: Select all

Array
(
    [next] => Next >>
    [prev] => 
    [first] => First
    [last] => Last
    [pageLinks] => 1 | 2
    [query] => SELECT * FROM `products` LIMIT 0,9
    [viewing] => 1 to 9 of 13
)
As you can see, all the navigation pieces are returned separately so you can place them where suits you best.

Example 2
Say you have a product catalog and the user has clicked into a certain category, and you display this by using GET values from the query string i.e. http://www.someonlinestore.com/catalog.php?categoryID=5

By default, the pagination class will maintain "categoryID=5" on all the pagination links it generates. This can be turned off by setting $maintainQueryString to FALSE

Example 3
Each link has its own CSS class definition e.g. the default class name is 'paginate', therefore:
  • 'Next >>' link will have the CSS class name 'paginateNext'.
  • '<< Prev' link will have the CSS class name 'paginatePrev'.
  • 'First' link will have the CSS class name 'paginateFirst'.
  • 'Last' link will have the CSS class name 'paginateLast'.

The page number links are simply 'paginate'.

The default class name can be changed by changing $linkClass.[/list]

Example 4
You can specify the number of links that should appear each side of the current page. The default it 3.

Example output is as follows (current page in bold)
1 | 2 | 3 | 4 ....

... 4 | 5 | 6 | 7 | 8 | 9 | 10....

.. 10 | 11 | 12 | 13.

Posted: Wed Nov 01, 2006 1:29 pm
by Burrito
looks useful.

I do like the way phpBB does it though and that is how I've organized mine. I think it would be nice to have an additonal argument that would allow for different output types.

ie:
<< Prev 1,2,3...4,5,6...24,25,26 Next >>
or
<< Prev ...5,6,7,8,9.... Next >>
or
<< Prev A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z Next >>
or some combination of the 1st and last.

having an alpha-line as an option is a must IMHO and will be the next thing I incorporate into mine.

good work pimp.

Posted: Wed Nov 01, 2006 3:43 pm
by Chris Corbyn
Burrito wrote:I think it would be nice to have an additonal argument that would allow for different output types.
Different view objects :) Most of the logic in pagination is mathematics.... the view needn't care about how it's computed, it just needs to final result so it needn't really be kept together:

Code: Select all

Pagination::instance(...)->render(new NumericView());

Posted: Thu Nov 02, 2006 3:20 am
by JayBird
Hmmm...weird, I'm sure someone posted a reply when i checked this morning.

Anyway...he was saying that it may be better to just COUNT() the rows in the DB instead of returning the entire resultset. Well, this is something i was talking to JCart about last night.

To do this, we were think of doing something along the lines of (as suggested by Aborint in a thread a while ago)

Code: Select all

preg_replace('#SELECT\s(.*?)\s+FROM#i', 'SELECT COUNT( * ) AS `count` FROM', preg_quote($this->query));
Which will change

Code: Select all

SELECT `productID`, `productName` FROM `products`
to

Code: Select all

SELECT COUNT( * ) FROM `products`
That works fine for simple queries as above

But some of my queries are a little more complex i.e.

Code: Select all

SELECT `p`.`productID`, `p`.`productName`, `p`.`productCost`, `p`.`brandID`,
       `i`.`imageName`,
       `pf`.`productID`, `pf`.`fabricID`,
       `pit`.`productID`, `pit`.`itemID`
FROM `products` AS `p`
LEFT JOIN `productImages` AS `i` ON `i`.`productID` = `p`.`productID`
LEFT JOIN `productFabric` AS `pf` ON `pf`.`productID` = `p`.`productID`
LEFT JOIN `productItem` AS `pit` ON `pit`.`productID` = `p`.`productID`
GROUP BY `p`.`productID`
...and after the preg_replace(), this query doesn't return the expected result.

I would like to be able to do this, so if anyone has a suggestion, I'm open to ideas.

As for Burritos suggestion, this is something i would like to add at a later date when i get time. I just did it this way as it fits the current application i am building.

Posted: Thu Nov 02, 2006 3:34 am
by malcolmboston
does the job looks simple and is pretty customisable

GJ Mark!

Posted: Thu Nov 02, 2006 4:07 am
by choppsta
I have something similar in my pagination code to your regex, but I first check for "GROUP BY" or "HAVING". If they are found in the query it doesn't do the COUNT(*) replacing.

Otherwise looks good, although I personally prefer to keep the presentation seperate and have my database object accept just a page number and the number of records per page, then just return the results, plus the number of pages.

The actual display of the page links is then handled elsewhere.

Posted: Thu Nov 02, 2006 4:22 am
by timvw
choppsta wrote:I have something similar in my pagination code to your regex, but I first check for "GROUP BY" or "HAVING". If they are found in the query it doesn't do the COUNT(*) replacing.
You should only check for the group by clause (since the having clause is only allowed when there is a group by clause).

Posted: Thu Nov 02, 2006 5:31 am
by sike
hey,

counting the full resultset is especially in mysql a damn easy thing:

Code: Select all

SELECT SQL_CALC_FOUND_ROWS * FROM foo LIMIT 0, 10
SELECT FOUND_ROWS();
chris

Posted: Thu Nov 02, 2006 6:55 am
by Chris Corbyn

Code: Select all

/**
         * Package all the data into a nice array
         * @return  array  Array of the generated links etc.
         */
        function getOutput()
        {
                $elements['next']             = $this->nextLink;
                $elements['prev']             = $this->prevLink;
                $elements['first']           = $this->firstLink;
                $elements['last']             = $this->lastLink;
                $elements['pageLinks']  = $this->pageLinks;
                $elements['query']           = $this->query;
                $elements['viewing']    = $this->viewing;
               
                return $elements;
        }
Where is $elements initialized?

Posted: Thu Nov 02, 2006 6:58 am
by Chris Corbyn
sike wrote:hey,

counting the full resultset is especially in mysql a damn easy thing:

Code: Select all

SELECT SQL_CALC_FOUND_ROWS * FROM foo LIMIT 0, 10
SELECT FOUND_ROWS();
chris
That is awesome! I so didn't know that was available :) Thank you.

Posted: Thu Nov 02, 2006 6:58 am
by JayBird
d11wtq wrote:Where is $elements initialized?
Whoopsie, it isn't :lol:

Posted: Thu Nov 02, 2006 10:35 am
by John Cartwright
d11wtq wrote:
sike wrote:hey,

counting the full resultset is especially in mysql a damn easy thing:

Code: Select all

SELECT SQL_CALC_FOUND_ROWS * FROM foo LIMIT 0, 10
SELECT FOUND_ROWS();
chris
That is awesome! I so didn't know that was available :) Thank you.
I second that, thanks.

Posted: Thu Nov 02, 2006 10:58 am
by Burrito
d11wtq wrote:
Burrito wrote:I think it would be nice to have an additonal argument that would allow for different output types.
Different view objects :) Most of the logic in pagination is mathematics.... the view needn't care about how it's computed, it just needs to final result so it needn't really be kept together:

Code: Select all

Pagination::instance(...)->render(new NumericView());
it's safe to blame this question entirely on my ignorance of MVC patterns and their benefit, but why would creating an entirely new object be more beneficial than just setting a property (or multiple properties) in the existing class?

Posted: Thu Nov 02, 2006 12:15 pm
by John Cartwright
The property would determine which view to load..

Code: Select all

class  
{
   protected $viewtype = 'numerical';

   public function render()
   {
      $name = $this->viewtype.'View';
      $this->view->render(new $name());
   }
}
Roughly. Although I get the feeling I don't understand what you meant. :wink:

Posted: Thu Nov 02, 2006 12:25 pm
by Burrito
I mean why not just do something like this:

Code: Select all

class paginate
{
  public $alpha;
  public $numbers;
  ...
  private someMethodToSetProperty()
  {
    $this->alpha = // add pagination stuff here to generate alpha pagination
    $this->numbers = // add pagination stuff here to generate numbers pagination
  }
}

$page = new paginate();
// display alphas
echo $page->alpha;
// display numbers
echo $page->numbers;
what advantage do you gain by having them in different objects rather than properties?